NETWORKDAYS formula

Aimee Hatcher

I am trying to calculate days open between the request date and today and not count weekends however I want the count to start at 0, not 1. I am using the below formula but for everything with today as the request date, it is showing -2. Once it moves to tomorrow, the days open goes back to 1. How do I get rid of the -2?

=IF(ISBLANK([Complete Date]@row), NETWORKDAYS([Request Date]@row, TODAY() - 1), NETWORKDAYS([Request Date]@row, [Complete Date]@row))

KPH

Is this what you need:

• If today is the requested date, the result should be 0.
• If yesterday was the requested date you would like the result to be 1.
• If tomorrow is the requested date, you would like the result to be -1.

Is that correct? You might just need to move a parenthesis in your formula.

NETWORKDAYS([Request Date]@row, TODAY())

Means the working days between Requested Date and Today. When Requested Date is today, that is 1.

Your formula:

NETWORKDAYS([Request Date]@row, TODAY() - 1)

Means the working days between Requested Date and Yesterday (the part in bold means todays date minus one). When Requested Date is today, that is -2.

My suggestion:

NETWORKDAYS([Request Date]@row, TODAY()) - 1

Means the working days between Requested Date and Today (the part in bold), minus 1. The minus 1 is after the networkdays calculation so 1 is subtracted from the output, not from the date input.

When Requested Date is today, that is 1-1=0.

I hope that is what you need, and it makes sense.

KPH

Is this what you need:

• If today is the requested date, the result should be 0.
• If yesterday was the requested date you would like the result to be 1.
• If tomorrow is the requested date, you would like the result to be -1.

Is that correct? You might just need to move a parenthesis in your formula.

NETWORKDAYS([Request Date]@row, TODAY())

Means the working days between Requested Date and Today. When Requested Date is today, that is 1.

Your formula:

NETWORKDAYS([Request Date]@row, TODAY() - 1)

Means the working days between Requested Date and Yesterday (the part in bold means todays date minus one). When Requested Date is today, that is -2.

My suggestion:

NETWORKDAYS([Request Date]@row, TODAY()) - 1

Means the working days between Requested Date and Today (the part in bold), minus 1. The minus 1 is after the networkdays calculation so 1 is subtracted from the output, not from the date input.

When Requested Date is today, that is 1-1=0.

I hope that is what you need, and it makes sense.

Aimee Hatcher

That worked, thank you!

KPH

Excellent! Thanks for letting me know.