In your SharePoint form submissions and list items, sometimes you’d like date fields to be displayed a different way for workflows, notifications, views, grouping, additional metadata, etc. See videos at bottom of post for a couple demonstrations.
Just create a calculated column (format as single line of text) in your SharePoint list or library. Then use any of these formulas, replacing [Created] with the name of the date field from which you’re extracting a piece:
| To Extract: | Use Formula: | Example |
| Year | =TEXT([Created],”YYYY”) | 2018 |
| Fiscal Year Range (4-digit) | =IF(MONTH([Created])>6,YEAR([Created])&”-“&YEAR([Created])+1,YEAR([Created])-1&”-“&YEAR([Created])) | 2018-2019 |
| Fiscal Year Range (2-digit) | =IF(MONTH([Created])>6,”FY ” & RIGHT(YEAR([Created])+1,2)&”-“&RIGHT(YEAR([Created])+1,2)+1, “FY ” & RIGHT(YEAR([Created])+1,2)-1&”-“&RIGHT(YEAR([Created])+1,2)) | FY 18-19 |
| Quarter | =”Q” & CHOOSE( MONTH([Date of inspection]) ,1,1,1 ,2,2,2 ,3,3,3 ,4,4,4) | Q3 |
| Month (2 digits) | =TEXT([Created],”MM”) | 08 |
| Month (abbreviation) | =TEXT([Created],”MMM”) | Aug |
| Month (full name) | =TEXT([Created],”MMMM”) | August |
| Week Number | =”Week ” & IF(ROUNDUP((ROUNDDOWN([Created],0)-(DATE(YEAR(ROUNDDOWN([Created],0)),1,1))+WEEKDAY((DATE(YEAR(ROUNDDOWN([Created],0)),1,1))))/7,0)>52,1,ROUNDUP((ROUNDDOWN([Created],0)-(DATE(YEAR(ROUNDDOWN([Created],0)),1,1))+WEEKDAY((DATE(YEAR(ROUNDDOWN([Created],0)),1,1))))/7,0)) | Week 35 |
| Day (2 digits) | =TEXT([Created],”DD”) | 27 |
| Weekday (abbreviation) | =TEXT([Created],”DDD”) | Mon |
| Weekday (full name) | =TEXT([Created],”DDDD”) | Monday |
Next-level Tips
- Build your own date format combining this logic (space and punctuation friendly):
- =TEXT([Created],”MMMM DD”) for August 27
- =TEXT([Created],”MMM-YYYY”) for Aug-2018
- Ampersand (&) joins any strings together.
- =[Created] & TEXT([Created],”(DDDD)”) will give you Aug-27-2018 (Monday)
- =”Fiscal Year ” & IF(MONTH([Created])>6,YEAR([Created])&”-“&YEAR([Created])+1,YEAR([Created])-1&”-“&YEAR([Created])) for “Fiscal Year 2018-2019”
- =[Student Name] & TEXT([Created],” (MMM YYYY)”) gives you Nate Chamberlain (Aug 2018)
- Experiment with different date formats. YYYY-MM sorts well in lists. I use YYYY-MM (MMM) for clients a lot so it will sort well and also tell you the month name to be crystal clear:
- =TEXT([Created],”YYYY-MM (MMM)”) gives you 2018-08 (Aug)
- To prevent “1899” showing up in your calculated column, use an if/then statement to “skip” blank date values or provide default text:
- =IF([Due Date]<>””,TEXT([Due Date],”YYYY-MM (MMM)”),”No Due Date”)
- Brackets ([ ]) are not required in formulas for one-word/no-space date fields. Brackets are only needed for “Due Date”, “Start Date” or other multi-word field names. However, they don’t hurt anything if you already have them.
- You can also use MONTH() and YEAR() to extract just those pieces, but I find the TEXT() function easiest to be able to get exactly what you want and combine multiple values more efficiently.
I’m working in SharePoint 2013.
I created a calculated column of data type = SLOT with the formula: =IF(Month(Created)>9,Year(Created)+1,Year(Created))
It works but it’s returning the as 2,019 (with a comma).
I tried wrapping the TEXT function around the above formula as follows: =TEXT((IF(Month(Created)>9,Year(Created)+1,Year(Created))),”YY”) but this returns “05”
I don’t have access to Notepad++. I tried to troubleshoot in Excel but with no luck either.