+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Forum Contributor
    Join Date
    04-28-2004
    Location
    Norwich, England
    MS-Off Ver
    2010
    Posts
    119

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

    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
    Last edited by raehippychick; 01-16-2009 at 05:02 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    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...
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    Perhaps something like
    Please Login or Register  to view this content.
    Regards

    Dav

  4. #4
    Forum Contributor
    Join Date
    04-28-2004
    Location
    Norwich, England
    MS-Off Ver
    2010
    Posts
    119
    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

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Maybe:

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

  6. #6
    Forum Contributor
    Join Date
    04-28-2004
    Location
    Norwich, England
    MS-Off Ver
    2010
    Posts
    119
    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

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    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

  8. #8
    Forum Contributor
    Join Date
    04-28-2004
    Location
    Norwich, England
    MS-Off Ver
    2010
    Posts
    119
    Thank you so much - all solved and sorted now

    All I have left to do today is replicate everything I did yesterday since it all got eaten by the IT oh joy!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1