Sp list - calculated column to add numbers to a date, does not give a new date

Question

So I have a date column.&nbsp; I also have a calculated column which is basically&nbsp;=date+10&nbsp;Most of the time this works and I get a new, correct date.&nbsp; Sometimes, I just get a number, say 256.&nbsp; Odd thing is, if I edit the item in question and save it, it then pulls the correct date value.&nbsp;Has anyone else seen this?

robelliott · Answer

Bryan123&nbsp;"That info is then pushed into the 2nd SP list by Power Automate.&nbsp; 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')&nbsp;(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)&nbsp;Then you use the output from the compose to add the date to the SharePoint list.&nbsp;You might have already done this, but if not give it a try and it might solve your problem.&nbsp;RobLos GallardosMicrosoft 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.

steven andrews · Answer

Bryan123&nbsp;In the calculated column settings you can specify what format the returned value is.&nbsp; From memory one of the options is a date format.&nbsp; Have you made sure that this is selected?

bryan123 · Answer

Steven Andrews&nbsp;&nbsp;Yes, its listed as a date.&nbsp; Most of the time it works, but sometimes it doesn't.&nbsp;The way it works is: we copy info from a spread sheet and paste it into a SP list.&nbsp; That info is then pushed into the 2nd SP list by Power Automate.&nbsp; It's this 2nd SP list where the calculated column is&nbsp;Odd thing is, all it takes is for the record to be updated once to correct it

steven andrews · Answer

Bryan123&nbsp;That does sound a little quirky.&nbsp; Both lists use the same locale for their date formatting?

bryan123 · Answer

Steven Andrews&nbsp;&nbsp;yes it does

Forum Discussion

Sp list - calculated column to add numbers to a date, does not give a new date

6 Replies

Resources