How to calculate average bed and wake time?

Question

I have a spreadsheet of two columns. A contains bed time (time I went to bed) and B contains wake time (time I woke up). How could I go about calculating the average of bed time and also wake time?

For example;

A = 11:45pm, 11:00pm, 12:15am, 12:45am, 12:00am

B = 6:00am, 5:00am, 5:30am, 7:00am, 6:00am

Also how would one calculate total time slept between rows? The typical average formula in excel gets thrown off even if I convert to 24 hour format. Any ideas would be much appreciated.

Answer 1

If you include the date with the time, you can simply subtract and average just like normal numbers. The number formatting, however, is important for the data to display correctly.

For example:

The formula in cell C2 is =B2-A2 and the formula for the averages in cell A7 is =AVERAGE(A2:A6)

Custom number formatting for Column C: uses h "hrs" mm "min" and Columns A-B use m/d/yyyy h:mm AM/PM. Cells A7 and B7 use the standard Time number format.

Answer 2

To do this accurately without having to add dates:

As @barry-houdini mentioned in a comment, to get the "Hours Slept" to work, you'll need to use MOD since time "wraps around" at 12 PM/24:00, which is equal to 1 in the spreadsheet world. To accomplish that:

C3: =mod(B3-A3,1)

Once you have the durations, the average is simply:

C2: =AVERAGE(C3:C7)

For the averages of the actual times you'll need to use ARRAYFORMULA if there's the possibility that the times in any one column are going to need to "wrap around" midnight (i.e. there might be some values on one side and some on the other). For that we have:

A2: =ARRAYFORMULA(AVERAGE(if(A3:A7>0.5,A3:A7,A3:A7+1)))

The above formula simply takes each value in the range, checks if it's over 0.5 (noon), and if it's not adds 1 to move it to the next day, then averages all the values. The 0.5 assumes you don't go to bed before noon, but if you have an unusual schedule you could obviously change it.

For the average of the wake times, the above isn't probably necessary for most people because generally you wake up between midnight one day and midnight the next, so none of your values need to "wrap around." That said:

B2: =AVERAGE(B3:B7)

To do this accurately with dates:

Adding dates really only masks the issues with calculating averages, because it's using both the date and the time for the average. It's calculating the time on a specific day that is the average of all the date and time values. That leads to two problems:

1. If you miss any one day of data, your average is completely thrown off
2. If you have an even number of days worth of data, it's going to give you the opposite AM/PM time value

What you're really trying to average is the TIMEVALUES, so we simply add that to each of the ranges in the formulas from above:

A2: =ARRAYFORMULA(AVERAGE(if(TIMEVALUE(A3:A7)>0.5,TIMEVALUE(A3:A7),TIMEVALUE(A3:A7)+1)))

B2: =ARRAYFORMULA(AVERAGE(TIMEVALUE(B3:B7)))

C2 doesn't need to change, since it's still just dealing with durations and not dates.