Excel Start Date with specific start Time

Question

Date and time data is imported into excel from an outside source (excel see the imported data as Text so column Q is converting to date/time that excel will recognize).

I am using multiple formulas to get the "Shift Start" date/ time for the imported date information. The shift can start at 6:00 or 7:00 AM and PM.

The formula in T2 works great when "6:00" is entered but does not work if I want it to be "7:00".

Is there a better formula to use that will guarantee that the time will always be 6 or 7 (if specified) while keeping the AM/ PM based on the Oldest Date?

*In the example below, the green formulas are what is being used for each column.

Solution

FLOOR function:
Rounds number down, toward zero, to the nearest multiple of significance.

You're just lucky with 6:00 as that divides one day (24 hours) without remainders.

To "round down" to previous 6:00 / 7:00 you need to:

• subtract 6:00 / 7:00 from the time value
• round it down to half day
• add 6:00 / 7:00 to it

In a formula:
=FLOOR(A1-"6:00",0.5)+"6:00"