Need formula to obtain Date for date/time range 6:00:00am - 5:59:59am

I'm looking for a formula to extract the date to Column G (number). The date/time is in Column D in the format "m/d/yyyy h:mm" (Ex: 9/1/2012 12:01:21 AM).

The date will start at 6:00:00am and end at 5:59:59am the next day.

For example, 9/3/2012 12:01:21 AM or 9/3/2012 5:59:21 AM should return date 2.

Thank you.

To return the day (as a number) as you describe, try this:

CheshireCat said:

To return the day (as a number) as you describe, try this:

Code:

=DAY(D2-TIME(6,0,0))

Click to expand...

Thank you!