Excel formula for “earliest bedtime” incorrectly treating post-midnight times as earlier than evening times

Question

Excel formula for “earliest bedtime” incorrectly treating post-midnight times as earlier than evening times

Cass 0

Details for Background: I have recently created a sleep tracker workbook in Excel across 2 sheets:

Sheet 1: “Sleep Log”

Columns include:

Day
Date
Bedtime
Wake(s) Time
Get Up Time
Sleep Duration (calculated)
Number of Wakes
Notes
Helper column containing Month + Year

Excel - Sleep Log

Sheet 2: “Summary”

Columns include:

Month
Earliest Bedtime
Latest Bedtime
Earliest Get Up
Latest Get Up
Average Sleep Duration
Shortest Sleep
Longest Sleep
Average Wakes Per Night
Days With No Wakes
Nights Tracked

Excel - Summary

Notes on Issue

My issue is with calculating the Earliest Bedtime in the second sheet (Summary).

Example:

10:20 PM should be considered earlier than 12:15 AM

However, Excel is returning 12:15 AM as the earliest bedtime

I understand this happens because Excel stores times after midnight as smaller numbers.

My current formula for the Month of May is:
=IF(COUNTIF('Sleep Log'!$J:$J,$A7)=0,"",MINIFS('Sleep Log'!$C:$C,'Sleep Log'!$J:$J,$A7))

Additional information:

'Bedtime' values are currently stored/formatted as text rather than true Excel time values
The helper column groups entries by Month + Year
I want times after midnight (e.g. 12:15 AM, 1:00 AM, etc.) to be treated as belonging to the previous night for comparison purposes
I am using a monthly summary table (kind of explained already)

I attempted several formulas using:

LET
FILTER
TIMEVALUE
adding +1 to post-midnight times

But either:

the result stayed incorrect,
returned blank cells,
or returned 12:00 AM incorrectly.

What would be the best approach to correctly calculate the earliest bedtime in this situation?

0 comments

3 answers

Your answer

Answer 1

riny 20,870 Volunteer Moderator

@Cass

No need for a helper column. Try this:

=MINIFS('Sleep Log'!C:C, 'Sleep Log'!C:C, ">=" & TIME(20, 0, 0))

It assumes that a 'normal' bed time would never be before 8PM, i.e. 20:00:00 (hh:mm:ss).

I assume you can include the arguments for the applicable month(s) yourself. And perhaps better to limit the ranges used. Rather than C:C, use something like C11:C376. That will capture a full year, I believe.

0 comments

Answer 2

Cass 0

I ended up creating a helper column in the first sheet for the time using;
=IF(C11="","",

IF(IFERROR(TIMEVALUE(C11),C11)<TIME(12,0,0),

IFERROR(TIMEVALUE(C11),C11)+1,

IFERROR(TIMEVALUE(C11),C11)

)

and the formula for the summary became:
=IF(COUNTIFS('Sleep Log'!$K:$K,$A7,'Sleep Log'!$D:$D,"<>")=0,"",MOD(MINIFS('Sleep Log'!$D:$D,'Sleep Log'!$K:$K,$A7),1))

0 comments

Answer 3

Convert the bedtimes to real Date/Time values and adjust post‑midnight entries by adding 1 day before taking the minimum.

Assume:

Bedtime is in column C on sheet Sleep Log
Month+Year helper is in column J
Summary month is in A7

Use this formula in the Earliest Bedtime cell (e.g. B7):

=IF(
    COUNTIF('Sleep Log'!$J:$J,$A7)=0,
    "",
    TEXT(
        MINIFS(
            'Sleep Log'!$B:$B + 
            IF(TIMEVALUE('Sleep Log'!$C:$C)<TIMEVALUE("12:00 PM"),1,0),
            'Sleep Log'!$J:$J,$A7
        ),
        "h:mm AM/PM"
    )
)

Explanation of the key parts:

TIMEVALUE('Sleep Log'!$C:$C) converts the text bedtime to a time value.
IF(...<TIMEVALUE("12:00 PM"),1,0) adds 1 day to any bedtime before noon (i.e., after midnight), so 12:15 AM and 1:00 AM are treated as occurring after the previous evening.
MINIFS then finds the earliest adjusted bedtime for the month.
TEXT(...,"h:mm AM/PM") formats the result as a time.

This makes 10:20 PM earlier than 12:15 AM for the same night, while still grouping by the month in column J.

References:

Share via

Excel formula for “earliest bedtime” incorrectly treating post-midnight times as earlier than evening times

3 answers

Your answer