SOLVED

SP Calculated Date

Iron Contributor

I am using the formula below - taken from here 

 

=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?

4 Replies
best response confirmed by jonboylib (Iron Contributor)
Solution

@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 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'

@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)

1 best response

Accepted Solutions
best response confirmed by jonboylib (Iron Contributor)
Solution

@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)

View solution in original post