Excel formula help needed On time Delivery

[Zilla]

I need help with the following spreadsheet.

It is to report On Time Delivery. I have set up as much as I can but to be honest I need help on the formula in column " G ".
The report is based on deliverys on the same day mostly within a 12 hour period.

This is what I am hoping to achieve/

1) If the time in column D is prior to the ETA but not prior to the ship time show in cell G2 as " On Time "

2) If the time in column D is prior to the ETA and prior to the ship time show in cell G2 as " Early "

3) If the time in column D is after the ETA show in cell G2 as " Late "

4) Regardless of what time is entered in column D, if a letter ( A, B, C etc. ) shows in column F show in cell G2 as " On Time "

The letters are used when a delivery will technically be late due but due to a reason such as customer NSF.


Any help would be appreciated.

Excel Workbook
	A	B	C	D	E	F	G
1	WEEKLY SCHEDULE	SHIP TIME	ETA	ACTUAL	Diff	Comment	On Time / Late
2	Store 1	12:00	2:00 PM	1:25 PM	-0:35		On Time
3	Store 2	12:00	3:00 PM	2:25 PM	-0:35		On Time
4	Store 3	12:00	4:00 PM	11:25 AM	-4:35	A	On Time
5	Store 4	12:00	5:00 PM	5:20 PM	0:20		Late
6	Store 5	12:00	6:00 PM	6:20 PM	0:20	B	On Time
7	Store 6	12:00	7:00 PM	7:20 PM	0:20	B	On Time
8	Store 7	12:00	8:00 PM	11:00 AM	-9:00		Early
9	Store 8	12:00	9:00 PM	11:00 AM	-10:00	C	On Time
10	Store 9	12:00	10:00 PM	10:30 AM	-11:30		Early
11	Store 10		8:00 AM	7:00 AM	-1:00		On Time
12	Store 11		9:00 AM	8:00 AM	-1:00		On Time
13	Store 12		10:00 AM	9:00 AM	-1:00		On Time
14	Store 13		11:00 AM	10:00 AM	-1:00		On Time
15	Store 14		12:00 PM	12:00 PM	0:00		On Time
16	Store 15		1:00 PM	2:00 PM	1:00		Late
17	Store 16		2:00 PM	3:00 PM	1:00		Late
18	Store 17		3:00 PM	4:00 PM	1:00		Late
19	Store 18		4:00 PM	5:00 PM	1:00		Late
20	Store 19		5:00 PM	11:00 AM	-6:00		On Time
21	Store 20		6:00 PM	12:00 PM	-6:00		On Time
22	Store 21		7:00 PM	1:00 PM	-6:00		On Time
23	Store 22		8:00 PM	2:00 PM	-6:00		On Time
Weekly

 

