Count Working Days between 2 dates, but return a zero if the 2 dates are the same day

**raehippychick** · 01-15-2009, 10:48 AM

Hi All

Today I am having a very annoying problem that really has me stumped – I need to work out the lag between a Due Date and Delivered Date

But as people sometimes manage to deliver on the Due Date it needs to show a zero (as in they got it in on time) but using the formula below the result is a 1 and I want a zero

Can anyone help me please? I have tried putting assorted -1s in to the formula and it looks like it might work until I copy down and find that if a person delivered one day early the result shows -3 for example!

Please Login or Register  to view this content.

Many thanks in advance

Rae

**NBVC** · 01-15-2009, 11:04 AM

until I copy down and find that if a person delivered one day early the result shows -3 for example!

can't see how... did you check the formula and dates there to make sure...

**Dav** · 01-15-2009, 11:18 AM

Perhaps something like

Please Login or Register  to view this content.

Regards

Dav

**raehippychick** · 01-15-2009, 11:20 AM

When the due date is 10 June 09 and I have a delivered date of 9th June 09 it shows (with the above formula) a -2, but that day is only 1 day earlier, so I wanted a -1

[The -3 I got was when I was fiddling about putting a -1 within the forumula in various places and was just an example of it going wrong adding a -1 into the formula]

All I really want is to show how many days early or late something has been delivered so that if it is delivered when it is due the cell shows a zero, one day before and it shows a -1, and one day late it will show 1

Then I can conditionally format column E so the cells go red if they are greater than one, thus flagging that someone was late delivering. However if the person delivered on the due date and it goes red they won't be happy with me!!

Rae

**NBVC** · 01-15-2009, 11:32 AM

Maybe:

=If(D2="","",(ABS(NETWORKDAYS(B2,D2,holidays))-1)*IF(B2<D2,1,-1))

**raehippychick** · 01-15-2009, 11:33 AM

Dav

That certainly seem to work (and I have managed to include my HOLIDAYS range in it) except it returns a minus figure for a late delivery and positive for an early one

It's not a problem though as I can just adjust the conditional formattiing so a minus figure means a late delivery until I have the time to figure out how to change it to the other way round as I have just found out that the powers that be now want a horribly huge macro delivered in half the time orginally planned

so the pressure is on me now!!

Many thanks

Rae

**NBVC** · 01-15-2009, 12:52 PM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save