Formula Guru please...

T

Toastis

Board Regular
Joined
Apr 20, 2011
Messages
65
I need to widdle this spreadsheet down to a manageable level and send a report to sr. mgmt of orders keyed late.

I tried a formula but it's not robust enough for the rules.

All Lube orders have to be in the day before they are delivered by 12PM.
We do not deliver on weekends so Monday orders have to go in by 12P Friday. We also do not work holidays.

Same day orders *could* be a violation unless they are will call orders, which if identified I can modify on a case by case basis.

FUEL orders have to be in before 3pm the day before they go out. Same day orders could be a violation of the cutoffs.

I added in column F to try to ascertain the difference in entered date vs. Delivery date. Then added in column i and J. It let still too much digging.

Does anyone have a formula suggestion?
Excel Workbook
BCDEFGHIJKL
1ORD DTENT DTDLV DTTypeElapsed DateENT SYSTimeLate or on timeCombo OKENT BYNAME
24/1/20114/1/20114/1/2011Lubes03/30/2011 0:0012:39:00 PMLateOKmbergINTRA-COMPANY TRANSFERS
34/1/20114/1/20114/1/2011Lubes03/30/2011 0:0012:42:00 PMLateOKmpohlINTRA-COMPANY TRANSFERS
44/1/20114/1/20114/1/2011Lubes03/31/2011 0:008:19:00 AMOn TimeOKmpohlINTRA-COMPANY TRANSFERS
54/1/20114/1/20114/4/2011Lubes33/31/2011 0:0011:50:00 AMOn TimeOKmpohlINTRA-COMPANY TRANSFERS
64/1/20114/1/20114/4/2011Fuel34/1/2011 0:005:53:00 AMOn TimeOKjeburcMANHEIM ALBQ AUTO AUCTION
74/1/20114/1/20114/4/2011Lubes34/1/2011 0:005:54:00 AMOn TimeOKjeburcCON FIRESTONE STORE # 013145
84/1/20114/1/20114/4/2011Lubes34/1/2011 0:005:54:00 AMOn TimeOKjeburcCON FIRESTONE STORE # 013227
94/1/20114/1/20114/4/2011Lubes34/1/2011 0:005:55:00 AMOn TimeOKjeburcCON FIRESTONE STORE # 028207
104/1/20114/1/20114/4/2011Lubes34/1/2011 0:005:55:00 AMOn TimeOKjeburcCON FIRESTONE STORE # 027219
114/1/20114/1/20114/4/2011Lubes34/1/2011 0:005:55:00 AMOn TimeOKjeburcCON FIRESTONE STORE # 013390
124/1/20114/1/20114/4/2011Lubes34/1/2011 0:005:56:00 AMOn TimeOKjeburcCON FIRESTONE STORE # 594253
134/1/20114/1/20114/4/2011Lubes34/1/2011 0:005:56:00 AMOn TimeOKjeburcCON FIRESTONE STORE # 594288
144/1/20114/1/20114/4/2011Lubes34/1/2011 0:005:56:00 AMOn TimeOKjeburcCON FIRESTONE
154/1/20114/1/20114/4/2011Lubes34/1/2011 0:005:56:00 AMOn TimeOKjeburcCON FIRESTONE STORE # 014443
164/1/20114/1/20114/4/2011Lubes34/1/2011 0:005:57:00 AMOn TimeOKjeburcCON FIRESTONE 4250 W RODEO RD
174/1/20114/1/20114/4/2011Lubes34/1/2011 0:005:57:00 AMOn TimeOKjeburcCON FIRESTONE STORE # 652164
184/1/20114/1/20114/4/2011Lubes34/1/2011 0:005:57:00 AMOn TimeOKjeburcCON FIRESTONE STORE #
194/1/20114/1/20114/2/2011Freight14/1/2011 0:006:14:00 AMOn TimeOKgimancWESTERN REFINING SW INC SW0270
204/1/20114/1/20114/2/2011Freight14/1/2011 0:006:16:00 AMOn TimeOKgimancWESTERN REFINING SW INC SW0270
214/1/20114/1/20114/2/2011Freight14/1/2011 0:006:18:00 AMOn TimeOKgimancWESTERN REFINING SW INC SW1733
224/1/20114/1/20114/6/2011Lubes54/1/2011 0:006:26:00 AMOn TimeOKgimancMOB TOYOTA TMMBC
234/1/20114/1/20114/6/2011Lubes54/1/2011 0:006:33:00 AMOn TimeOKgimancMOB TOYOTA TMMBC
244/1/20114/1/20114/1/2011Lubes04/1/2011 0:006:43:00 AMOn TimeNot OKgimancMOB MACK DEALER #F292
254/1/20114/1/20114/5/2011Lubes44/1/2011 0:006:52:00 AMOn TimeOKgimancSARGENT CONTROLS
264/1/20114/1/20114/4/2011Lubes34/1/2011 0:006:54:00 AMOn TimeOKdebedwMOB DE LA FUENTE CADILLAC
274/1/20114/1/20114/4/2011Lubes34/1/2011 0:006:56:00 AMOn TimeOKgimancCON FIRESTONE STORE # 013145
284/1/20114/1/20114/1/2011Freight04/1/2011 0:007:09:00 AMOn TimeNot OKmiedgeWESTERN REFINING SW INC (TC)
294/1/20114/1/20114/1/2011Freight04/1/2011 0:007:09:00 AMOn TimeNot OKmiedgeWESTERN REFINING SW INC (TC)
304/1/20114/1/20114/4/2011Lubes34/1/2011 0:007:09:00 AMOn TimeOKgimancMETRO HONDA
5737
Excel 2007
Cell Formulas
RangeFormula
I3=IF(H3>="12:00:00"+0,"Late","On Time")
I4=IF(H4>="12:00:00"+0,"Late","On Time")
I5=IF(H5>="12:00:00"+0,"Late","On Time")
I6=IF(H6>="12:00:00"+0,"Late","On Time")
I7=IF(H7>="12:00:00"+0,"Late","On Time")
I8=IF(H8>="12:00:00"+0,"Late","On Time")
I9=IF(H9>="12:00:00"+0,"Late","On Time")
I10=IF(H10>="12:00:00"+0,"Late","On Time")
I11=IF(H11>="12:00:00"+0,"Late","On Time")
I12=IF(H12>="12:00:00"+0,"Late","On Time")
I13=IF(H13>="12:00:00"+0,"Late","On Time")
I14=IF(H14>="12:00:00"+0,"Late","On Time")
I15=IF(H15>="12:00:00"+0,"Late","On Time")
I16=IF(H16>="12:00:00"+0,"Late","On Time")
I17=IF(H17>="12:00:00"+0,"Late","On Time")
I18=IF(H18>="12:00:00"+0,"Late","On Time")
I19=IF(H19>="12:00:00"+0,"Late","On Time")
I20=IF(H20>="12:00:00"+0,"Late","On Time")
I21=IF(H21>="12:00:00"+0,"Late","On Time")
I22=IF(H22>="12:00:00"+0,"Late","On Time")
I23=IF(H23>="12:00:00"+0,"Late","On Time")
I24=IF(H24>="12:00:00"+0,"Late","On Time")
I25=IF(H25>="12:00:00"+0,"Late","On Time")
I26=IF(H26>="12:00:00"+0,"Late","On Time")
I27=IF(H27>="12:00:00"+0,"Late","On Time")
I28=IF(H28>="12:00:00"+0,"Late","On Time")
I29=IF(H29>="12:00:00"+0,"Late","On Time")
I30=IF(H30>="12:00:00"+0,"Late","On Time")


