Forum Discussion
SP Calculated Date
I am using the formula below - taken from https://support.microsoft.com/en-us/office/examples-of-common-formulas-in-lists-d81f5f21-2b4e-45ce-b170-bf7ebf6988b3#:~:text=To%20add%20a%20number%20of%20months%20to%20a%20date%2C%20use%20the%20DATE%2C%20YEAR%2C%20MONTH%2C%20and%20DAY%20functions.
=DATE(YEAR([Date Purchased])+4,MONTH([Date Purchased]),DAY([Date Purchased]))
This successfully adds 4 years to my Date Purchased column. Now I want to achieve the following;
1. How to i display the date in 'friendly format', currently it's 18/07/2020, I would like July 18 2020
2. I only want to apply this formula when a column named 'Category' is equal to 'Desktop'
How can I achieve this?
jonboylib =TEXT(DATE(YEAR([Date Purchased])+4,MONTH([Date Purchased]),DAY([Date Purchased])),"mmmm dd yyyy")
To only apply this formatting when Category = Desktop use this:
=IF(Category="Desktop",TEXT(DATE(YEAR([Date Purchased])+4,MONTH([Date Purchased]),DAY([Date Purchased])),"mmmm dd yyyy"),DATE(YEAR([Date Purchased])+4,MONTH([Date Purchased]),DAY([Date Purchased])))
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
4 Replies
jonboylib =TEXT(DATE(YEAR([Date Purchased])+4,MONTH([Date Purchased]),DAY([Date Purchased])),"mmmm dd yyyy")
To only apply this formatting when Category = Desktop use this:
=IF(Category="Desktop",TEXT(DATE(YEAR([Date Purchased])+4,MONTH([Date Purchased]),DAY([Date Purchased])),"mmmm dd yyyy"),DATE(YEAR([Date Purchased])+4,MONTH([Date Purchased]),DAY([Date Purchased])))
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)RobElliott This does work, thanks.
However I notice that ordering this date column doesn't work. Is that because the calculation is returning a string and not a date/time?
In my calculated column settings I do have the following option selected;
The data type returned from this formula is: Date and Time
jonboylib yes, because you have a mix of text and dates it won't sort properly.
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
RobElliott Thanks for this.
What about my second requirement, any ideas?
2. I only want to apply this formula when a column named 'Category' is equal to 'Desktop'
