Need Help With Tricky Formulas

Nicole J

For my projects (one per "column"), I have a column for "Deadline", "Remaining Hours", and "Networking Days Between Today and Deadline". Then there are columns that need formulas that need to calculate "splitting all remaining hours" evenly across months up to the deadline (and a column for August 2024, September 2024, through "infinity").

For instance, if the "deadline" is 10/25/2024 and there are 362.25 remaining hours, that means there are 58 working days between today and the deadline. So, 6.24 hours would have to be worked across every working day until the deadline. That means the "August" formula would need to yield a total of 118.67 (6.24 hours/day times the 19 working days between today and the end of August") and the "September" formula would need to yield a total of 124.8 (6.24 hour/day times the 20 working days in September) and the "October" formula would need to yield a total of 118.67 (6.24 hours/day times the 19 working days between 10/01 & 10/25).

I hope this makes sense. If you think you might be able to help me out, please let me know. I'd even be able to share a "dummy sheet" with you to play around with.

Many thanks,

heyjay

This is for your September Formula. Replace 9 with the corresponding month.
You will also need to adjust each networkdays for the end month.

=IF(MONTH(TODAY()) = 9, 

NETWORKDAYS(TODAY(), DATE(2024, 9, 30), {Holiday}), 
NETWORKDAYS(DATE(2024, 9, 1), DATE(2024, 9, 30), {Holiday})) * 

[Remaining Hours]1 / [Networking Days Between Today and Deadline]1

The IF Statement is to cover if we are computing for the current month, then it will use TODAY() otherwise it will use the whole month for the networkdays.

Nicole J

heyjay,

I entered the below into my September column, but am getting a "#UNPARSEABLE" error.

=IF(MONTH(TODAY()) = 9, NETWORKDAYS(TODAY(), DATE(2024, 9, 30), {Holiday}), NETWORKDAYS(DATE(2024, 9, 1), DATE(2024, 9, 30), {Holiday})) * [Remaining Hours]1 / [Networking Days Between Today and Deadline]1

Nicole J

Okay, heyjay. I think I may be on to something.

I used this formula for the month of August (which has 22 working days):

=[Remaining Hours]@row / [Net Working Days Between "Today" and "Deadline"]@row * 22

And it seems to somewhat work. I'd have to do the same thing for each other month (September has 20 working days, October has 23 working days, etc.).

However, what this doesn't account for is for months beyond the deadline. So for a line with a deadline in October, I'd need November rows and beyond to display a "-". Is there a "IF" that can be devised for if the deadline month/year is before that month's/year's row then it will display a "-"?

Also, I'd need IF formulas for if the deadline is in the past OR if the remaining hours is negative OR if the deadline is blank, then a "-" is to be displayed.