Current formula attempts:
=IF(H4>="12:00:00"+0,"Late","On Time")

I didnt save the formula in column J. however, it dosent' completely work because we have orders that are entered in retroactive to delivery that it was taking into account. Orders that are data the day before do not count.

Thanks in advance.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
If you calculate the Expected/On Time delivery date based on the rules and then test all you have to do is compare the actual delivery date with the expected it might simplify your problem. I wasn't sure about a how things were entered on your sheet so I rearranged the columns for this but you should be able to follow it.
Code: 
=B2+(IF(WEEKDAY(B2)=6,3,1)+(--(A2>VLOOKUP(C2,Cutoff_Time!$A$2:$B$4,2,FALSE))*IF(WEEKDAY(B2)=5,3,1)))

'//============= BREAKDOWN OF ABOVE ===========================
	
=B2			'// Order Date
	+
	(IF(WEEKDAY(B2)=6,3,1)	
			'// Check if order date is a Friday if so return 3 (Fri+3=Mon) 
			'// Otherwise 1 for next day.
			'// No checks for weekends
	+(
	--(A2>VLOOKUP(C2,Cutoff_Time!$A$2:$B$4,2,FALSE))	
			'// If Order time is > Vlookup Time for 'Type' returns 1
			'// Otherwise returns 0 for next day
	*IF(WEEKDAY(B2)=5,3,1))	
			'// Check if Thursday if so multiple the above by 3 to get late in the day orders
			'//  to have a delivery day of Monday. Cancels out for non-late in the day orders.
		)
Where
Formula is entered in Column D
Column A is Order Time
Column B is Order Date
Column C is Service Type
Cutoff_Time!$A$2:$B$4 is a Table with the cutoff times
on a seperate sheet. With Column A = Types; and B = Cutoff Times

