Tricky Formula - Need Help

Nicole J

I need help in figuring out a more complex formula. Below is a screenshot of my sheet.

What I'm hoping to devise is a formula to go into the yellow highlighted cells (a unique formula for each column, I expect) that essentially tells me:

Based on "Deadline@row" and "Remaining Hours@row", how many hours will have to be worked in each month between "today" and the "Deadline" to accomplish the "Remaining Hours" if the work is evenly split between "today" and the "Deadline".

Also, if a Deadline is in the past I'd like a "-" displayed in that formula's results. Also, if a Deadline is in say, October 2024, all monthly columns beyond October 2024 would also display a "-". And lastly, if the Remaining Hours is negative, then that formula's result should also be displayed as a "-".

I hope this makes sense and someone can help.

heyjay

Checks if deadline is in the past, =IF(TODAY() > Deadline@row, "-",

Checks if deadline is in October, MONTH(Deadline@row) = 10), "-",

Checks if sum is negative, IF([Remaining Hours]@row / (MONTH(Deadline@row) - MONTH(TODAY())) < 0, "-",

=IF(OR(
TODAY() > Deadline@row, 
MONTH(Deadline@row) = 10), "-", 

IF(
[Remaining Hours]@row / (MONTH(Deadline@row) - MONTH(TODAY())) < 0, "-", 
[Remaining Hours]@row / (MONTH(Deadline@row) - MONTH(TODAY()))))

Nicole J

So, is there one formula needed to be applied to each individual month? Like one for "August", one for "September", etc.? What about different years? My sheet will need data for "August of 2024", "September of 2024", etc.

Thank, you, heyjay.

heyjay

For months, you will replace 10 with the equivalent month in number, 1 = Jan, 2 = Feb, …

For years, you will have to add AND(YEAR(deadline@row) = 2024, MONTH(Deadline@row) = 10)

Nicole J

Got it. So, can you please tell me just the formula that I'd need to enter into the August 2024 column? I can then switch the months and years in the formula for different months and years.

Many thanks.

heyjay

=IF(OR(
TODAY() > Deadline@row,

AND(
MONTH(Deadline@row) = 8,
YEAR(Deadline@row) = 2024)), "-",

IF(
[Remaining Hours]@row / (MONTH(Deadline@row) - MONTH(TODAY())) < 0, "-",
[Remaining Hours]@row / (MONTH(Deadline@row) - MONTH(TODAY()))))

Nicole J

Thanks, heyjay.

When I enter the formula of:

=IF(OR(TODAY() > Deadline@row, AND(MONTH(Deadline@row) = 8, YEAR(Deadline@row) = 2024)), "-", IF([Remaining Hours]@row / (MONTH(Deadline@row) - MONTH(TODAY())) < 0, "-", [Remaining Hours]@row / (MONTH(Deadline@row) - MONTH(TODAY()))))

… it works for August 2024. But when I change the formula to have a "9" instead of the "8" for the September column, it gives me an "#INVALID OPERATION". Where it should be also giving me something close to 144.81 hours.

What's the trick to having the formula be tweaked for other months/years?

I'm also getting an "#DIVIDE BY ZERO" error on a line that has a deadline of 07/31/2025 and 486 remaining hours. Not sure where the "#DIVIDE BY ZERO" error is coming from.

heyjay

Can you paste the formula that gave you an error here?

Nicole J

This is the formula I entered into the September column (I just switched the "8" for a "9".

=IF(OR(TODAY() > [Hours Allocated]@row, AND(MONTH([Hours Allocated]@row) = 9, YEAR([Hours Allocated]@row) = 2024)), "-", IF([Net Working Days Between "Today" and "Deadline"]@row / (MONTH([Hours Allocated]@row) - MONTH(TODAY())) < 0, "-", [Net Working Days Between "Today" and "Deadline"]@row / (MONTH([Hours Allocated]@row) - MONTH(TODAY()))))

Also, for deadlines that are beyond 2025, the formula's not right. For example, Deadline of 10/31/2025 and Remaining hours of 1,905 gives me a result of 635 hours. But if I evenly had 635 hours worked each month up to the deadline (15 months), that would equal 9,525 hours.

I'm also getting an "#DIVIDE BY ZERO" error on a line that has a deadline of 07/31/2025 and 486 remaining hours. Not sure where the "#DIVIDE BY ZERO" error is coming from.

heyjay

This one does not seem correct, TODAY() > [Hours Allocated]@row, you are comparing a Date vs Number.

It seems that you copy and paste the cell, it moves all column reference by one to the right.

Copy this to your months column then change the number.

=IF(OR(TODAY() > Deadline@row,AND(MONTH(Deadline@row) = 8,YEAR(Deadline@row) = 2024)), "-",IF([Remaining Hours]@row / (MONTH(Deadline@row) - MONTH(TODAY())) < 0, "-",[Remaining Hours]@row / (MONTH(Deadline@row) - MONTH(TODAY()))))

Nicole J

Nope. A few things:

• New August 2024 formula
  • Presents "-" on a line item with a Deadline of 05/30/2025, even though it has 2,171 hours remaining
  • Presents "635.06" on a line item with a Deadline of 10/31/2025 and 1,905 hours remaining (that would mean 635.06 hours evenly spread across fifteen months and make the remaining hours 9,525)
  • Presents "#DIVIDE BY ZERO" on a line item with a Deadline of 07/31/2025 and 486 hours remaining.
• New "future months" formula seems to be working except for the above notes. However, on my first line item (Deadline of 10/25/2024 and Remaining Hours of 434.81, it gives me a "-" in October (although that should have the same number/answer as August and September).

Would this be easier to share my sheet with you and give you editing permissions to play around?

heyjay

Yes, that would be OK too. Please share a copy or dummy version of your sheet here.

Nicole J

Not quite sure how to share it with you. I can give you a link to my sheet, but I'll need an email address to grant you access; I think.

Can you advise on how to get you a dummy version of my sheet?

heyjay

Save as New > Share > Get Link. Please post link here so that others may also help. You'll get a notif once I request for access.

Nicole J

https://app.smartsheet.com/sheets/qP4572v9W3PVHhJ6MCJm75V3HGVGh7hQJ3G5qG51

Nicole J

Let me know once you're in. I've highlighted in ORANGE cells where the formula doesn't seem to be working correctly.