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

[Bryan123]

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?

[#2]

@Bryan123 

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?

[#3]

@Steven Andrews 

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

[#4]

@Bryan123 

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

[#5]

@Steven Andrews 

yes it does

[#6]

@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.

[#7]

@RobElliott 

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