Test if 'On Time' or 'Late' with
Code: 
=IF(E2>D2,"Late","On Time")
Where
Formula is entered in Column F
Column D is Calculated/Expected Delivery Date
Column E is Actual Delivery Date

I am not sure how you handle 'Freight'. Let me know if you have any questions.
 
Upvote 0
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Time</td><td style=";">ENT SYS</td><td style=";">CUST</td><td style=";">Rule</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">12:39 PM</td><td style="text-align: right;;">3/30/2011</td><td style=";">Lubes</td><td style="text-align: right;border-right: 1px solid black;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">12:42 PM</td><td style="text-align: right;;">3/30/2011</td><td style=";">Lubes</td><td style="text-align: right;border-right: 1px solid black;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">8:19 AM</td><td style="text-align: right;;">3/31/2011</td><td style=";">Lubes</td><td style="text-align: right;border-right: 1px solid black;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">11:50 AM</td><td style="text-align: right;;">3/31/2011</td><td style=";">Lubes</td><td style="text-align: right;border-right: 1px solid black;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5:53 AM</td><td style="text-align: right;;">4/1/2011</td><td style=";">Fuel</td><td style="text-align: right;border-right: 1px solid black;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">5:54 AM</td><td style="text-align: right;;">4/1/2011</td><td style=";">Lubes</td><td style="text-align: right;border-right: 1px solid black;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">5:54 AM</td><td style="text-align: right;;">4/1/2011</td><td style=";">Lubes</td><td style="text-align: right;border-right: 1px solid black;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">5:55 AM</td><td style="text-align: right;;">4/1/2011</td><td style=";">Lubes</td><td style="text-align: right;border-right: 1px solid black;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">5:55 AM</td><td style="text-align: right;;">4/1/2011</td><td style=";">Lubes</td><td style="text-align: right;border-right: 1px solid black;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">5:55 AM</td><td style="text-align: right;;">4/1/2011</td><td style=";">Lubes</td><td style="text-align: right;border-right: 1px solid black;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">5:56 AM</td><td style="text-align: right;;">4/1/2011</td><td style=";">Lubes</td><td style="text-align: right;border-right: 1px solid black;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">5:56 AM</td><td style="text-align: right;;">4/1/2011</td><td style=";">Lubes</td><td style="text-align: right;border-right: 1px solid black;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">5:56 AM</td><td style="text-align: right;;">4/1/2011</td><td style=";">Lubes</td><td style="text-align: right;border-right: 1px solid black;;">#N/A</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">5737</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=B2+(<font color="Blue">IF(<font color="Red">WEEKDAY(<font color="Green">B2</font>)=6,3,1</font>)+(<font color="Red">--(<font color="Green">A2>VLOOKUP(<font color="Purple">G2,Cutoff_Time!A:B,2,FALSE</font>)</font>)*IF(<font color="Green">WEEKDAY(<font color="Purple">B2</font>)=5,3,1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=B3+(<font color="Blue">IF(<font color="Red">WEEKDAY(<font color="Green">B3</font>)=6,3,1</font>)+(<font color="Red">--(<font color="Green">A3>VLOOKUP(<font color="Purple">G3,Cutoff_Time!A:B,2,FALSE</font>)</font>)*IF(<font color="Green">WEEKDAY(<font color="Purple">B3</font>)=5,3,1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=B4+(<font color="Blue">IF(<font color="Red">WEEKDAY(<font color="Green">B4</font>)=6,3,1</font>)+(<font color="Red">--(<font color="Green">A4>VLOOKUP(<font color="Purple">G4,Cutoff_Time!A:B,2,FALSE</font>)</font>)*IF(<font color="Green">WEEKDAY(<font color="Purple">B4</font>)=5,3,1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=B5+(<font color="Blue">IF(<font color="Red">WEEKDAY(<font color="Green">B5</font>)=6,3,1</font>)+(<font color="Red">--(<font color="Green">A5>VLOOKUP(<font color="Purple">G5,Cutoff_Time!A:B,2,FALSE</font>)</font>)*IF(<font color="Green">WEEKDAY(<font color="Purple">B5</font>)=5,3,1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D6</th><td style="text-align:left">=B6+(<font color="Blue">IF(<font color="Red">WEEKDAY(<font color="Green">B6</font>)=6,3,1</font>)+(<font color="Red">--(<font color="Green">A6>VLOOKUP(<font color="Purple">G6,Cutoff_Time!A:B,2,FALSE</font>)</font>)*IF(<font color="Green">WEEKDAY(<font color="Purple">B6</font>)=5,3,1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D7</th><td style="text-align:left">=B7+(<font color="Blue">IF(<font color="Red">WEEKDAY(<font color="Green">B7</font>)=6,3,1</font>)+(<font color="Red">--(<font color="Green">A7>VLOOKUP(<font color="Purple">G7,Cutoff_Time!A:B,2,FALSE</font>)</font>)*IF(<font color="Green">WEEKDAY(<font color="Purple">B7</font>)=5,3,1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D8</th><td style="text-align:left">=B8+(<font color="Blue">IF(<font color="Red">WEEKDAY(<font color="Green">B8</font>)=6,3,1</font>)+(<font color="Red">--(<font color="Green">A8>VLOOKUP(<font color="Purple">G8,Cutoff_Time!A:B,2,FALSE</font>)</font>)*IF(<font color="Green">WEEKDAY(<font color="Purple">B8</font>)=5,3,1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D9</th><td style="text-align:left">=B9+(<font color="Blue">IF(<font color="Red">WEEKDAY(<font color="Green">B9</font>)=6,3,1</font>)+(<font color="Red">--(<font color="Green">A9>VLOOKUP(<font color="Purple">G9,Cutoff_Time!A:B,2,FALSE</font>)</font>)*IF(<font color="Green">WEEKDAY(<font color="Purple">B9</font>)=5,3,1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D10</th><td style="text-align:left">=B10+(<font color="Blue">IF(<font color="Red">WEEKDAY(<font color="Green">B10</font>)=6,3,1</font>)+(<font color="Red">--(<font color="Green">A10>VLOOKUP(<font color="Purple">G10,Cutoff_Time!A:B,2,FALSE</font>)</font>)*IF(<font color="Green">WEEKDAY(<font color="Purple">B10</font>)=5,3,1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D11</th><td style="text-align:left">=B11+(<font color="Blue">IF(<font color="Red">WEEKDAY(<font color="Green">B11</font>)=6,3,1</font>)+(<font color="Red">--(<font color="Green">A11>VLOOKUP(<font color="Purple">G11,Cutoff_Time!A:B,2,FALSE</font>)</font>)*IF(<font color="Green">WEEKDAY(<font color="Purple">B11</font>)=5,3,1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D12</th><td style="text-align:left">=B12+(<font color="Blue">IF(<font color="Red">WEEKDAY(<font color="Green">B12</font>)=6,3,1</font>)+(<font color="Red">--(<font color="Green">A12>VLOOKUP(<font color="Purple">G12,Cutoff_Time!A:B,2,FALSE</font>)</font>)*IF(<font color="Green">WEEKDAY(<font color="Purple">B12</font>)=5,3,1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D13</th><td style="text-align:left">=B13+(<font color="Blue">IF(<font color="Red">WEEKDAY(<font color="Green">B13</font>)=6,3,1</font>)+(<font color="Red">--(<font color="Green">A13>VLOOKUP(<font color="Purple">G13,Cutoff_Time!A:B,2,FALSE</font>)</font>)*IF(<font color="Green">WEEKDAY(<font color="Purple">B13</font>)=5,3,1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D14</th><td style="text-align:left">=B14+(<font color="Blue">IF(<font color="Red">WEEKDAY(<font color="Green">B14</font>)=6,3,1</font>)+(<font color="Red">--(<font color="Green">A14>VLOOKUP(<font color="Purple">G14,Cutoff_Time!A:B,2,FALSE</font>)</font>)*IF(<font color="Green">WEEKDAY(<font color="Purple">B14</font>)=5,3,1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />



