Date and Time to Time Range Formulas

Copper Contributor

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 TimeTime Range
7/31/23 9:29:58 AM9-10 AM
7/31/23 1:56:00 PM1-2 PM
8/1/23 9:11:43 AM9-10 AM
8/3/23 5:38:44 PM5-6 PM
8/4/23 3:39:13 PM3-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

@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.



I attached an example to convert time to time value use MATCH function with "match_type=1" to group it.


Screenshot 2023-10-21 at 2.25.07 PM.png


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. 


Screenshot 2023-10-21 at 2.28.50 PM.png





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().