I have a formula that tallies Net Work Days between "today" and a deadline.
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?
Answers
-
Try:
=IF(Deadline@row < TODAY(), "N/A", NETWORKDAYS(TODAY(), Deadline@row)
-
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.
-
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.
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!