Capitalizing on Calculated Columns: The ‘Today’ Trick Unveiled

Known for its insightful capabilities, SharePoint has a renowned trick up its sleeve that enables users to employ [Today], within calculated columns.

However, it’s worth noting a few restrictions related to this trick:

  • Recreating the placeholder [Today] column becomes a necessity whenever you need to modify the calculated column(s).
  • The calculation in question remains ‘fixed.’ Rather than refreshing daily as expected, the value only sees an update when the list item undergoes an edit.

Consequently, “Today” here should essentially be perceived as the date on which the last modification of an item occurred.

Still, we must ask… isn’t there an available column that offers this information already? Why not stick to utilizing the [Modified] column? To further understand this, let’s evaluate these two values with a straightforward example, employing the tasks list from my previous write-up.

The formulas below showcase the calculation of the remaining days till the due date:

  • Utilizing the “Today” trick: =[Due Date]-[Today]
  • Utilizing the pre-existing [Modified] column: =[Due Date]-[Modified]

The observed result (taking today as October 16th):

Observe the minor discrepancy, this arises due to the [Today] column offering the present-day date set at 12 am, whereas [Modified] provides the current date along with the precise time. To mimic the functioning of the [Today] column, it’s merely required to round down the [Modified] date: =[Due Date]-ROUNDDOWN([Modified],0)

Following result:

Drawing on these insights, we can safely conclude: Instead of investing time understanding and implementing the “Today” trick in your calculated columns, simply utilize:

  • The [Modified] column to fetch the date along with the time
  • Or, ROUNDDOWN([Modified],0) which imitates the behaviours seen in the “Today” trick

Stay tuned for a future post where we’ll unravel a method to fetch today’s date, day after day!


Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *