Date and Time to Time Range Formulas

[anwarsafian]

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"

      )

[#2]

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

[#3]

@anwarsafian 

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.

https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a#:~:te... 

[#4]

@anwarsafian 

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