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

sabesh

New member
Joined
Sep 13, 2012
Messages
2
Reaction score
0
Points
0
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.
 

CheshireCat

New member
Joined
Dec 30, 2011
Messages
121
Reaction score
0
Points
0
Location
Victoria, Canada
Excel Version(s)
Microsoft Excel 2013
To return the day (as a number) as you describe, try this:

Code:
=DAY(D2-TIME(6,0,0))
 
Top