Formula for if time is same, earlier or later?

T

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
StoreWinter Hours TY Close TimeCurrent Summer Hours Close TimeTime 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")))
 
Last edited by a moderator:
Sort by date Sort by votes
Joe4 said:
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))
Click to expand...

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

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Joe4 said:
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.
Click to expand...

VBA does have a MOD function, however it only returns integer results, so would not be useful here.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Hi. I'm trying to fill 1 for the time between arrival and departure.
Replies
1
Views
164
Asbestos_Jen
Asbestos_Jen
M
  • Question Question
Count #of Employees Worked Between Time Periods
Replies
3
Views
846
MrRajKumar
M
M
  • Solved
Difference between 2 hours doen't result in 0
Replies
3
Views
120
Fluff
Fluff
J
  • Question Question
XLOOKUP, Finding a Value based on Time and Date
Replies
1
Views
310
SunnyAlv
SunnyAlv
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

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