Consulting

Results 1 to 7 of 7

Thread: Date Formula with IF and ISBLANK

  1. #1
    VBAX Regular
    Joined
    Apr 2017
    Location
    Atlanta
    Posts
    81
    Location

    Question Date Formula with IF and ISBLANK

    I can't get the below formula to calculate Q6 even if it is blank.

    =IF(K6="","",IF(ISBLANK(O6),TODAY()-K6,O6-K6-Q6))

    K6 - Date Opened
    O6 - Date Completed
    Q6 - Number of days the request was on hold.

    Thank you in advance.

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    Why do you want something different for ""?

    Review https://support.microsoft.com/en-us/...6-4c3c0a1e2188

    Do this test. Open a new workbook. Put these expressions in A1 and B1:
    =ISBLANK(C1)
    =C1=""

    Both should return True.
    Last edited by June7; 11-06-2023 at 07:19 PM.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    jnix612, why not write the formula this was


     =IF(K6="","",IF(ISBLANK(O6),Today()-K6,O6-K6-Q6))
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,244
    Location
    Is O6 blank due to the result of a formula or is it a blank cell that contains no formula?
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved
    Click here for a guide on how to upload a file with your post

    Excel 365, Version 2405, Build 17628.20102

  5. #5
    VBAX Regular
    Joined
    Apr 2017
    Location
    Atlanta
    Posts
    81
    Location
    I'm sorry I did not provide all the info. There will always be a date in K6 and O6. N6 is a project paused date.

    Q6 will be blank because the project was never placed on hold so the rest of the formula should continue to calculate.

    K6 - Date Opened
    O6 - Date Completed
    N6 - Date placed on hold
    Q6 - Number of days the request was on hold.

  6. #6
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,744
    Location
    As Georgiboy asked earlier, is Q6 actually empty, or does it contain a formula that returns "" if N6 is blank? If the latter, try:

    =IF(K6="","",IF(ISBLANK(O6),TODAY()-K6,O6-K6-N(Q6)))
    Be as you wish to seem

  7. #7
    VBAX Regular
    Joined
    Apr 2017
    Location
    Atlanta
    Posts
    81
    Location
    Aflatoon and Georgiboy - sorry about not paying attention to give the full details.

    Yes the formula you gave me works perfect. Q6 does have a formula in it.

    N6-K6 = Q6 (# days on hold)
    date placed on hold - date opened = # days on hold

    Using Q6 in my final formula to say how many total days was this issue open.

    O6-K6-Q6 = R6 (days issue open) - my formula would not calculate if Q6 was blank
    date completed - date opened - # days on hold = number of days this issue was open

    Thank you all again and once again I apologize for missing details. Moving too fast.

Posting Permissions

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