Formula for Days overdue

James Melendez

I am using smartsheets to keep track of assigned items to my team.  I want to insert a column that let's me know the days overdue.  I don't know how to insert a forumla that does this.  Can anyone help?

tobrien3

I think the formula you are looking for is:

 

=Due Date - Today()

 

Let me know if that helps!

Jenny Lam

The formula I want to suggest is: =NETDAYS([Start Date], TODAY())

 

This forumla simply calculates the number of days between two days,and it always produces a positive number.

 

In your sitation, I think it might be preferrable to count the number of working days (i.e. exclude weekend days and holidays according to your sheet's project setting). In this case, I suggest you try this formula: =NETWORKDAYS([Start Date], TODAY()).

 

Another date formula, NETWORKDAY (without "S") is the same as NETWORKDAYS, but adds 1 to the calculated value if "Start Date" is a non-working day.

 

There are various options on how to perform date-related calculations.  Please review the Date Formulas (http://help.smartsheet.com/customer/portal/articles/775363#date) and see which one fits your situation.

 

Moreover, if there's an "End Date" column, the calculation of days overdue may only necessary when "End Date" is not set. This situation adds complexity for it is necessary to add conditional check on "Is End Date cell blank?" in the formula.

 

Here is a a sample sheet I have come up with. Hope this helps: https://app.smartsheet.com/b/publish?EQBCT=c88a3f5da0ce41b3af7c3e9096269ce5

 

 

 

J. Craig Williams

James,

 

You may also want to wrap Jenny's great suggestions with a check on whether the task is overdue or done.

 

=IF([Completed?]23,"", NETWORKDAYS([Start Date], TODAY()))

 

This will look at the "Completed?" checkbox column, row 23, to see if the checkbox is checked. If it is, display nothing (the "") else do the formula from Jenny.

Depending on what you have for the status will determine the exact formula, but you should get the idea.

 

Craig

Rob van Dijk

Hi James Melendez, be aware that using a formula that gives a new result every new day, will effect 'Modified by' field.

 

The first person who opens the sheets in a new day, will automaltically 'change' all the rows/cells with that formula, according to the system field "Modified by' (same for Modified (date) .)

 

A glitch in Smartsheet..

aaronakzin

When I try the following formula I get #UNPARSEABLE.

 

=WORKDAY([SLA148], TODAY())

 

SLA is the column I'm trying to calculate off of and the formula is in a different column that has Date properties.

 

How do I correct this?

Kal-El

How do I use the Networkdays (M-F) if I have not completed the task but it is overdue? For example:

 

Planned Completion: 09/29/17

Actual Complete: Not completed yet

Various: Is 119, however that includes weekends. I would like just week days.

 

The formula I am using to get Weekdays only:

Planned Completion - NETWORKDAY()

I receive # INCORRECT Argument Set