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.
Leave a Reply