Formula for if time is same, earlier or later?

tblackwell

New Member
Joined
Oct 24, 2018
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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!


ARST
1StoreWinter Hours TY Close TimeCurrent Summer Hours Close TimeTime Difference Winter versus Summer
2600111:00:00 PM11:00:00 PMSAME
3600211:00:00 PM12:00:00 AMEARLIER
4600311:00:00 PM12:00:00 AMEARLIER
5600412:00:00 AM11:00:00 PMEARLIER
6600511:00:00 PM12:00:00 AMEARLIER
7600612:00:00 AM12:00:00 AMEARLIER
8600711:00:00 PM12:00:00 AMEARLIER
9600812:00:00 AM12:00:00 AMEARLIER
10600910:00:00 PM12:00:00 AMEARLIER
11601011:00:00 PM12:00:00 AMEARLIER
12601110:00:00 PM11:00:00 PMLATER
13601210:00:00 PM11:00:00 PMLATER
14601312:00:00 AM12:00:00 AMEARLIER
15601411:00:00 PM12:00:00 AMEARLIER
16601511:00:00 PM12:00:00 AMEARLIER
17601711:00:00 PM12:00:00 AMEARLIER
18601812:00:00 AM12:00:00 AMEARLIER
19601910:00:00 PM10:00:00 PMLATER
20602111:00:00 PM12:00:00 AMEARLIER
21602210:00:00 PM11:00:00 PMLATER
22602311:00:00 PM12:00:00 AMEARLIER
23602411:00:00 PM11:00:00 PMSAME
24602510:00:00 PM11:00:00 PMLATER
25602611:00:00 PM12:00:00 AMEARLIER
26602711:00:00 PM11:00:00 PMSAME
27602810:00:00 PM11:00:00 PMLATER
28602911:00:00 PM12:00:00 AMEARLIER
29603110:00:00 PM11:00:00 PMLATER
30603210:00:00 PM11:00:00 PMLATER
31603311:00:00 PM12:00:00 AMEARLIER
32603411:00:00 PM12:00:00 AMEARLIER
33603510:00:00 PM11:00:00 PMLATER
34603611:00:00 PM12:00:00 AMEARLIER
35603711:00:00 PM12:00:00 AMEARLIER
36603812:00:00 AM12:00:00 AMEARLIER
37605010:00:00 PM12:00:00 AMEARLIER
38605110:00:00 PM11:00:00 PMLATER
39605212:00:00 AM1:00:00 AMEARLIER
40605310:00:00 PM12:00:00 AMEARLIER
41605411:00:00 PM12:00:00 AMEARLIER
42605511:00:00 PM12:00:00 AMEARLIER
43605710:00:00 PM11:00:00 PMLATER
44605810:00:00 PM11:00:00 PMLATER
45605910:00:00 PM11:00:00 PMLATER
46606010:00:00 PM10:00:00 PMEARLIER
47606111:00:00 PM12:00:00 AMEARLIER
48606211:00:00 PM12:00:00 AMEARLIER
49606310:00:00 PM12:00:00 AMEARLIER
50606411:00:00 PM1:00:00 AMEARLIER

<tbody>
</tbody>
Weekday_Sales

Worksheet Formulas
CellFormula
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")))
 
Last edited by a moderator:

tblackwell

New Member
Joined
Oct 24, 2018
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
[COLOR=#333333]=MOD([/COLOR][COLOR=Blue]R2,1[/COLOR][COLOR=#333333])
[/COLOR]
with this:
Code:
=IF(MOD(R2,1)=0,1,MOD(R2,1))

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

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,910
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Doh! I got caught in a VBA way of thinking!

You don't actual need to use:
Code:
R2-TRUNC(R2)
and can use the simpler:
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,330
Messages
5,635,637
Members
416,869
Latest member
JeffK26

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top