Formula for if time is same, earlier or later?

[tblackwell]

QUESTION: Can you help me create a formula that looks at time in two different columns and determines if the time is the same, earlier or later? For perspective, this is to determine difference in closing time this winter compared to this summer.

Sample data below.

CURRENT FORMULA: =IF(R2=S2,"SAME",IF(R2>S2,"EARLIER",IF(R2<s2,"later")))<s2,"later")))<s2,"later")))


Issues: This formula thinks that 12:00:00 AM is earlier than 11:00:00 PM, that 10:00:00 PM is later than 11:00:00 PM, 1:00:00 AM is earlier than 12:00:00 AM, and so forth.

DESIRED OUTCOME: If column R is closing earlier than column S then "earlier", if column R is closing later than column S then "later", if column R is same time as column S then "same"

Windows 10 Pro, Office 365

Please help!

	A	R	S	T
Store	Winter Hours TY Close Time	Current Summer Hours Close Time	Time Difference Winter versus Summer
SAME
EARLIER
EARLIER
EARLIER
EARLIER
EARLIER
EARLIER
EARLIER
EARLIER
EARLIER
LATER
LATER
EARLIER
EARLIER
EARLIER
EARLIER
EARLIER
LATER
EARLIER
LATER
EARLIER
SAME
LATER
EARLIER
SAME
LATER
EARLIER
LATER
LATER
EARLIER
EARLIER
LATER
EARLIER
EARLIER
EARLIER
EARLIER
LATER
EARLIER
EARLIER
EARLIER
EARLIER
LATER
LATER
LATER
EARLIER
EARLIER
EARLIER
EARLIER
EARLIER

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]6001[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]11:00:00 PM[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]6002[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]6003[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]6004[/TD]
[TD="align: right"]12:00:00 AM[/TD]
[TD="align: right"]11:00:00 PM[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6005[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]6006[/TD]
[TD="align: right"]12:00:00 AM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]6007[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]6008[/TD]
[TD="align: right"]12:00:00 AM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]6009[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]6010[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]6011[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]11:00:00 PM[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]6012[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]11:00:00 PM[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]6013[/TD]
[TD="align: right"]12:00:00 AM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]6014[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]6015[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]6017[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]6018[/TD]
[TD="align: right"]12:00:00 AM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]6019[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]10:00:00 PM[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]6021[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"]6022[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]11:00:00 PM[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"]6023[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"]6024[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]11:00:00 PM[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"]6025[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]11:00:00 PM[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"]6026[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]26[/TD]
[TD="align: right"]6027[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]11:00:00 PM[/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]6028[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]11:00:00 PM[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]6029[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"]6031[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]11:00:00 PM[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"]6032[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]11:00:00 PM[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"]6033[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]32[/TD]
[TD="align: right"]6034[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]33[/TD]
[TD="align: right"]6035[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]11:00:00 PM[/TD]

[TD="align: center"]34[/TD]
[TD="align: right"]6036[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]35[/TD]
[TD="align: right"]6037[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]6038[/TD]
[TD="align: right"]12:00:00 AM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]6050[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]6051[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]11:00:00 PM[/TD]

[TD="align: center"]39[/TD]
[TD="align: right"]6052[/TD]
[TD="align: right"]12:00:00 AM[/TD]
[TD="align: right"]1:00:00 AM[/TD]

[TD="align: center"]40[/TD]
[TD="align: right"]6053[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]41[/TD]
[TD="align: right"]6054[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]42[/TD]
[TD="align: right"]6055[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]43[/TD]
[TD="align: right"]6057[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]11:00:00 PM[/TD]

[TD="align: center"]44[/TD]
[TD="align: right"]6058[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]11:00:00 PM[/TD]

[TD="align: center"]45[/TD]
[TD="align: right"]6059[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]11:00:00 PM[/TD]

[TD="align: center"]46[/TD]
[TD="align: right"]6060[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]10:00:00 PM[/TD]

[TD="align: center"]47[/TD]
[TD="align: right"]6061[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]48[/TD]
[TD="align: right"]6062[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]49[/TD]
[TD="align: right"]6063[/TD]
[TD="align: right"]10:00:00 PM[/TD]
[TD="align: right"]12:00:00 AM[/TD]

[TD="align: center"]50[/TD]
[TD="align: right"]6064[/TD]
[TD="align: right"]11:00:00 PM[/TD]
[TD="align: right"]1:00:00 AM[/TD]

</tbody>

Weekday_Sales​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]R2[/TH]
[TD="align: left"]=IF(Q2="SAME",J2,IF(AND(J2=$W$10,Q2="EARLIER"),$W$10,IF(Q2="EARLIER",J2-TIME(1,0,0),J2+TIME(1,0,0))))[/TD]
[/TR]
[TR]
[TH]S2[/TH]
[TD="align: left"]=VLOOKUP(A2,'S:\Scratch\Chris Cole\Labor Model 4.1\NBO\Gold Standard - Master Paramters & Hours\[2019 Master Guide with Hours - AT.xls]Master'!$B$15:$H$405,7,FALSE)[/TD]
[/TR]
[TR]
[TH]T2
[/TH]
[TD="align: left"]=IF(R2=S2,"SAME",IF(R2>S2,"EARLIER",IF(R2<s2,"later"< font="">)</s2,"later"<>))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


</s2,"later")))<s2,"later")))<s2,"later")))

 

[tblackwell]

Regarding the time of 12:00 AM, that would be a date/time number with no time piece, so the fractional part would be zero.
So, to get it to be greater than all the the other ones, you could add one day (24 hours) to it.

So you could replace this:

[COLOR=#333333]=MOD([/COLOR][COLOR=Blue]R2,1[/COLOR][COLOR=#333333])
[/COLOR]

with this:

=IF(MOD(R2,1)=0,1,MOD(R2,1))

Great idea thanks, I would have never thought of that!

 

[Joe4]

You are welcome.

 

[Scott Huish]

Doh! I got caught in a VBA way of thinking!

You don't actual need to use:

R2-TRUNC(R2)

and can use the simpler:

MOD(R2,1)

instead (as MOD returns the remainder of the first number when divided by the second number).

I forgot that while VBA does not have a MOD remainder function, Excel does!
(Thanks for the reminder, Scott!)

So everything I said in my initial reply is still true, Scott just pointed out a slightly simpler way of dealing with it.

VBA does have a MOD function, however it only returns integer results, so would not be useful here.