Jul 06 2020 01:50 AM
Jul 06 2020 01:50 AM
is there a way to calculate a date value in a column based upon the entered date in one column plus a duration entered in another column, e.g. I am recording a purchase date on an item and its warranty period in years and I want to calculate the expiry date. I know I can do this in SharePoint calculated column but the value returned is in a text format not in a true date format so it is not easily usable in powerapps and power automate. I just wondered if JSON would return a v true 'date
Jul 06 2020 08:11 AM - edited Jul 06 2020 08:14 AMSolution
Hi @JamesMWood you can do this with a simple flow in Power Automate. The columns are StartDate (DateTime), WarrantyYears(Number) and WarrantyExpiryDate (DateTime). When an item is created or modified in your SharePoint list we calculate the number of warranty days (years x 365), convert that to an integer and then add those days to the start date. Finally we update the SharePoint item with the warranty expiry date:
I always like to split up my Compose controls so that it's easy to see what each one is doing, but you could combine them if you wished to. The expressions in each Compose are shown in the comments.
Microsoft Power Automate Community Super User
Jul 08 2020 02:41 AM
@RobElliott Hi Rob, I have used the Compose options that you provided and when I updated the entry to test the flow I got the following error:
InvalidTemplate. Unable to process template language expressions in action 'ComposeWarrantyDays' inputs at line '1' and column '14067': 'The template language function 'mul' expects its first parameter to be an integer or a decimal number. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#mul for usage details.'.
Jul 08 2020 12:35 PM
Jul 14 2020 04:44 AM
@RobElliott I had to recreate the warranty period column from scratch and the first 2 compose actions work, I am now getting and error on the 3rd function with the following error.
Unable to process template language expressions in action 'ComposeAddDays' inputs at line '1' and column '13924': 'The template language function 'addDays' expects its first parameter to be a string that contains the time. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#adddays for usage details.'.
I have changed the format on the purchase date column to be date and time from date only, would you recommend re-doing the purchase date column from scratch?
Jul 14 2020 07:36 AM
@RobElliott I think I have worked out what is going on. Although I am not sure why it is causing issues the SharePoint list was originally an excel file with all of the same columns, which I imported into sharepoint using the app to create a list from an excel. As soon as I create the date and duration columns in SharePoint the calculations work, I am not sure why but I assume that the import app has brought in some weird formatting from excel.
So I can happily report that your solution works
Oct 28 2020 11:05 AM
You can use toLocaleDateString() function/operator in Column formatting to convert your date in friendly format.
According to official documentation,
toLocaleDateString(): returns a language sensitive representation of just the date portion of a date
"txtContent":"=toLocaleDateString(@now)" ---> results vary based on user's locale, but en-us looks like "2/5/2019"