Calculating the number of workdays between two dates, even if the dates are after business hours

Options

Hi,

I have a formula to calculate the business days between the date REQUESTED for a document, and the date ALL COMPLETE.  It works fine unless the document is requested after 5pm and/or completed after 5pm PST.  For instance, if the Request is 5:30 pm, and the Complete is 6:30pm same day, I’m getting -2, instead of getting 1 business day.

Any suggestion on how to adjust my formula so that the -2 is converted to “1”  ? 

=IFERROR(NETWORKDAYS(Requested@row, [All Complete]@row), " ")

The IFERROR is there because sometimes there are notes in the Complete cells (which is a date column), so I want those cells to be blank.

 I get the same -2 if I use “NETDAYS” or “NETWORKDAYS”

I’ve tried adding various AND formulas, but it kept getting an error.

Thank you for your assistance.


Answers

  • Krissia B.
    Krissia B. Moderator
    Options

    Hello @Misha A

     

    Upon reviewing your formula & testing this in my own sheet, I adjusted the formula you provided because it is giving me an #unparseable formula error message. After adjusting the formula, same day dates are giving me "1" instead of "-2". See screenshots of what I have done.

     


    I used NETDAYS & NETWORKDAYS

    =IFERROR(NETWORKDAYS(REQUESTED@row, [All Complete]@row), "")

    =IFERROR(NETDAYS(REQUESTED@row, [All Complete]@row), "")

     

    Visit the links below for more information.

    NETWORKDAYS Function

    NETDAYS Function

    IFERROR Function

    Formula Error Message

     

    If the above doesnt resolve the issue, please provide us additional screenshots of what you have setup. If you could convert that column into a cell formula and provide us the formula being used there. (Please block out any sensitive data)

     

    Cheers,

    Krissia

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!