• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

plus or minus hour

T

thesilkster

Member
hi i need to know if one of my drivers have arrived on a delivery but it must be within 1 hour of time before or after ? is this 2 if statment or a greater or less than
 
Hi ,

Since this is an Excel forum , please specify your problem with reference to worksheet cells :

1. Which cell has the driver's arrival time ?

2. Which cell has the target arrival time ?

3. Which cell has to have the formula ?

4. What should be the output format , should it be text such as "In Time" , "Delayed" , "Early" , or should it be the time difference between the target and the actual ?

5. Is this only for one cell or for several cells in a column or row ?

Narayan
 
Hi ,

I would have thought uploading the file itself would be as easy , and probably more useful.

Anyway , I will also not bother to test my formula , I will just put it down , and leave it to you to test it and comment.

For criteria , try this :

=IF(B4-A4>TIMEVALUE("01:00:00"),"Too Early",IF(A4-B4>TIMEVALUE("01:00:00"),"Too late","On Time"))

For demurrage time , you have mentioned :
total time 90 minutes after booking time as long as the driver is on time
Click to expand...

What happens if the driver is not on time ? What is to be done ?

Narayan
 
thanks that worked great ,if the driver is late or to early then demurrage is discounted.... if "on time" then demurrage starts 90 mins after booking time .
i have added the spreadsheet ..sorry i thought it would have been rude to post it. yet again i am grateful and thank you for your time
 

Attachments

  • demurrage 1.xlsx
    11.1 KB · Views: 3
Hi ,

I will go by what you have posted here viz. if the driver is On Time , only then is there any demurrage ; if he is either Too Early or Too Late , then there is no demurrage ; is that correct ?

See if this is OK.

Narayan
 

Attachments

  • demurrage 1.xlsx
    10.9 KB · Views: 4
thanks thats great just what i was after

i am trying to do this with an access database as well as its much better for reports

i have managed most of it i am struggling with your last formular in access the expresion builder will not accept it
 

Attachments

  • screendump.png
    screendump.png
    28.8 KB · Views: 9
ahh i have a small problem if i arrive before the hour i can still claim demurrage i just have to wait till the booking time before demurrage starts so the bit about 1 hour befor is not true only the one hour after
 
You must log in or register to reply here.
Back
Top