Forum Discussion
Date and Time to Time Range Formulas
Hi expert
I would like to know what is formula to group the time range as shown in below table regardless what the date is.
| Date and Time | Time Range |
| 7/31/23 9:29:58 AM | 9-10 AM |
| 7/31/23 1:56:00 PM | 1-2 PM |
| 8/1/23 9:11:43 AM | 9-10 AM |
| 8/3/23 5:38:44 PM | 5-6 PM |
| 8/4/23 3:39:13 PM | 3-4 PM |
I tried below formula but it never worked
=IF( AND( H3>DATEVALUE("1/1/1900"),H3<DATEVALUE("1/1/2100") ),
IF( AND( H3>=TIMEVALUE("09:00:00"),H3<TIMEVALUE("10:00:00") ), "9-10 am", "else" ), "false"
)
3 Replies
As variant for such sample
in C4
=IF( (HOUR(B4)=11) + (HOUR(B4)=23), TEXT(B4, "h AM/PM" ), TRIM( REPLACE( TEXT(B4, "h AM/PM" ), 3, 3, "") ) ) & "-" & TEXT(B4+1/24, "h AM/PM" )and drag it down.
In your initial formula you check if dates are below 1900 year. Not sure why, but if you expect wrong values in the source instead of dates, you may wrap above formula by IFERROR().
I attached an example to convert time to time value use MATCH function with "match_type=1" to group it.
MATCH function with "match_type=1" finds the largest value that is less than or equal to lookup_value.
I find it useful for grouping based on value.
anwarsafian To avoid potentially complex formulas, I would just create a list with the groupings from 0-1 AM to 11 PM-12 AM and use a simple INDEX function to pick-up the correct grouping based on the HOUR of the date/time. See attached.
