5

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.

  • Welcome to superuser. Can you clarify to explain what you mean by "total average of bed time"? What is it that you want to know? Do you mean if we have 11pm and midnight as bedtime, you mean the average to be 11:30pm? As for calculating times, include the date... then your date calculation will be easier (correct). – Sun Dec 24 '14 at 19:50
  • Thank you. Basically from the times in column A I'm trying to figure out exactly what you said; if 11pm and 12am the average would be 1130pm. – tiger Dec 24 '14 at 20:03
2

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:

enter image description here

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.

  • Thank you! This worked wonderfully, just one question; in column A, how do you manage the time going from PM > AM. For example in row 3 and 4 the date is skipped by one day. What if I'm switching a few mins between am and pm back and fourth? – tiger Dec 24 '14 at 20:26
  • That's the reason for including the date. Excel stores date/time as a single value, so date+time is a continuous scale. The whole number portion is a day count since a reference date and the decimal portion is fraction of a day. Excel doesn't think in terms of AM/PM, that is just formatting. It is a date+time value on the continuum minus another date+time value. – fixer1234 Dec 24 '14 at 20:32
  • Yeah, like fixer says, just enter both the date and the time in the cell exactly as it is shown: 12/4/2014 12:15 AM. Excel will associate that with a date + time as a single number. – user387876 Dec 24 '14 at 20:50
  • Ah gotcha so the actual date and time need to be entered. Thanks again! – tiger Dec 24 '14 at 20:53
  • 1
    It's possible to do this without dates as long as you never sleep fpr more than 24 hours! just put Bed time in A2 and Wake time in B2 and use this formula in C2 - =MOD(B2-A2,1) - Using dates might help in some circumstances but I'd be careful with averaging those columns, e.g. =AVERAGE(A2:A6) might give you a valid result in the example shown but what if row 6 wasn't there - what do you get with =AVERAGE(A2:A5)? Averaging those will be problematic because you are including the date in the average. – barry houdini Dec 27 '14 at 11:51
2

To do this accurately without having to add dates:

Calculating sleep times and averages without using 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:

Calculating sleep times and averages using 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.