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:

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
This formula thinks that 12:00:00 AM is earlier than 11:00:00 PM

Technically, this is true. 12:00 AM is midnight, the beginning of the day ("0:00" in Military time).
If you want to say that it should be the end of the day, you will need to account for that explicitly in your IF formula.


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.
The only way Excel would think that is if the "date" parts of the entires are different.
It is important to note that even if you do not show the date parts, ithey are still there. If you temporarily change the format of your cells to see the date, then it will be obvious what it is comparing, and should explain the results you are seeing.

It is important to understand how Excel stores dates and times. It stores them as numbers, specifically the number of days since 1/0/1900. And time is just a fractional component of one day. Then the number is formatted with a Date/Time format for display purposes. If you change the format of any valid Date/Time entry to "General", you will see it exactly as Excel does.

If you want to compare strictly the time component of each cell (and ignore any day component), you can do that by only comparing the decimal portions of each entry. So, if you have an entry in cell A1, and want only the fractional time compenent, you can remove the date piece by getting rid of the whole number part, just leaving the fraction like this:
=R2-TRUNC(R2)

​So, if you wanted to compare the time components only of R2 ands S2, it would start out something like this:
Code:
=IF((R2-TRUNC(R2))=(S2-TRUNC(S2)),...
 
Last edited:

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
If there are dates in the cells with the times, you could eliminate the nested IF like this:

=TEXT(MOD(S2,1)-MOD(R2,1),"""LATER"";""EARLIER"";""SAME""")
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
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.
 
Last edited:

tblackwell

New Member
Joined
Oct 24, 2018
Messages
28
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If there are dates in the cells with the times, you could eliminate the nested IF like this:

=TEXT(MOD(S2,1)-MOD(R2,1),"""LATER"";""EARLIER"";""SAME""")

@Scott - Good idea, unfortunately no dates are in the cells. :(
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
Good idea, unfortunately no dates are in the cells.
Why do you say that? Did you try to expand them out, like I said?
Choose one of the Formats that has both Date and Time, and see what it shows the contents of your cells are.

Even if they were to have no Date component (or I should more accurately say, a zero date component, as they ALL have date components), Scott's function would not hurt anything and would still work.

If you have something that is not working for you, please pick a particular example that is not working, post the data values you are comparing after you change the cell formats to show both Date and Time, and show us the formula you are using (after making Scott's edits), what it is returning, and what you expect it to return.
 

tblackwell

New Member
Joined
Oct 24, 2018
Messages
28
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@Joe4 good idea!

Any ideas on "tricking" excel to think that 11pm is "earlier" than 12am?

I tried this but failed: =IF(AND(T2=TIME(23,0,0),S2=TIME(0,0,0)),"EARLIER",IF(T2=U2,"SAME",IF(T2<u2,"earlier",if(t2>U2,"LATER"))))

See row 6 for example...


ARSTUV
1StoreWinter Hours TY Close TimeCurrent Summer Hours Close TimeMOD Winter Hours TY Close TimeMOD Current Summer Hours Close TimeTIME CHANGE THIS WINTER TO THIS SUMMER
2600111:00:00 PM11:00:00 PM11:00:00 PM11:00:00 PMSAME
3600211:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
4600311:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
5600411:00:00 PM11:00:00 PM11:00:00 PM11:00:00 PMSAME
6600511:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
7600612:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AMLATER
8600711:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
9600811:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
10600910:00:00 PM12:00:00 AM10:00:00 PM12:00:00 AMLATER
11601011:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
12601110:00:00 PM11:00:00 PM10:00:00 PM11:00:00 PMEARLIER
1360129:00:00 PM11:00:00 PM9:00:00 PM11:00:00 PMEARLIER
14601312:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AMLATER
15601411:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
16601511:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
17601711:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
18601812:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AMLATER
1960199:00:00 PM10:00:00 PM9:00:00 PM10:00:00 PMEARLIER
20602111:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
21602210:00:00 PM11:00:00 PM10:00:00 PM11:00:00 PMEARLIER
22602310:00:00 PM12:00:00 AM10:00:00 PM12:00:00 AMLATER
23602411:00:00 PM11:00:00 PM11:00:00 PM11:00:00 PMSAME
24602510:00:00 PM11:00:00 PM10:00:00 PM11:00:00 PMEARLIER
25602611:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
26602711:00:00 PM11:00:00 PM11:00:00 PM11:00:00 PMSAME
27602810:00:00 PM11:00:00 PM10:00:00 PM11:00:00 PMEARLIER
28602911:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
29603110:00:00 PM11:00:00 PM10:00:00 PM11:00:00 PMEARLIER
30603210:00:00 PM11:00:00 PM10:00:00 PM11:00:00 PMEARLIER
31603311:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
32603411:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
33603510:00:00 PM11:00:00 PM10:00:00 PM11:00:00 PMEARLIER
34603611:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
35603711:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
36603812:00:00 AM12:00:00 AM12:00:00 AM12:00:00 AMLATER
37605010:00:00 PM12:00:00 AM10:00:00 PM12:00:00 AMLATER
38605110:00:00 PM11:00:00 PM10:00:00 PM11:00:00 PMEARLIER
39605212:00:00 AM1:00:00 AM12:00:00 AM1:00:00 AMLATER
40605310:00:00 PM12:00:00 AM10:00:00 PM12:00:00 AMLATER
41605411:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
42605511:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
43605710:00:00 PM11:00:00 PM10:00:00 PM11:00:00 PMEARLIER
44605810:00:00 PM11:00:00 PM10:00:00 PM11:00:00 PMEARLIER
45605910:00:00 PM11:00:00 PM10:00:00 PM11:00:00 PMEARLIER
46606010:00:00 PM10:00:00 PM10:00:00 PM10:00:00 PMLATER
47606111:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
48606211:00:00 PM12:00:00 AM11:00:00 PM12:00:00 AMEARLIER
49606310:00:00 PM12:00:00 AM10:00:00 PM12:00:00 AMLATER
50606411:00:00 PM1:00:00 AM11:00:00 PM1:00:00 AMLATER

<tbody>
</tbody>
Weekday_Sales

Worksheet Formulas
CellFormula
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=MOD(R2,1)
U2=MOD(S2,1)
V2=IF(<font color="Blue">AND(T2=TIME(23,0,0),S2=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T2=U2,"SAME",IF(T2<u2,"earlier",if(T2>U2,"LATER"</u2,"earlier",if())))
S3=VLOOKUP(A3,'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)
T3=MOD(R3,1)
U3=MOD(S3,1)
V3=IF(<font color="Blue">AND(T3=TIME(23,0,0),S3=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T3=U3,"SAME",IF(T3<u3,"earlier",if(T3>U3,"LATER"</u3,"earlier",if())))
S4=VLOOKUP(A4,'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)
T4=MOD(R4,1)
U4=MOD(S4,1)
V4=IF(<font color="Blue">AND(T4=TIME(23,0,0),S4=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T4=U4,"SAME",IF(T4<u4,"earlier",if(T4>U4,"LATER"</u4,"earlier",if())))
S5=VLOOKUP(A5,'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)
T5=MOD(R5,1)
U5=MOD(S5,1)
V5=IF(<font color="Blue">AND(T5=TIME(23,0,0),S5=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T5=U5,"SAME",IF(T5<u5,"earlier",if(T5>U5,"LATER"</u5,"earlier",if())))
S6=VLOOKUP(A6,'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)
T6=MOD(R6,1)
U6=MOD(S6,1)
V6=IF(<font color="Blue">AND(T6=TIME(23,0,0),S6=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T6=U6,"SAME",IF(T6<u6,"earlier",if(T6>U6,"LATER"</u6,"earlier",if())))
S7=VLOOKUP(A7,'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)
T7=MOD(R7,1)
U7=MOD(S7,1)
V7=IF(<font color="Blue">AND(T7=TIME(23,0,0),S7=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T7=U7,"SAME",IF(T7<u7,"earlier",if(T7>U7,"LATER"</u7,"earlier",if())))
S8=VLOOKUP(A8,'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)
T8=MOD(R8,1)
U8=MOD(S8,1)
V8=IF(<font color="Blue">AND(T8=TIME(23,0,0),S8=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T8=U8,"SAME",IF(T8<u8,"earlier",if(T8>U8,"LATER"</u8,"earlier",if())))
S9=VLOOKUP(A9,'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)
T9=MOD(R9,1)
U9=MOD(S9,1)
V9=IF(<font color="Blue">AND(T9=TIME(23,0,0),S9=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T9=U9,"SAME",IF(T9<u9,"earlier",if(T9>U9,"LATER"</u9,"earlier",if())))
S10=VLOOKUP(A10,'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)
T10=MOD(R10,1)
U10=MOD(S10,1)
V10=IF(<font color="Blue">AND(T10=TIME(23,0,0),S10=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T10=U10,"SAME",IF(T10<u10,"earlier",if(T10>U10,"LATER"</u10,"earlier",if())))
S11=VLOOKUP(A11,'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)
T11=MOD(R11,1)
U11=MOD(S11,1)
V11=IF(<font color="Blue">AND(T11=TIME(23,0,0),S11=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T11=U11,"SAME",IF(T11<u11,"earlier",if(T11>U11,"LATER"</u11,"earlier",if())))
S12=VLOOKUP(A12,'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)
T12=MOD(R12,1)
U12=MOD(S12,1)
V12=IF(<font color="Blue">AND(T12=TIME(23,0,0),S12=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T12=U12,"SAME",IF(T12<u12,"earlier",if(T12>U12,"LATER"</u12,"earlier",if())))
S13=VLOOKUP(A13,'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)
T13=MOD(R13,1)
U13=MOD(S13,1)
V13=IF(<font color="Blue">AND(T13=TIME(23,0,0),S13=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T13=U13,"SAME",IF(T13<u13,"earlier",if(T13>U13,"LATER"</u13,"earlier",if())))
S14=VLOOKUP(A14,'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)
T14=MOD(R14,1)
U14=MOD(S14,1)
V14=IF(<font color="Blue">AND(T14=TIME(23,0,0),S14=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T14=U14,"SAME",IF(T14<u14,"earlier",if(T14>U14,"LATER"</u14,"earlier",if())))
S15=VLOOKUP(A15,'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)
T15=MOD(R15,1)
U15=MOD(S15,1)
V15=IF(<font color="Blue">AND(T15=TIME(23,0,0),S15=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T15=U15,"SAME",IF(T15<u15,"earlier",if(T15>U15,"LATER"</u15,"earlier",if())))
S16=VLOOKUP(A16,'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)
T16=MOD(R16,1)
U16=MOD(S16,1)
V16=IF(<font color="Blue">AND(T16=TIME(23,0,0),S16=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T16=U16,"SAME",IF(T16<u16,"earlier",if(T16>U16,"LATER"</u16,"earlier",if())))
S17=VLOOKUP(A17,'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)
T17=MOD(R17,1)
U17=MOD(S17,1)
V17=IF(<font color="Blue">AND(T17=TIME(23,0,0),S17=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T17=U17,"SAME",IF(T17<u17,"earlier",if(T17>U17,"LATER"</u17,"earlier",if())))
S18=VLOOKUP(A18,'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)
T18=MOD(R18,1)
U18=MOD(S18,1)
V18=IF(<font color="Blue">AND(T18=TIME(23,0,0),S18=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T18=U18,"SAME",IF(T18<u18,"earlier",if(T18>U18,"LATER"</u18,"earlier",if())))
S19=VLOOKUP(A19,'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)
T19=MOD(R19,1)
U19=MOD(S19,1)
V19=IF(<font color="Blue">AND(T19=TIME(23,0,0),S19=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T19=U19,"SAME",IF(T19<u19,"earlier",if(T19>U19,"LATER"</u19,"earlier",if())))
S20=VLOOKUP(A20,'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)
T20=MOD(R20,1)
U20=MOD(S20,1)
V20=IF(<font color="Blue">AND(T20=TIME(23,0,0),S20=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T20=U20,"SAME",IF(T20<u20,"earlier",if(T20>U20,"LATER"</u20,"earlier",if())))
S21=VLOOKUP(A21,'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)
T21=MOD(R21,1)
U21=MOD(S21,1)
V21=IF(<font color="Blue">AND(T21=TIME(23,0,0),S21=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T21=U21,"SAME",IF(T21<u21,"earlier",if(T21>U21,"LATER"</u21,"earlier",if())))
S22=VLOOKUP(A22,'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)
T22=MOD(R22,1)
U22=MOD(S22,1)
V22=IF(<font color="Blue">AND(T22=TIME(23,0,0),S22=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T22=U22,"SAME",IF(T22<u22,"earlier",if(T22>U22,"LATER"</u22,"earlier",if())))
R2=IF(Q2="SAME",J2,IF(AND(J2=$Y$10,Q2="EARLIER"),$Y$10,IF(Q2="EARLIER",J2-TIME(1,0,0),J2+TIME(1,0,0))))
R3=IF(Q3="SAME",J3,IF(AND(J3=$Y$10,Q3="EARLIER"),$Y$10,IF(Q3="EARLIER",J3-TIME(1,0,0),J3+TIME(1,0,0))))
R4=IF(Q4="SAME",J4,IF(AND(J4=$Y$10,Q4="EARLIER"),$Y$10,IF(Q4="EARLIER",J4-TIME(1,0,0),J4+TIME(1,0,0))))
R5=IF(Q5="SAME",J5,IF(AND(J5=$Y$10,Q5="EARLIER"),$Y$10,IF(Q5="EARLIER",J5-TIME(1,0,0),J5+TIME(1,0,0))))
R6=IF(Q6="SAME",J6,IF(AND(J6=$Y$10,Q6="EARLIER"),$Y$10,IF(Q6="EARLIER",J6-TIME(1,0,0),J6+TIME(1,0,0))))
R7=IF(Q7="SAME",J7,IF(AND(J7=$Y$10,Q7="EARLIER"),$Y$10,IF(Q7="EARLIER",J7-TIME(1,0,0),J7+TIME(1,0,0))))
R8=IF(Q8="SAME",J8,IF(AND(J8=$Y$10,Q8="EARLIER"),$Y$10,IF(Q8="EARLIER",J8-TIME(1,0,0),J8+TIME(1,0,0))))
R9=IF(Q9="SAME",J9,IF(AND(J9=$Y$10,Q9="EARLIER"),$Y$10,IF(Q9="EARLIER",J9-TIME(1,0,0),J9+TIME(1,0,0))))
R10=IF(Q10="SAME",J10,IF(AND(J10=$Y$10,Q10="EARLIER"),$Y$10,IF(Q10="EARLIER",J10-TIME(1,0,0),J10+TIME(1,0,0))))
R11=IF(Q11="SAME",J11,IF(AND(J11=$Y$10,Q11="EARLIER"),$Y$10,IF(Q11="EARLIER",J11-TIME(1,0,0),J11+TIME(1,0,0))))
R12=IF(Q12="SAME",J12,IF(AND(J12=$Y$10,Q12="EARLIER"),$Y$10,IF(Q12="EARLIER",J12-TIME(1,0,0),J12+TIME(1,0,0))))
R14=IF(Q14="SAME",J14,IF(AND(J14=$Y$10,Q14="EARLIER"),$Y$10,IF(Q14="EARLIER",J14-TIME(1,0,0),J14+TIME(1,0,0))))
R15=IF(Q15="SAME",J15,IF(AND(J15=$Y$10,Q15="EARLIER"),$Y$10,IF(Q15="EARLIER",J15-TIME(1,0,0),J15+TIME(1,0,0))))
R16=IF(Q16="SAME",J16,IF(AND(J16=$Y$10,Q16="EARLIER"),$Y$10,IF(Q16="EARLIER",J16-TIME(1,0,0),J16+TIME(1,0,0))))
R17=IF(Q17="SAME",J17,IF(AND(J17=$Y$10,Q17="EARLIER"),$Y$10,IF(Q17="EARLIER",J17-TIME(1,0,0),J17+TIME(1,0,0))))
R18=IF(Q18="SAME",J18,IF(AND(J18=$Y$10,Q18="EARLIER"),$Y$10,IF(Q18="EARLIER",J18-TIME(1,0,0),J18+TIME(1,0,0))))
R20=IF(Q20="SAME",J20,IF(AND(J20=$Y$10,Q20="EARLIER"),$Y$10,IF(Q20="EARLIER",J20-TIME(1,0,0),J20+TIME(1,0,0))))
R21=IF(Q21="SAME",J21,IF(AND(J21=$Y$10,Q21="EARLIER"),$Y$10,IF(Q21="EARLIER",J21-TIME(1,0,0),J21+TIME(1,0,0))))
R23=IF(Q23="SAME",J23,IF(AND(J23=$Y$10,Q23="EARLIER"),$Y$10,IF(Q23="EARLIER",J23-TIME(1,0,0),J23+TIME(1,0,0))))
S23=VLOOKUP(A23,'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)
T23=MOD(R23,1)
U23=MOD(S23,1)
V23=IF(<font color="Blue">AND(T23=TIME(23,0,0),S23=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T23=U23,"SAME",IF(T23<u23,"earlier",if(T23>U23,"LATER"</u23,"earlier",if())))
R24=IF(Q24="SAME",J24,IF(AND(J24=$Y$10,Q24="EARLIER"),$Y$10,IF(Q24="EARLIER",J24-TIME(1,0,0),J24+TIME(1,0,0))))
S24=VLOOKUP(A24,'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)
T24=MOD(R24,1)
U24=MOD(S24,1)
V24=IF(<font color="Blue">AND(T24=TIME(23,0,0),S24=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T24=U24,"SAME",IF(T24<u24,"earlier",if(T24>U24,"LATER"</u24,"earlier",if())))
R25=IF(Q25="SAME",J25,IF(AND(J25=$Y$10,Q25="EARLIER"),$Y$10,IF(Q25="EARLIER",J25-TIME(1,0,0),J25+TIME(1,0,0))))
S25=VLOOKUP(A25,'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)
T25=MOD(R25,1)
U25=MOD(S25,1)
V25=IF(<font color="Blue">AND(T25=TIME(23,0,0),S25=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T25=U25,"SAME",IF(T25<u25,"earlier",if(T25>U25,"LATER"</u25,"earlier",if())))
R26=IF(Q26="SAME",J26,IF(AND(J26=$Y$10,Q26="EARLIER"),$Y$10,IF(Q26="EARLIER",J26-TIME(1,0,0),J26+TIME(1,0,0))))
S26=VLOOKUP(A26,'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)
T26=MOD(R26,1)
U26=MOD(S26,1)
V26=IF(<font color="Blue">AND(T26=TIME(23,0,0),S26=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T26=U26,"SAME",IF(T26<u26,"earlier",if(T26>U26,"LATER"</u26,"earlier",if())))
R27=IF(Q27="SAME",J27,IF(AND(J27=$Y$10,Q27="EARLIER"),$Y$10,IF(Q27="EARLIER",J27-TIME(1,0,0),J27+TIME(1,0,0))))
S27=VLOOKUP(A27,'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)
T27=MOD(R27,1)
U27=MOD(S27,1)
V27=IF(<font color="Blue">AND(T27=TIME(23,0,0),S27=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T27=U27,"SAME",IF(T27<u27,"earlier",if(T27>U27,"LATER"</u27,"earlier",if())))
R28=IF(Q28="SAME",J28,IF(AND(J28=$Y$10,Q28="EARLIER"),$Y$10,IF(Q28="EARLIER",J28-TIME(1,0,0),J28+TIME(1,0,0))))
S28=VLOOKUP(A28,'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)
T28=MOD(R28,1)
U28=MOD(S28,1)
V28=IF(<font color="Blue">AND(T28=TIME(23,0,0),S28=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T28=U28,"SAME",IF(T28<u28,"earlier",if(T28>U28,"LATER"</u28,"earlier",if())))
R29=IF(Q29="SAME",J29,IF(AND(J29=$Y$10,Q29="EARLIER"),$Y$10,IF(Q29="EARLIER",J29-TIME(1,0,0),J29+TIME(1,0,0))))
S29=VLOOKUP(A29,'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)
T29=MOD(R29,1)
U29=MOD(S29,1)
V29=IF(<font color="Blue">AND(T29=TIME(23,0,0),S29=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T29=U29,"SAME",IF(T29<u29,"earlier",if(T29>U29,"LATER"</u29,"earlier",if())))
R30=IF(Q30="SAME",J30,IF(AND(J30=$Y$10,Q30="EARLIER"),$Y$10,IF(Q30="EARLIER",J30-TIME(1,0,0),J30+TIME(1,0,0))))
S30=VLOOKUP(A30,'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)
T30=MOD(R30,1)
U30=MOD(S30,1)
V30=IF(<font color="Blue">AND(T30=TIME(23,0,0),S30=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T30=U30,"SAME",IF(T30<u30,"earlier",if(T30>U30,"LATER"</u30,"earlier",if())))
R31=IF(Q31="SAME",J31,IF(AND(J31=$Y$10,Q31="EARLIER"),$Y$10,IF(Q31="EARLIER",J31-TIME(1,0,0),J31+TIME(1,0,0))))
S31=VLOOKUP(A31,'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)
T31=MOD(R31,1)
U31=MOD(S31,1)
V31=IF(<font color="Blue">AND(T31=TIME(23,0,0),S31=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T31=U31,"SAME",IF(T31<u31,"earlier",if(T31>U31,"LATER"</u31,"earlier",if())))
R32=IF(Q32="SAME",J32,IF(AND(J32=$Y$10,Q32="EARLIER"),$Y$10,IF(Q32="EARLIER",J32-TIME(1,0,0),J32+TIME(1,0,0))))
S32=VLOOKUP(A32,'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)
T32=MOD(R32,1)
U32=MOD(S32,1)
V32=IF(<font color="Blue">AND(T32=TIME(23,0,0),S32=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T32=U32,"SAME",IF(T32<u32,"earlier",if(T32>U32,"LATER"</u32,"earlier",if())))
R33=IF(Q33="SAME",J33,IF(AND(J33=$Y$10,Q33="EARLIER"),$Y$10,IF(Q33="EARLIER",J33-TIME(1,0,0),J33+TIME(1,0,0))))
S33=VLOOKUP(A33,'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)
T33=MOD(R33,1)
U33=MOD(S33,1)
V33=IF(<font color="Blue">AND(T33=TIME(23,0,0),S33=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T33=U33,"SAME",IF(T33<u33,"earlier",if(T33>U33,"LATER"</u33,"earlier",if())))
R34=IF(Q34="SAME",J34,IF(AND(J34=$Y$10,Q34="EARLIER"),$Y$10,IF(Q34="EARLIER",J34-TIME(1,0,0),J34+TIME(1,0,0))))
S34=VLOOKUP(A34,'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)
T34=MOD(R34,1)
U34=MOD(S34,1)
V34=IF(<font color="Blue">AND(T34=TIME(23,0,0),S34=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T34=U34,"SAME",IF(T34<u34,"earlier",if(T34>U34,"LATER"</u34,"earlier",if())))
R35=IF(Q35="SAME",J35,IF(AND(J35=$Y$10,Q35="EARLIER"),$Y$10,IF(Q35="EARLIER",J35-TIME(1,0,0),J35+TIME(1,0,0))))
S35=VLOOKUP(A35,'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)
T35=MOD(R35,1)
U35=MOD(S35,1)
V35=IF(<font color="Blue">AND(T35=TIME(23,0,0),S35=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T35=U35,"SAME",IF(T35<u35,"earlier",if(T35>U35,"LATER"</u35,"earlier",if())))
R36=IF(Q36="SAME",J36,IF(AND(J36=$Y$10,Q36="EARLIER"),$Y$10,IF(Q36="EARLIER",J36-TIME(1,0,0),J36+TIME(1,0,0))))
S36=VLOOKUP(A36,'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)
T36=MOD(R36,1)
U36=MOD(S36,1)
V36=IF(<font color="Blue">AND(T36=TIME(23,0,0),S36=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T36=U36,"SAME",IF(T36<u36,"earlier",if(T36>U36,"LATER"</u36,"earlier",if())))
R37=IF(Q37="SAME",J37,IF(AND(J37=$Y$10,Q37="EARLIER"),$Y$10,IF(Q37="EARLIER",J37-TIME(1,0,0),J37+TIME(1,0,0))))
S37=VLOOKUP(A37,'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)
T37=MOD(R37,1)
U37=MOD(S37,1)
V37=IF(<font color="Blue">AND(T37=TIME(23,0,0),S37=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T37=U37,"SAME",IF(T37<u37,"earlier",if(T37>U37,"LATER"</u37,"earlier",if())))
R38=IF(Q38="SAME",J38,IF(AND(J38=$Y$10,Q38="EARLIER"),$Y$10,IF(Q38="EARLIER",J38-TIME(1,0,0),J38+TIME(1,0,0))))
S38=VLOOKUP(A38,'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)
T38=MOD(R38,1)
U38=MOD(S38,1)
V38=IF(<font color="Blue">AND(T38=TIME(23,0,0),S38=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T38=U38,"SAME",IF(T38<u38,"earlier",if(T38>U38,"LATER"</u38,"earlier",if())))
R39=IF(Q39="SAME",J39,IF(AND(J39=$Y$10,Q39="EARLIER"),$Y$10,IF(Q39="EARLIER",J39-TIME(1,0,0),J39+TIME(1,0,0))))
S39=VLOOKUP(A39,'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)
T39=MOD(R39,1)
U39=MOD(S39,1)
V39=IF(<font color="Blue">AND(T39=TIME(23,0,0),S39=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T39=U39,"SAME",IF(T39<u39,"earlier",if(T39>U39,"LATER"</u39,"earlier",if())))
R40=IF(Q40="SAME",J40,IF(AND(J40=$Y$10,Q40="EARLIER"),$Y$10,IF(Q40="EARLIER",J40-TIME(1,0,0),J40+TIME(1,0,0))))
S40=VLOOKUP(A40,'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)
T40=MOD(R40,1)
U40=MOD(S40,1)
V40=IF(<font color="Blue">AND(T40=TIME(23,0,0),S40=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T40=U40,"SAME",IF(T40<u40,"earlier",if(T40>U40,"LATER"</u40,"earlier",if())))
R41=IF(Q41="SAME",J41,IF(AND(J41=$Y$10,Q41="EARLIER"),$Y$10,IF(Q41="EARLIER",J41-TIME(1,0,0),J41+TIME(1,0,0))))
S41=VLOOKUP(A41,'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)
T41=MOD(R41,1)
U41=MOD(S41,1)
V41=IF(<font color="Blue">AND(T41=TIME(23,0,0),S41=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T41=U41,"SAME",IF(T41<u41,"earlier",if(T41>U41,"LATER"</u41,"earlier",if())))
R42=IF(Q42="SAME",J42,IF(AND(J42=$Y$10,Q42="EARLIER"),$Y$10,IF(Q42="EARLIER",J42-TIME(1,0,0),J42+TIME(1,0,0))))
S42=VLOOKUP(A42,'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)
T42=MOD(R42,1)
U42=MOD(S42,1)
V42=IF(<font color="Blue">AND(T42=TIME(23,0,0),S42=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T42=U42,"SAME",IF(T42<u42,"earlier",if(T42>U42,"LATER"</u42,"earlier",if())))
R43=IF(Q43="SAME",J43,IF(AND(J43=$Y$10,Q43="EARLIER"),$Y$10,IF(Q43="EARLIER",J43-TIME(1,0,0),J43+TIME(1,0,0))))
S43=VLOOKUP(A43,'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)
T43=MOD(R43,1)
U43=MOD(S43,1)
V43=IF(<font color="Blue">AND(T43=TIME(23,0,0),S43=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T43=U43,"SAME",IF(T43<u43,"earlier",if(T43>U43,"LATER"</u43,"earlier",if())))
R44=IF(Q44="SAME",J44,IF(AND(J44=$Y$10,Q44="EARLIER"),$Y$10,IF(Q44="EARLIER",J44-TIME(1,0,0),J44+TIME(1,0,0))))
S44=VLOOKUP(A44,'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)
T44=MOD(R44,1)
U44=MOD(S44,1)
V44=IF(<font color="Blue">AND(T44=TIME(23,0,0),S44=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T44=U44,"SAME",IF(T44<u44,"earlier",if(T44>U44,"LATER"</u44,"earlier",if())))
R45=IF(Q45="SAME",J45,IF(AND(J45=$Y$10,Q45="EARLIER"),$Y$10,IF(Q45="EARLIER",J45-TIME(1,0,0),J45+TIME(1,0,0))))
S45=VLOOKUP(A45,'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)
T45=MOD(R45,1)
U45=MOD(S45,1)
V45=IF(<font color="Blue">AND(T45=TIME(23,0,0),S45=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T45=U45,"SAME",IF(T45<u45,"earlier",if(T45>U45,"LATER"</u45,"earlier",if())))
R46=IF(Q46="SAME",J46,IF(AND(J46=$Y$10,Q46="EARLIER"),$Y$10,IF(Q46="EARLIER",J46-TIME(1,0,0),J46+TIME(1,0,0))))
S46=VLOOKUP(A46,'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)
T46=MOD(R46,1)
U46=MOD(S46,1)
V46=IF(<font color="Blue">AND(T46=TIME(23,0,0),S46=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T46=U46,"SAME",IF(T46<u46,"earlier",if(T46>U46,"LATER"</u46,"earlier",if())))
R47=IF(Q47="SAME",J47,IF(AND(J47=$Y$10,Q47="EARLIER"),$Y$10,IF(Q47="EARLIER",J47-TIME(1,0,0),J47+TIME(1,0,0))))
S47=VLOOKUP(A47,'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)
T47=MOD(R47,1)
U47=MOD(S47,1)
V47=IF(<font color="Blue">AND(T47=TIME(23,0,0),S47=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T47=U47,"SAME",IF(T47<u47,"earlier",if(T47>U47,"LATER"</u47,"earlier",if())))
R48=IF(Q48="SAME",J48,IF(AND(J48=$Y$10,Q48="EARLIER"),$Y$10,IF(Q48="EARLIER",J48-TIME(1,0,0),J48+TIME(1,0,0))))
S48=VLOOKUP(A48,'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)
T48=MOD(R48,1)
U48=MOD(S48,1)
V48=IF(<font color="Blue">AND(T48=TIME(23,0,0),S48=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T48=U48,"SAME",IF(T48<u48,"earlier",if(T48>U48,"LATER"</u48,"earlier",if())))
R49=IF(Q49="SAME",J49,IF(AND(J49=$Y$10,Q49="EARLIER"),$Y$10,IF(Q49="EARLIER",J49-TIME(1,0,0),J49+TIME(1,0,0))))
S49=VLOOKUP(A49,'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)
T49=MOD(R49,1)
U49=MOD(S49,1)
V49=IF(<font color="Blue">AND(T49=TIME(23,0,0),S49=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T49=U49,"SAME",IF(T49<u49,"earlier",if(T49>U49,"LATER"</u49,"earlier",if())))
R50=IF(Q50="SAME",J50,IF(AND(J50=$Y$10,Q50="EARLIER"),$Y$10,IF(Q50="EARLIER",J50-TIME(1,0,0),J50+TIME(1,0,0))))
S50=VLOOKUP(A50,'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)
T50=MOD(R50,1)
U50=MOD(S50,1)
V50=IF(<font color="Blue">AND(T50=TIME(23,0,0),S50=TIME(0,0,0)),"EARLIER",IF(<font color="Red">T50=U50,"SAME",IF(T50<u50,"earlier",if(T50>U50,"LATER"</u50,"earlier",if())))

<tbody>
</tbody>

<tbody>
</tbody>


</u2,"earlier",if(t2>
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
Any ideas on "tricking" excel to think that 11pm is "earlier" than 12am?
Is that the only remainining issue?
If not, and there others, please change the format of all the time columns to "mm/dd/yyyy hh:mm" and post the data again, and let us know which row isn't working.
I am headed out for a while, but will be back later.
 
Last edited:

tblackwell

New Member
Joined
Oct 24, 2018
Messages
28
Office Version
  1. 365
Platform
  1. Windows
@Joe4 @Scott_Huish

Thanks for your help today. Unfortunately due to the time sensitivity of this project I had to move on to other things.

If you have any other ideas about how I might set this up for success next time, I am all ears. This is a twice a year project and this is the thing that I continually get stuck on. It's user error I'm sure, just wish there was an easier way to solve this.

Thanks.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,523
Messages
5,636,820
Members
416,943
Latest member
kitkat22

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