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

Misha A

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.

Krissia B.

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