Aug 25 2020 11:37 AM
So I have a date column. I also have a calculated column which is basically
=date+10
Most of the time this works and I get a new, correct date. Sometimes, I just get a number, say 256. Odd thing is, if I edit the item in question and save it, it then pulls the correct date value.
Has anyone else seen this?
Aug 25 2020 11:46 AM
In the calculated column settings you can specify what format the returned value is. From memory one of the options is a date format. Have you made sure that this is selected?
Aug 25 2020 12:51 PM
Yes, its listed as a date. Most of the time it works, but sometimes it doesn't.
The way it works is: we copy info from a spread sheet and paste it into a SP list. That info is then pushed into the 2nd SP list by Power Automate. It's this 2nd SP list where the calculated column is
Odd thing is, all it takes is for the record to be updated once to correct it
Aug 25 2020 02:40 PM
That does sound a little quirky. Both lists use the same locale for their date formatting?
Aug 31 2020 11:46 AM
Sep 01 2020 05:19 AM - edited Sep 01 2020 05:20 AM
@Bryan123 "That info is then pushed into the 2nd SP list by Power Automate. It's this 2nd SP list where the calculated column is". Most problems with Power Automate and Excel Dates revolve around the fact that Excel stores dates as integers. So you need to convert the Excel integer to a date with an expression in a Compose action like this (Time is my column in Excel with the date):
formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['Time']),-2)), 'yyyy-MM-dd')
(the way I do it is to use 1900-01-01 for ease of remembering rather than 1899-12-30, then take off 2 days from the result)
Then you use the output from the compose to add the date to the SharePoint list.
You might have already done this, but if not give it a try and it might solve your problem.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.
Sep 01 2020 12:02 PM
Would this account for it working some times and not other times? Would it account for all that was needed to fix it was a simple update to the record which I'm assuming just refreshes the columns? Nothing that impacted the date field was updated