Calculating Time Taken

[gjohnno]

I work for a large Logistic company who contracts most of their distribution out to another company.

This Contractor is paid on the level of delivery service required.

There are 3 levels of delivery service provided

Level 1 = 24 hours delivery

Level 2 = 3 days delivery

Level 3 = 14 days delivery

The Contractor has up till 23:59 on the 3rd or 14th Day respectively to deliver level 2 & 3 distribution

Example 1
Cell A2 Level 1
Cell B2 I have the Start Date & Time of 26 May 2008 09:47
Cell C2 I have the Finish Date & Time of 27 May 2008 07: 23

The formula I use is =IF(C2<=B2+1,"YES","NO" )

The answer would be 'Yes'

Example 2
Cell A5 Level 2
Cell B5 I have the Start Date & Time of 26 May 2008 09:47
Cell C5 I have the Finish Date & Time of 29 May 2008 07: 23

The formula I use is =IF(C5<=B5+3,"YES","NO" )

The answer would be 'Yes'

Example 3
Cell A8 Level 2
Cell B8 I have the Start Date & Time of 26 May 2008 09:47
Cell C8 I have the Finish Date & Time of 29 May 2008 12: 55

The formula I use is =IF(C8<=B8+3,"YES","NO" )

The answer would be 'No'

The reason why it is 'No" it is taken from the start time

Any ideas of a formula to include the extra time up till 23:59 on the 3rd or 14th day of delivery

Thank you in advance for your assistance

gjohnno

 

[cornflakegirl]

Welcome to the board.

What you want to do is add 3 to the date, then round up the time to the end of the day. Because days are integers in Excel, you can do exactly that:

=IF(C5 < ROUNDUP(B5+3,0),"YES","NO" )

Notice the < = is changed to a < because the round up takes it to the next day at 00:00:00 - so it needs to be less than that to comply.

 

[gjohnno]

Thank you for your response.