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
1	Store	Winter Hours TY Close Time	Current Summer Hours Close Time	Time Difference Winter versus Summer
2	6001	11:00:00 PM	11:00:00 PM	SAME
3	6002	11:00:00 PM	12:00:00 AM	EARLIER
4	6003	11:00:00 PM	12:00:00 AM	EARLIER
5	6004	12:00:00 AM	11:00:00 PM	EARLIER
6	6005	11:00:00 PM	12:00:00 AM	EARLIER
7	6006	12:00:00 AM	12:00:00 AM	EARLIER
8	6007	11:00:00 PM	12:00:00 AM	EARLIER
9	6008	12:00:00 AM	12:00:00 AM	EARLIER
10	6009	10:00:00 PM	12:00:00 AM	EARLIER
11	6010	11:00:00 PM	12:00:00 AM	EARLIER
12	6011	10:00:00 PM	11:00:00 PM	LATER
13	6012	10:00:00 PM	11:00:00 PM	LATER
14	6013	12:00:00 AM	12:00:00 AM	EARLIER
15	6014	11:00:00 PM	12:00:00 AM	EARLIER
16	6015	11:00:00 PM	12:00:00 AM	EARLIER
17	6017	11:00:00 PM	12:00:00 AM	EARLIER
18	6018	12:00:00 AM	12:00:00 AM	EARLIER
19	6019	10:00:00 PM	10:00:00 PM	LATER
20	6021	11:00:00 PM	12:00:00 AM	EARLIER
21	6022	10:00:00 PM	11:00:00 PM	LATER
22	6023	11:00:00 PM	12:00:00 AM	EARLIER
23	6024	11:00:00 PM	11:00:00 PM	SAME
24	6025	10:00:00 PM	11:00:00 PM	LATER
25	6026	11:00:00 PM	12:00:00 AM	EARLIER
26	6027	11:00:00 PM	11:00:00 PM	SAME
27	6028	10:00:00 PM	11:00:00 PM	LATER
28	6029	11:00:00 PM	12:00:00 AM	EARLIER
29	6031	10:00:00 PM	11:00:00 PM	LATER
30	6032	10:00:00 PM	11:00:00 PM	LATER
31	6033	11:00:00 PM	12:00:00 AM	EARLIER
32	6034	11:00:00 PM	12:00:00 AM	EARLIER
33	6035	10:00:00 PM	11:00:00 PM	LATER
34	6036	11:00:00 PM	12:00:00 AM	EARLIER
35	6037	11:00:00 PM	12:00:00 AM	EARLIER
36	6038	12:00:00 AM	12:00:00 AM	EARLIER
37	6050	10:00:00 PM	12:00:00 AM	EARLIER
38	6051	10:00:00 PM	11:00:00 PM	LATER
39	6052	12:00:00 AM	1:00:00 AM	EARLIER
40	6053	10:00:00 PM	12:00:00 AM	EARLIER
41	6054	11:00:00 PM	12:00:00 AM	EARLIER
42	6055	11:00:00 PM	12:00:00 AM	EARLIER
43	6057	10:00:00 PM	11:00:00 PM	LATER
44	6058	10:00:00 PM	11:00:00 PM	LATER
45	6059	10:00:00 PM	11:00:00 PM	LATER
46	6060	10:00:00 PM	10:00:00 PM	EARLIER
47	6061	11:00:00 PM	12:00:00 AM	EARLIER
48	6062	11:00:00 PM	12:00:00 AM	EARLIER
49	6063	10:00:00 PM	12:00:00 AM	EARLIER
50	6064	11:00:00 PM	1:00:00 AM	EARLIER

<tbody>
</tbody>

Weekday_Sales​

Worksheet Formulas Cell Formula R2 =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)))) S2 =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) T2 =IF(R2=S2,"SAME",IF(R2>S2,"EARLIER",IF(R2<s2,"later"< font="">)</s2,"later"<>)) <tbody> </tbody>

<tbody>
</tbody>

</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.