Tip / Tricks - Simplify Date Management w "Days from Today" calculation.

Jeremy Myers

Looking at long column of dates is cognitively challenging, but everyone understands a range of numbers, negative to zero and then positive.

 

I originally used this scheme to simplify my conditional formatting setup but found that users really liked it so now I keep it visible.  It also makes reports much easier to understand and read, I now hide the calendar dates and just show the "Days from Today" values.  

 

The formula calculates "Days from Today" against the original deadline dates and displays Today as "0", yesterday as "-1" and tomorrow as "+1"  As things become increasingly overdue, the numbers become ever more negative and the farther out in the future they are, the higher the posivie numeric values.  Piece of cake.

 

In my industry, nothing happens on weekends so if the user inputs a weekend day for the <date due> value, it displays "Sat / Sun" but does not increment the calculated output. 

 

Hope you like it!  

 

 

=IF(ISBLANK(Deadline9), "", IF(WEEKDAY(Deadline9) = 1, "Sat / Sun", IF(WEEKDAY(Deadline9) = 7, "Sat / Sun", IF(ISDATE(Deadline9), IF(NETWORKDAYS(TODAY(), Deadline9) < 0, NETWORKDAYS(TODAY(), Deadline9) + 1, NETWORKDAYS(TODAY(), Deadline9) - 1)))))

Shaine Greenwood

This is a great method for quickly seeing the day of the week for reference. This can be adopted in many ways, for instance, to spell out the day of the week:

 

=IF(WEEKDAY(Deadline9) = 1, "Sunday", IF(WEEKDAY(Deadline9) = 2, "Monday", IF(...etc...)

 

Check out our function guide for a list of all of our functions: https://help.smartsheet.com/articles/775363-using-formulas