Calculate amount of days untill deadline and how many days is overdue

Marie_Louise22

Hi

Hoping you guys can help!

I'm trying to calculate the days between a desired deadline and today (so kind of a countdown) and in the same field then also count if the task is overdue and by how many days.

so, I was thinking, if it was counting down the days, it would be a positive number and when/if a task is overdue the number would be negative. so, its -3 days overdue... (or the other way around) (only networkdays)

I also need it to stop counting when a checkbox (Done) is checked. :)

I have been trying with a =IF formula but maybe it should be a count.

I can get them to work if it is only counting the days until due date and another counting days overdue date but can't seem to put them together.

Looking forward to hearing from you!

Best,

Marie

Paul Newcome

You would first need to insert a date type column. This can be hidden after setting it up to keep the sheet looking clean and will be called "Checked Date" in this example.

Then the formula to get the days would be:

=NETWORKDAYS([Start Date]@row, IF([Checked Date]@row <> "", [Checked Date]@row, TODAY()))

Marie_Louise22

Hi Paul

Thanks for the fast response!

Okay, what should be in the "checked day" column?

Paul Newcome

Sorry about that. I got ahead of myself. It is a Record A Date automation set to trigger when the box is checked.

Marie_Louise22

Okay, but i have that.

i just called it "Realized End-date"

this is recoreded automatically when the box is checked. :)

Paul Newcome

In that case you would use the same NETWORKDAYS formula as above but change the column names to whatever is applicable to your sheet.

Marie_Louise22

Okay, Thanks..

It works, when it counts the days after the realized end date has been put in.

but it doesn't count the days from today until the desired deadline..

Paul Newcome

Sorry. Looks like I need another cup of coffee.

=NETWORKDAYS(IF([Checked Date]@row <> "", [Start Date]@row, TODAY()), IF([Checked Date]@row <> "", [Checked Date]@row, [Desired Deadline]@row))

Marie_Louise22

It's great! :D

It works!

Thank you so much!

Paul Newcome

Happy to help. 👍️