Forum Discussion

Iron Contributor

Aug 19, 2022

Solved

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%20mon...

Lists

SharePoint Online

RobElliott
Aug 19, 2022
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

Silver Contributor

Aug 19, 2022

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)

jonboylib

Iron Contributor

Aug 19, 2022

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

RobElliott
Silver Contributor
Aug 19, 2022
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)