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.
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:
Tip: No need to use ROUNDDOWN or ROUNDUP for a calculation of days since it rounds automatically. Only round for years, weeks, hours or minutes.
Help! M reporting period each week is Thursday – Wednesday. I want to create two views – current period (I pull reports on Thursday so it needs to be from the Thursday before to Wednesday) and prior period, which would be the Thursday – Wednesday the week before current period. Suggestions on calculations???
Excellent article but when I try it on my SharePoint list I get the following error.
Calculated columns cannot contain volatile functions like Today and Me
dont use [TODAY], use the actual calculation TODAY().
So, for example: =(TODAY()-[DATE ON LIST])/365
This will give you the years between today and the date in the column on your sharepoint list.
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
For reference here is the formula I’m using
=IF(ISBLANK([Purchase Date]),”Unknown”,(CONCATENATE((ROUNDDOWN(((TodayDate-[Purchase Date])/365),1)),” yrs”)))
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?
Is it possible to do this type of formula(=IF(ISBLANK([LastPurchase]),”N/A”,(([Today]-[LastPurchase])/365)) ) using work days only?
Hi,
Is there any way to calculate the current week number using “Today()”?
Thanks
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
Your new column, Days Open, needs to be of column type ‘Calculated Column’ instead of ‘Single line of text’. Then you’ll be able to use the formula you’ve shared.
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?
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.
Thank you Nate! That did the trick!
Hello,
I want to Count a number of days without Weekend.Can somoene helpe me please.
Thank you
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
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.
When I attempt this, i get an error stating that you can’t use volatile functions like [Today] and [Me] in a calculated column.
Try Today() instead of [Today]
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
Hello, do you know if there is a way to achieve the same result in SharePoint Server 2016 as the TODAY function can’t be used in calculated columns ? (cf https://support.office.com/en-us/article/today-function-e76dd490-0579-453f-8dd3-fbbed4357ff2)
Thanks in advance!
Hi Eloise. You’ll need to create a new date column in your list to use in calculations. I use SP Server 2016 as well and this will work. It links to an alternative method using SharePoint Designer if Flow isn’t possible for you: https://sharepointlibrarian.com/2017/12/08/use-microsoft-flow-to-create-a-today-column-for-use-in-sharepoint-list-calculations/
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 !
Doesn’t work in Office 365
Hi Richard, I’m using O365 myself and it works – are you running into a specific issue you can share?