# Formula for if time is same, earlier or later?

#### tblackwell

##### New Member
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

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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

You are welcome.

#### Scott Huish

##### MrExcel MVP
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.

Replies
4
Views
152
Replies
5
Views
180
Replies
1
Views
198
Replies
0
Views
150
Replies
8
Views
335

1,130,362
Messages
5,641,703
Members
417,229
Latest member
BODYCOTE

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

### Which adblocker are you using?

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

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