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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
159
Asbestos_Jen
Asbestos_Jen
M
  • Question Question
Count #of Employees Worked Between Time Periods
Replies
3
Views
821
MrRajKumar
M
J
  • Question Question
XLOOKUP, Finding a Value based on Time and Date
Replies
1
Views
302
SunnyAlv
SunnyAlv
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
247
dshtesy
D
Z
  • Solved
Formula to find 'Part of the Day' from the Time Column
Replies
8
Views
589
Zee996
Z

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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