Excel formula help needed On time Delivery

**Molson_Canadian** · 01-16-2012, 07:31 PM

I have a dead line to meet so sorry for the rush

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.

The first cell to paste the formula in should be G2

Any help would be appreciated.
Excel Help.xlsx

**Special-K** · 01-16-2012, 08:29 PM

Try

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

By the way cell G8 is incorrect in your example spreadsheet. E8 should be -11:40 not 20mins difference.
C8 is 8PM and D8 is 8:20AM thats not 20 mins difference.
The result should be Early since 8:20Am is before 8PM and before the Ship time of 12:00 (as per rule 2)

**Molson_Canadian** · 01-16-2012, 09:02 PM

Hi Special your formula worked when excel changed the first part of it IF(F9<"","On Time and took out the >.

It did show the correct On Time, Late Early when there was no letter in the comment column.

If you remember #4 from above:

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 ones that have a letter in the comment column show when its late even though a letter in that column is suppose to override that.
If I can correct that part then the formula will work and I will be a happy guy.

Thanks again for your efforts.