Date and Time to Time Range Formulas

Copper Contributor

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

Re: Date and Time to Time Range Formulas

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

Re: Date and Time to Time Range Formulas

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.

Re: Date and Time to Time Range Formulas

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