I have a formula that tallies Net Work Days between "today" and a deadline.

Nicole J

I have a formula that tallies Net Work Days between "today" and a deadline

=NETWORKDAYS(TODAY(), Deadline@row)

However, if this deadline is in the past, I'm hoping that the formula will display an "N/A" as the result of the formula.

Thoughts?

Nic Larsen

Try:

=IF(Deadline@row < TODAY(), "N/A", NETWORKDAYS(TODAY(), Deadline@row)

Nicole J

Perfect. Thank you, Nic.

While I've got you can you help me with a deeper formula issue?

I've got a column of data for "Deadline" and "Remaining Hours". Can you help me devise a formula that tallies how many hours would have to be worked evenly across each month between today and the deadline in order to accomplish the job?

For instance, if "Deadline" is 10/25/2024 and the "Remaining Hours" is 64, how many hours would have to be worked in August, and September, and October to spread the work across those three months? (I do have columns for each month, so I expect that the formula would be unique to the "August Column" and the "September Column", etc. Note: if the "Deadline" is in the past and/or the "Remaining Hours" is negative, I'd want the formula to spit out an "N/A".

Here's a picture of my sheet.

Nic Larsen

This is probably beyond my skill set but I am sure there are others on here who could come up with a more refined solution.

I added a column called Months and used this formula to determine number of months between deadline and today:

=MONTH(Deadline@row) - MONTH(TODAY())

Then added a separate formula for the month's columns, upping the month number as so. It looks to see if the deadline date is greater than the month and then does the math based of remaining hours. It is not elegant, but it works in the same year.

Aug: =IF(MONTH(Deadline@row) >= 8, [Remaining Hours]@row / Months@row)

Sept: =IF(MONTH(Deadline@row) >= 9, [Remaining Hours]@row / Months@row)

Where this is not going to work is when you cross from December to January 2025 and the year changes. It was giving me issues when trying to add the year to the function.

Maybe this will get you started.. or someone else can jump in. I'll play around with it some more as well.

Nicole J

Thank you, Nic. I'll have to admit that this is also beyond my skill set. And will have to rely on further help.

Your thinking and progress so far is appreciated and sound, but I still have the challenge of moving beyond 2024. Also, in your "months formula", it's not properly accounting for deadlines that are in the past (if in the past, formula could maybe spit out an "N/A") as well as for deadlines that are beyond 2024 (for instance where the deadline is 10/31/2025, the months formula is displaying a "3" but it should be a "15".

I'll have to hope that someone who has either experienced this problem before OR someone more knowledgable can chime in.

Thanks.