+ Reply to Thread
Results 1 to 5 of 5

Deeper if then logic for existing fomula?

  1. #1
    Registered User
    Join Date
    07-11-2017
    Location
    chialo
    MS-Off Ver
    2016
    Posts
    6

    Deeper if then logic for existing fomula?

    I have the below formula to see how many days between dates for projects:

    =IF(I19="",NETWORKDAYS(H19,TODAY())-1,"")

    So kind of works, but doesn't work if the H column is blank as well. How would I get this logic.

    The "H" column is the start date
    The "I" column is the End date.
    Start date is blank if not started
    End date is blank if not completed.
    Start date is assumed to be today to show total duration of the project

    Looking to have a single formula for column "J" of duration that says:

    If Start Date is populated and Completion Date isn't, use like I have above.
    If Start Date is populated an Completion Date is populated diff them.
    If start date is blank and completion date is blank return blank or 0

    Right now the above formula if start and end are blank will return 30665.

    Thoughts?

    Thanks.

    JR

  2. #2
    Registered User
    Join Date
    07-11-2017
    Location
    chialo
    MS-Off Ver
    2016
    Posts
    6

    Re: Deeper if then logic for existing fomula?

    Been playing with it, below gets everything unless both start and completed exist.

    =IF(AND(I19="",H19=""),"",IF(I19="",NETWORKDAYS(H19,TODAY())-1,"")))

  3. #3
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Deeper if then logic for existing fomula?

    Try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If Start Date is blank, it returns blank.
    If Start Date isn't blank but End Date is, it gives the number of days between the Start Date and Today, as per your previous formula.
    If both Start and End Dates are filled in, it gives the difference.

    Hope that does what you need.

    Edit: added -1 to final subtraction to give exclusive not inclusive days
    Last edited by Aardigspook; 07-17-2017 at 01:39 PM. Reason: Small amendment to formula
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  4. #4
    Registered User
    Join Date
    07-11-2017
    Location
    chialo
    MS-Off Ver
    2016
    Posts
    6

    Re: Deeper if then logic for existing fomula?

    Maybe this?

    =IF(AND(I19="",H19=""),"",IF(I19="",NETWORKDAYS(H19,TODAY())-1,NETWORKDAYS(H19,I19)))

    Thanks Aardigspook, just saw your response.

  5. #5
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,405

    Re: Deeper if then logic for existing fomula?

    Glad to help.
    If that takes care of your original question, then please take a moment to mark the thread as Solved so others know there's an answer here (instructions are in my sig). Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Expand a simple report into 15 minute slots for deeper analysis
    By C J W in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-17-2017, 03:09 PM
  2. Calculation - Adding IF statement to existing logic
    By bdav1216 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-27-2016, 10:37 AM
  3. Replies: 11
    Last Post: 11-04-2013, 04:32 PM
  4. Replies: 3
    Last Post: 10-14-2013, 03:06 PM
  5. [SOLVED] VBA programming just gets beter and better the deeper you go but..minimize a userform
    By Pyro Form in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-08-2012, 06:10 AM
  6. if function deeper than 8 nested
    By shafattack in forum Excel General
    Replies: 12
    Last Post: 10-26-2009, 02:11 PM
  7. Replies: 1
    Last Post: 08-15-2009, 05:52 AM

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