Counting the number of days overdue based on date and time

Hi,

Thanks that works fine. Any ideas what sort of situations might exist when it wouldn't work?

One more provision ive just realized id like to add:


B3 contains a date of an extension to the original due date in B1. If there is a date in B3, this should be used instead of B1....That make sense?

Thanks kindly

Any help with the above would be appreciated.

Fotis,

No problems - it's hard for me to get my head round. You nearly cracked it however. I added a 1 in the second part as below and it works. I have no idea how though

Please Login or Register  to view this content.

Many thanks

Just to clear this up for anyone stumbling across this thread and needing this sort of thing but wondering how it works:

You have:

1. Item with a DUE delivery date (18/06/14)

2. Item with a DUE delivery time (4:00pm) Note: Anything later than 4pm on the day of delivery or any subsequent date adds 1 day on to the day’s late total. In effect 4pm is being used as the end of the day instead of 12pm.

3. You also have an ACTUAL delivery date: (19/06/14)

4. An ACTUAL delivery time (4:01pm)

In the above example:

DAYS LATE = 2

1 day between the DUE and ACTUAL date, and another day because it is past 4pm.

This is then complicated by the fact an item may be granted an extension to its delivery date. This goes in a separate cell. When the item arrives the delivery date still gets entered, but to calculate if it is late or not, that has to be compared against the new extension date, not the original DUE date.

You can use the example Fotis attached to see how this works, but replace the formula in E18 with the following:

Please Login or Register  to view this content.

Hey, I am currently using this formula but I have a little problem, maybe it isn't a big problem or maybe I am missing something.

So I am referencing this formula for: lets say A1, A2 and A3 so the formula is on cells B1, B2 and B3. It works perfectly but when I delete the cell reference I got numbers "41809" in that cell and it will not go away. Please help.

Hi - for Beany Baby and anyone else

Sorry to bump this but it deserves an amendment as I was wrong about the above when i'd initially said it worked... I still dont have an answer.


Thanks

Cracked it

This is the correct answer as per Fotis's reply and download in #7.

=IF($I14>C6,INT(($F14+$G14)-($I14+$F6))+1,INT(($F14+$G14)-($C6+$F6))+1)

I think Chris's works too albeit with different cell references.