Forum Discussion

anwarsafian's avatar
anwarsafian
Copper Contributor
Oct 21, 2023

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...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 21, 2023

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

Resources