Creating a sleep/wake log - need to calculate average sleep and wake time

[MLSNetworks]

I have created a sleep/wake log for a sleep experiment where individuals enter in the time they went to bed at night on Row 2 and the time they got up in the morning on Row 3 for 7 days. I have a formula at the end of each row for those 7 days that gives me the average time in bed and average wake time over the 7 day period.

My issue is when I format the cells as Time for each day, excel needs you to enter in the time in "military time" in order to do the calculation at the end of the row and I don't want to have to tell the individuals to enter their time in military time for each day.

For example, if an individual goes to bed at 9:00 PM I don't want them to have to enter in 21:00, I want them to just enter in 9:00 PM because I want to make this log as easy as possible for the user so they don't have to think about what military time to enter. Btw, in case anyone is wondering, this is a manual exercise and using a sleep tracker is expressly not to be used as part of the experiment.

Below is an example of my data. I want the hour and minutes of the time the user went to bed and woke up to be "wizzywig" within the cell and have an average formula for the 7 days in cell I2 and I3.

Is there a way to force excel to recognize the cell format as military time when entering in hour and minutes or must the users enter their time in military time?​

​

​

<strike></strike>

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Week 1[/TD]
[TD]Day 1[/TD]
[TD]Day 2[/TD]
[TD]Day 3[/TD]
[TD]Day 4[/TD]
[TD]Day 5[/TD]
[TD]Day 6[/TD]
[TD]Day 7[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Time Went to Bed [/TD]
[TD]9:45 PM[/TD]
[TD]9:15 PM[/TD]
[TD]9:00 PM[/TD]
[TD]9:30 PM[/TD]
[TD]

9:00 PM​

<strike></strike>
[/TD]
[TD]<strike></strike>

9:15 PM​

<strike></strike>
[/TD]
[TD]10:00 PM[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Time Woke Up[/TD]
[TD]6:00 AM[/TD]
[TD]6:15 PM[/TD]
[TD]6:30 PM[/TD]
[TD]6:00 AM[/TD]
[TD]6:00 PM[/TD]
[TD]6:30 PM[/TD]
[TD]7:30 AM[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks to anyone who can help me with this!

 

[Logit]

Format the time cells as h:mm;@ AM/PM

The user will enter time in this manner : 9:15 PM and the time will be displayed as 21:15

Or : 9:15 AM and the time will be displayed as 9:15

They must enter the AM or PM at the end.

 

[sandy666]

or try PowerQuery:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Week 1[/td][td=bgcolor:#5B9BD5]Day 1[/td][td=bgcolor:#5B9BD5]Day 2[/td][td=bgcolor:#5B9BD5]Day 3[/td][td=bgcolor:#5B9BD5]Day 4[/td][td=bgcolor:#5B9BD5]Day 5[/td][td=bgcolor:#5B9BD5]Day 6[/td][td=bgcolor:#5B9BD5]Day 7[/td][td=bgcolor:#70AD47]Average[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Time Went to Bed[/td][td=bgcolor:#DDEBF7]

9:45 PM​

[/td][td=bgcolor:#DDEBF7]

9:15 PM​

[/td][td=bgcolor:#DDEBF7]

9:00 PM​

[/td][td=bgcolor:#DDEBF7]

9:30 PM​

[/td][td=bgcolor:#DDEBF7]

9:00 PM​

[/td][td=bgcolor:#DDEBF7]

9:15 PM​

[/td][td=bgcolor:#DDEBF7]

10:00 PM​

[/td][td=bgcolor:#E2EFDA]

0.21.23.34​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Time Woke Up[/td][td]

6:00 AM​

[/td][td]

6:15 PM​

[/td][td]

6:30 PM​

[/td][td]

6:00 AM​

[/td][td]

6:00 PM​

[/td][td]

6:30 PM​

[/td][td]

7:30 AM​

[/td][td]

0.13.15.00​

[/td][/tr]
[/table]

for last column Custom Format: d.hh.mm.ss
or hh:mm:ss AM/PM

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Average[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]

09:23:34 PM​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

01:15:00 PM​

[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Avg = Table.AddColumn(Source, "Average", each List.Average({[Day 1], [Day 2], [Day 3], [Day 4], [Day 5], [Day 6], [Day 7]}), type number),
    ROC = Table.SelectColumns(Avg,{"Average"})
in
    ROC[/SIZE]