Forum Discussion
Sp list - calculated column to add numbers to a date, does not give a new date
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
That does sound a little quirky. Both lists use the same locale for their date formatting?
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.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
