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:
Once you have the durations, the average is simply:
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:
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:
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:
- If you miss any one day of data, your average is completely thrown off
- 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:
C2 doesn't need to change, since it's still just dealing with durations and not dates.