Date calculations using “Today” in SharePoint lists for years of service, days without incident, etc. (includes using blank date values)

calculations

This is a fairly simple solution that takes a date column, compares it to another date and gives you an answer in years (or days, or whatever you want). You’ll need to already have date columns to work with, and if comparing the date to today (years of age, membership, service, etc.) you’ll need a today column (hidden from the view above). This previous post will help you rig a “today” column that is always accurate without needing to update list items manually. Of course all of the following solutions work for any two dates (i.e. day span of vacation request), I’m just sharing specific examples that would involve “Today”.

The following solutions are to be used in a calculated column, set to display as a single line of text. I will give you the formulas, progressively more complex to show the individual components, though you’ll need to change the column names to match your own.

Tip: You may wish to set the result to show as a number for workflow purposes (greater than, less than, etc.) which will mean the “concatenate” step below won’t work. But you could always create a second calculated column to take that calculated column and concatenate it with your text for your list view. Then just use the hidden number column for your workflow.

Years of Service / Years of Membership / Years of Age / Etc.

=[Today]-[StartDate] will default to days (i.e. 455)

=((Today]-[StartDate])/365) will give you years, but as 1.2462591864

=ROUNDDOWN((([Today]-[StartDate])/365),1) where “1” is the number of decimal places you want. You could also ROUNDUP. This will give you 1.2

=CONCATENATE((ROUNDDOWN((([Today]-[StartDate])/365),1)),” yrs”) adds “yrs” or any other text before or after your number (i.e. 1.2 yrs)

Tip: You can sandwich the calculated number between multiple text bits. For example, =CONCATENATE(“Member for “,(ROUNDDOWN((([Today]-[StartDate])/365),1)),” yrs”) would give you “Member for 1.2 yrs” as a result.

Weeks, hours, minutes, oh my!

Days are almost exactly the same as the steps above, but leave out the “/365” part. It’s also a small adjustment to get weeks, hours or minutes.

Days: =([Today]-[LastPurchase])

Weeks: =([Today]-[LastPurchase])/7

Hours: =([Today]-[LastPurchase])*24

Minutes: =([Today]-[LastPurchase])*1440

Blank Date Values

Now let’s imagine we have a customer who has never made a contact, visit or purchase. Or perhaps this is days since last incident or violation. So we have a blank date column being used in a calculated column. This will give us an unfortunate result for “Days since last purchase” as Brady Apple has below.

blankdate

Simply adjust your calculated formula as follows. Basically we’re saying if “LastPurchase” is blank, return “N/A” otherwise do the calculation. Note I’m using years again since it’s more likely to be more useful to most.

Tip: “N/A” could be “No purchases made” or even “” to return a blank value instead. 

=IF(ISBLANK([LastPurchase]),”N/A”,(([Today]-[LastPurchase])/365))

and adding in our rounding and concatenating, we get:

=IF(ISBLANK([LastPurchase]),”N/A”,(CONCATENATE((ROUNDDOWN((([Today]-[LastPurchase])/365),1)),” yrs”))

which gives us:blankdate2Tip: No need to use ROUNDDOWN or ROUNDUP for a calculation of days since it rounds automatically. Only round for years, weeks, hours or minutes.

24 Replies to “Date calculations using “Today” in SharePoint lists for years of service, days without incident, etc. (includes using blank date values)”

  1. Hi,

    Really helpful and useful article!

    Two questions here

    Obviously microsoft flow updates the today date column to keep that up to date daily. I understand that part – but I thought calculated columns couldn’t dynamically update? IE if I follow this guide and the calculation happens and shows for example 1 Year. Am I right in thinking that won’t dynamically update? IE after 2 years have passed it will still show 1 year? Or are you saying because the today date column updates daily it forces the calculated column to recalculate daily?

    Second question – how do I get it to round so it doesn’t show decimal points thoughout a year and just a round year number. EG: 1 Year, 2 Year, 3 Year. Not the 0.9 / 1.5 / 2.3 etc?

    Thanks,

    JH

    1. For reference here is the formula I’m using

      =IF(ISBLANK([Purchase Date]),”Unknown”,(CONCATENATE((ROUNDDOWN(((TodayDate-[Purchase Date])/365),1)),” yrs”)))

    2. Hi, I’ve worked out the forumla I need. For reference this works for rounding to nearest year as a whole value.

      =IF(ISBLANK([Purchase Date]),”Unknown”,(CONCATENATE((ROUND(((TodayDate-[Purchase Date])/365),0)),” yrs”)))

      That looks to do the job

      Nate – Can you still answer my question regarding calculated column please?

  2. Is it possible to do this type of formula(=IF(ISBLANK([LastPurchase]),”N/A”,(([Today]-[LastPurchase])/365)) ) using work days only?

  3. I’m trying to create a List Column to do this =([Today]-[DateReceived]) but it errors with “Sorry, something went wrong. The formula contains reference(s) to field(s).”
    “DateReceived” Column is Date format.
    Created Column “Today” (Date format), not viewable.
    Tried to create new column “Days Open”, Single line of text format (I tried Date format too)
    Default value: Calculated Value: “=([Today]-[DateReceived])”
    SharePoint 2016, Tried in IE 11 and Edge 44.18362.449.0
    I cannot use SharePoint Designer

      1. Thank you for your quick response Nate! I don’t have ‘Calculated Column’ available as a Column type. Is there a setting somewhere to make that viewable as an option?
        Under Additional Column Settings, there is a Default value Options are Text and Calculated Value. When I selected Calculated Value, there’s a text field to enter in the formula. Is the Calculated Value section here different from where i should be selecting it?

        1. Hi Rich, you’ll only see it as a column type option when you’re creating a new column. It isn’t available on pre-existing.

  4. Hi is there a way to reduce the item count day by day ,based on todays date for ex:i have coloumn “No of days remained”=80, if date equals to today the reduce 80 daily 79 78 77 …..like this

    1. Yes, you just need a date of the “final” date (0 days) and then you would take FinalDate-[Today] to get the number of days left between today and the date that was originally 80 days out.

  5. When I attempt this, i get an error stating that you can’t use volatile functions like [Today] and [Me] in a calculated column.

  6. Hello,

    unfortunately, i keep running into syntax error. The calculation between current date (build with flow ) – date started gives me a result, but when i add a function the calculation always goes into syntax error.

    I read somewhere that this could have something to do with regional/language settings?

    Any ideas?

    Thank you,

    kr

      1. Hi Nate, thanks a lot for your quick answer. Unfortunately I don’t have Flow and can’t use SP Designer as I don’t have admistrative roles… I will therefore contact the administrators in order to follow your instructions with SP Designer. Thank you very much !

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.