I don't think it's working properly, I am sure it must be user error. I formatted the time and dates cause I thought that might be the cause, or did I break it?
 
Upvote 0
Ugh - NM I got it - I see I am a moron. I got it to work. Thank you very much.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Time</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ENT SYS</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">CUST</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Expected </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Actual </td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Test</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12:39 PM</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3/30/11 12:00 AM</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Lubes</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4/1/2011</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4/1/2011</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">On Time</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">5737</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=B2+(<font color="Blue">IF(<font color="Red">WEEKDAY(<font color="Green">B2</font>)=6,3,1</font>)+(<font color="Red">--(<font color="Green">A2>VLOOKUP(<font color="Purple">C2,Cutoff_Time!A:B,2,FALSE</font>)</font>)*IF(<font color="Green">WEEKDAY(<font color="Purple">B2</font>)=5,3,1</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=IF(<font color="Blue">E2>D2,"Late","On Time"</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
400
dshtesy
D
M
  • Question Question
Pivot table not showing as Numbers
Replies
1
Views
279
shift-del
shift-del
S
  • Question Question
Formula to identify the time & frequency of max value.
Replies
5
Views
283
Cubist
Cubist
C
  • Question Question
Ordinary Hours or Penalty Shift
Replies
0
Views
418
ChetanPuri
C
J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
342
JMWh1t3
J

Forum statistics

Threads
1,224,616
Messages
6,179,911
Members
452,949
Latest member
beartooth91

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