+ Reply to Thread
Results 1 to 12 of 12

Date formulas - PLEASE HELP!

  1. #1
    Registered User
    Join Date
    04-25-2013
    Location
    Durham
    MS-Off Ver
    Excel 2010
    Posts
    5

    Date formulas - PLEASE HELP!

    Hi! I am hoping someone may be able to offer some advice on a date issue that has me tearing my hair out!
    I have an excel spread sheet where:

    A = date complaint was received
    B = date complaint was responded to
    C = the number of days to response,
    Another sheet logs bank holidays.

    I have used =NETWORKDAYS(A1,B1,anothersheet1:2) to return the number of working days for response and then conditional formatting shows if this is over the 10 working day response target.

    All works really well until B is blank. If B is blank I need to determine if a response was due at the end of my quarter 01/04/2012 so:
    IF B is blank and A1 to 01/04/2012 is less than 10 network days excluding bank holidays then excel returns “Not Due”
    If B is blank and A1 to 01/04/2012 is more than 10 network days excluding bank holidays then excel returns “overdue”
    I just seem to be going around in circles – any help would be greatly appreciated!
    Thanks, Katie

  2. #2
    Valued Forum Contributor jwright650's Avatar
    Join Date
    12-10-2010
    Location
    Va, USA
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    606

    Re: Date formulas - PLEASE HELP!

    Can you post a sample workbook so whomever is helping will have something to tinker with for you?
    Life is like a roll of toilet paper. The closer it gets to the end, the faster it goes.
    John Wright

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.67 - (22111300)
    Posts
    7,506

    Re: Date formulas - PLEASE HELP!

    you could add a IF in front
    IF( B1 <>"", NETWORKDAYS(A1,B1,anothersheet1:2), IF( NETWORKDAYS(A1,"01/04/2012",anothersheet1:2) <=10 "Not Due", "overdue" ))
    Last edited by etaf; 04-25-2013 at 09:07 AM.

  4. #4
    Registered User
    Join Date
    04-25-2013
    Location
    Durham
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Date formulas - PLEASE HELP!

    That worked a treat etaf - thank you so so much youre a life saver! Its easy when you know how katie

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.67 - (22111300)
    Posts
    7,506

    Re: Date formulas - PLEASE HELP!

    your welcome

  6. #6
    Valued Forum Contributor jwright650's Avatar
    Join Date
    12-10-2010
    Location
    Va, USA
    MS-Off Ver
    Excel 2003, Excel 2010
    Posts
    606

    Re: Date formulas - PLEASE HELP!

    Hi Katie,
    Don't forget to mark your thread as solved and click the * under etaf's post and give etaf some credit for helping

  7. #7
    Registered User
    Join Date
    04-25-2013
    Location
    Durham
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Date formulas - PLEASE HELP!

    Hi Guys,

    Sorry to re bother, just wondering if you know of a way to work out a solution where i have 3 dates to consider in my formula?

    I have been using the IF( NETWORKDAYS(A1,"01/04/2012",anothersheet1:2) <=10 "Not Due formula which works so well, but have now been asked to incorporate a holiding response which would mean:

    IF(networkdays(A1,holding1,anothersheet1:2)<=10 AND holding1,01/04/2013,anothersheet1:2 <=10 then it is not due,

    i swear this issue is going to be the end of me!

    Any help greatly appreciated!

    Thanks again,
    Katie

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.67 - (22111300)
    Posts
    7,506

    Re: Date formulas - PLEASE HELP!

    you can do what you said

    IF(networkdays(A1,holding1,anothersheet1:2)<=10 AND holding1,01/04/2013,anothersheet1:2 <=10 then it is not due,
    IF ( AND ( networkdays(A1,holding1,anothersheet1:2)<=10 , networkdays( holding1,01/04/2013,anothersheet1:2) <=10 )

    not sure what holding1 is

    BUT if you want to do an AND
    its

    AND( test_for_true, test_for_true , etc )

  9. #9
    Registered User
    Join Date
    04-25-2013
    Location
    Durham
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Date formulas - PLEASE HELP!

    where holding 1 is a cell with another date in it? call the cell D1? it wont let me use 3 dates in a Networkdays argument, i ideally need:

    IF(AND(NETWORKDAYS(A1,D1,ANOTHERSHEET1:2)<=10,NETWORDAYS(D1,"01/04/2013",ANOTHERSHEET1:2)<=10),"NOT DUE") ??

    The issue is the and i need the forumula to take into account the date the complait was recieved to a holding response was less than 10 days, and the holding response to 01/04/13 was less than 10 days then a response isnt due - its a bloomin nightmare!

  10. #10
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.67 - (22111300)
    Posts
    7,506

    Re: Date formulas - PLEASE HELP!

    the AND should work
    as
    =NETWORKDAYS(A1,D1,ANOTHERSHEET1:2)<=10

    should be true
    AND
    NETWORDAYS(D1,"01/04/2013",ANOTHERSHEET1:2)<=10
    should be true

    to get the IF to return
    "NOT DUE"

    if either one is false it will return

    so they must both be true

    have you tried just putting the 2 formulas into separate cells to see what you get true or false as a test

    =NETWORKDAYS(A1,D1,ANOTHERSHEET1:2)<=10
    =NETWORDAYS(D1,"01/04/2013",ANOTHERSHEET1:2)<=10

    in case the logic of those two formulas are not producing what you actually think they should

  11. #11
    Registered User
    Join Date
    04-25-2013
    Location
    Durham
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Date formulas - PLEASE HELP!

    If i use the following i get a True result:

    IF(NETWORKDAYS(A1,D1,ANOTHERSHEET!1:2)<=10,NETWORKDAYS(D1,"01/04/2013",ANOTHERSHEET!1:2)<=10,"NOT DUE")

    what i cant get is the argument to display "not due" or to be able to add an otherwise "overdue" , it just keeps saying i have too many arguments which is why i feel like i need an IF(AND(Networkdays( it just isnt a thing! Sorry to be annoying with this one - i always though i was quite good with excel!

  12. #12
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.67 - (22111300)
    Posts
    7,506

    Re: Date formulas - PLEASE HELP!

    you are evaluting just this part of the IF
    NETWORKDAYS(A1,D1,ANOTHERSHEET!1:2)<=10
    then you have a comma
    so if that first test bit is TRUE
    it will then evaluate the true condition which is now
    NETWORKDAYS(D1,"01/04/2013",ANOTHERSHEET!1:2)<=10
    because of the comma - thats the syntax of the IF function
    so thats the bit that you are seeing as true or false
    if that was false you would get
    "NOT DUE"
    as thats how the IF has been set out by you
    IF(test , do if test is True , do if test is False)

    you do need the AND

    NETWORKDAYS(A1,D1,ANOTHERSHEET!1:2)<=10
    AND
    NETWORKDAYS(D1,"01/04/2013",ANOTHERSHEET!1:2)<=10

    becomes

    AND (NETWORKDAYS(A1,D1,ANOTHERSHEET!1:2)<=10 , NETWORKDAYS(D1,"01/04/2013",ANOTHERSHEET!1:2)<=10)

    then the IF
    IF( test, true, false)

    IF( AND (NETWORKDAYS(A1,D1,ANOTHERSHEET!1:2)<=10 , NETWORKDAYS(D1,"01/04/2013",ANOTHERSHEET!1:2)<=10) , "NOT DUE" , "OVERDUE")

    if both networkdays are true then you will get not due
    if either one of the networkdays is false you get overdue
    Last edited by etaf; 04-30-2013 at 11:53 AM.

+ 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