[Robert Mika]

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;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><th>G</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">WEEKLY SCHEDULE</td><td style=";">SHIP TIME</td><td style=";">ETA</td><td style=";">ACTUAL</td><td style=";">Diff</td><td style=";">Comment</td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">Store 1</td><td style="text-align: right;;">12:00</td><td style="text-align: right;;">2:00 PM</td><td style="text-align: right;;">1:25 PM</td><td style=";">-0:35</td><td style="text-align: right;;"></td><td style=";">On time</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Store 2</td><td style="text-align: right;;">12:00</td><td style="text-align: right;;">3:00 PM</td><td style="text-align: right;;">2:25 PM</td><td style=";">-0:35</td><td style="text-align: right;;"></td><td style=";">On time</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">Store 3</td><td style="text-align: right;;">12:00</td><td style="text-align: right;;">4:00 PM</td><td style="text-align: right;;">11:25 AM</td><td style="text-align: right;;">0</td><td style=";">A</td><td style=";">On time</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">Store 4</td><td style="text-align: right;;">12:00</td><td style="text-align: right;;">5:00 PM</td><td style="text-align: right;;">5:20 PM</td><td style="text-align: right;;">0:20</td><td style="text-align: right;;"></td><td style=";">Late</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">Store 5</td><td style="text-align: right;;">12:00</td><td style="text-align: right;;">6:00 PM</td><td style="text-align: right;;">6:20 PM</td><td style="text-align: right;;">0:20</td><td style=";">B</td><td style=";">On time</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">Store 6</td><td style="text-align: right;;">12:00</td><td style="text-align: right;;">7:00 PM</td><td style="text-align: right;;">7:20 PM</td><td style="text-align: right;;">0:20</td><td style=";">B</td><td style=";">On time</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style=";">Store 7</td><td style="text-align: right;;">12:00</td><td style="text-align: right;;">8:00 PM</td><td style="text-align: right;;">11:00 AM</td><td style=";">-9:00</td><td style="text-align: right;;"></td><td style=";">Early</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style=";">Store 8</td><td style="text-align: right;;">12:00</td><td style="text-align: right;;">9:00 PM</td><td style="text-align: right;;">11:00 AM</td><td style=";">-10:00</td><td style=";">C</td><td style=";">On time</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style=";">Store 9</td><td style="text-align: right;;">12:00</td><td style="text-align: right;;">10:00 PM</td><td style="text-align: right;;">10:30 AM</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td><td style=";">Early</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style=";">Store 10</td><td style="text-align: right;;"></td><td style="text-align: right;;">8:00 AM</td><td style="text-align: right;;">7:00 AM</td><td style=";">-1:00</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style=";">Store 11</td><td style="text-align: right;;"></td><td style="text-align: right;;">9:00 AM</td><td style="text-align: right;;">8:00 AM</td><td style=";">-1:00</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style=";">Store 12</td><td style="text-align: right;;"></td><td style="text-align: right;;">10:00 AM</td><td style="text-align: right;;">9:00 AM</td><td style=";">-1:00</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style=";">Store 13</td><td style="text-align: right;;"></td><td style="text-align: right;;">11:00 AM</td><td style="text-align: right;;">10:00 AM</td><td style=";">-1:00</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style=";">Store 14</td><td style="text-align: right;;"></td><td style="text-align: right;;">12:00 PM</td><td style="text-align: right;;">12:00 PM</td><td style="text-align: right;;">0:00</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">16</td><td style=";">Store 15</td><td style="text-align: right;;"></td><td style="text-align: right;;">1:00 PM</td><td style="text-align: right;;">2:00 PM</td><td style="text-align: right;;">1:00</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style=";">Store 16</td><td style="text-align: right;;"></td><td style="text-align: right;;">2:00 PM</td><td style="text-align: right;;">3:00 PM</td><td style="text-align: right;;">1:00</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style=";">Store 17</td><td style="text-align: right;;"></td><td style="text-align: right;;">3:00 PM</td><td style="text-align: right;;">4:00 PM</td><td style="text-align: right;;">1:00</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">19</td><td style=";">Store 18</td><td style="text-align: right;;"></td><td style="text-align: right;;">4:00 PM</td><td style="text-align: right;;">5:00 PM</td><td style="text-align: right;;">1:00</td><td style=";"> </td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">20</td><td style=";">Store 19</td><td style="text-align: right;;"></td><td style="text-align: right;;">5:00 PM</td><td style="text-align: right;;">11:00 AM</td><td style=";">-6:00</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">21</td><td style=";">Store 20</td><td style="text-align: right;;"></td><td style="text-align: right;;">6:00 PM</td><td style="text-align: right;;">12:00 PM</td><td style=";">-6:00</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">22</td><td style=";">Store 21</td><td style="text-align: right;;"></td><td style="text-align: right;;">7:00 PM</td><td style="text-align: right;;">1:00 PM</td><td style=";">-6:00</td><td style="text-align: right;;"></td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">23</td><td style=";">Store 22</td><td style="text-align: right;;"></td><td style="text-align: right;;">8:00 PM</td><td style="text-align: right;;">2:00 PM</td><td style=";">-6:00</td><td style="text-align: right;;"></td><td style=";"></td></tr></tbody></table>

Sheet5​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" width="85%" cellpadding="2.5px"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" width="100%" cellpadding="2.5px"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">E4</th><td style="text-align:left">=-3:34</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">E10</th><td style="text-align:left">=-10:29</td></tr><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">G2</th><td style="text-align:left">=IF(B2="","",IF(F2<>"","On time",IF(AND(D2>B2,D2<=C2),"On time",IF(AND(D2<C2,D2<B2),"Early",IF(D2>C2,"Late")))))</td></tr></tbody></table></td></tr></tbody></table>
There is no data in B11 down so this formula return empty cell.
Copy furmula down

 

[guerillaunit]

Try this

=IF(ISBLANK(F4) = FALSE, "On Time", IF(AND(D4<C4,D4>B4), "On Time", IF(AND(D4<C4, D4<B4),"Early", IF(D4>C4, "Late"))))

 

[guerillaunit]

=IF(ISBLANK(F4) = FALSE, "On Time", IF(AND(D4<C4,D4>B4), "On Time", IF(AND(D4<C4, D4<B4),"Early", IF(D4>C4, "Late"))))<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Zilla]

Hi All
First thanks for the replys. I tried Robert Mika's formula and it worked except for G5 shows as Early where in truth it is 20 minutes late. It seems to show all lates as earlys.

I also tried guerillaunit's formula and it gave me an error. I pasted the formula into cell G4 since it references B4 C4 and D4. And still an error pops up.

Any help on how I can correct the two issues?

Thanks

 

[Robert Mika]

Hi Zilla,
Sometimes the formating does not come uo correctly try this:=IF(B2="","",IF(F2<>"","On time",IF(AND(D2>B2,D2<=C2),"On time",IF(AND(D2 < B2),"Early",IF(D2>C2,"Late")))))

The missing bit is in red just delete the sapce after D2

 

[guerillaunit]

Hi Zilla,
Check your private messages. The thread kept deleting a part of the code I was posting. I think Robert might have had the same problem

 

[zilla37]

Hi Robert it worked.
Thanks again for all the help

 

[zilla37]

One last question.
If there is no time entered in the "actual time " field EARLY appears.
Is there a way of adding to the formula so that if the "actual time " field is blank then nothing appears in the " On time / Late " column.

Thanks

 

[Robert Mika]

=IF(D2="","",IF(B2="","",IF(F2<>"","On time",IF(AND(D2>B2,D2<=C2),"On time",IF(AND(D2 < B2),"Early",IF(D2>C2,"Late"))))))