Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

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

Jeremy Myers
Jeremy Myers ✭✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

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!  Cool



=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)))))


This discussion has been closed.