Date Field is off by one day

[Dom32]

Hello,

I have a Power Automate flow that started acting weird ... today.  My input is a field date, that comes as EPOCH so I convert to Date format. it was setup a month ago, everything was working fine (last Friday input date matched output date). But today, the OUTPUT is off my one day .... Nothing has changed....

code: convertTimeZone(addSeconds('1970-1-1', Div(items('Apply_to_each_2')?['Attributes/EditDate'],1000),'MM/dd/yyyy hh:mm:ss'),'UTC','Central Standard Time','MM/dd/yyyy hh:mm:ss')

sample of the input

which is 

and this is what was returns from Expression:

Any help would be much appreciated

Thanks!

Dominic

[SudeepGhatakNZ]

@Dom32 ,

Is your date saved in SharePoint?

[wskinnermctc · Accepted solution]

Hello @Dom32 I made an example flow and it seems like it has something to do with how your date is formatted without AM/PM. When I made one side the same as each step in your expression I got the incorrect date as you did that was one day behind. However, when I changed the date format to include the AM/PM then it gave me the correct date.

I can't be sure what causes it. But you should probably try to format your expression like below:

convertTimeZone(addSeconds('1970-1-1', Div(items('Apply_to_each_2')?['Attributes/EditDate'],1000),'MM/dd/yyyy hh:mm:ss tt'),'UTC','Central Standard Time','MM/dd/yyyy hh:mm:ss tt')

 To add AM/PM to a time, you put 'tt' where ever you want it to show up.

Below is my example flow. It has 2 sides that are the same except side A does not have the AM/PM and side B does.

Example Flow

Below is a picture of results for side A that was incorrect.

Flow Result Side A

Below is a result of Side B that was correct:

Flow Result Side B

Below are the comparison of the result steps that are different:

Flow Results Summary

I'm sure there is a technical reason why the AM/PM is causing a problem. Must have to do with how it perceives UTC in that format. I don't know really, just happened to test it and it worked.

Let me know if this ended up working for you,

[ScottShearer]

 @Dom32   It looks like your data is coming from Excel.  If so, go to advanced properties and set the date format to ISO 8601.  Then you'll get a date back instead of a number.

[Chriddle]

It's simple:

Your intermediate format in the addSeconds function corrupts the date.

Just use:

convertTimeZone(
	addSeconds(
		'1970-1-1',
		Div(1674488535556,1000)
	),
	'UTC','Central Standard Time',
	'MM/dd/yyyy hh:mm:ss'
)

Output: 01/23/2023 09:42:15

[wskinnermctc]

Yes it looks like @Chriddle is correct. You could skip the intermediate formatting of date when you add seconds and it will just format the final output. I made a third example in the flow that shows the output will be correct.

Flow Example Not Formatting Date when Add Seconds step

The results of the third example when not adding the formatdate during the add seconds step will have the same output if you did format the datetime with the additional 'tt' (AM/PM) during the add seconds step.

Correct Outputs With AM/PM or Not FormatDate During Add Seconds

Your full expression could be either below:

Format the DateTime during Add Seconds with MM/dd/yyyy hh:mm:ss tt:

convertTimeZone(addSeconds('1970-1-1', Div(items('Apply_to_each_2')?['Attributes/EditDate'],1000),'MM/dd/yyyy hh:mm:ss tt'),'UTC','Central Standard Time','MM/dd/yyyy hh:mm:ss tt')

Or

Format the DateTime last during convert time zone and do not complete any datetime formatting during the add seconds step:

convertTimeZone(addSeconds('1970-1-1', Div(items('Apply_to_each_2')?['Attributes/EditDate'],1000)),'UTC','Central Standard Time','MM/dd/yyyy hh:mm:ss tt')

Good Luck!

[Dom32]

Thank you all for responding.  I accepted @wskinnermctc  solution as it was the first one I tried and solved my problem.  But @Chriddle  solution made it cleaner.  You guys saved the week  🙂

Thanks again!

D

[wskinnermctc]

Just adding a note that I realized when trying to test this.

The expression ConvertFromUTC is not usable in the final format because the addSeconds expression doesn't put a 'Z' on the end. Power Automate reads a time string that ends in 'Z' as UTC. 

If the string doesn't have 'Z' at the end, then PA doesn't recognize the string as UTC and therefore it doesn't accept it in the ConvertFromUTC.

It is silly but the addSeconds step would have first be ConvertTimeZone from UTC to UTC in order for the additional 'Z' to be put on the end. Then use the ConvertFromUTC expression on the string that now ends in 'Z'.

This is obviously an unnecessary additional step.

The reason ConvertTimeZone expression works and ConvertFromUTC expression does not work in this case is because ConvertTimeZone is basically an add hours expression. It is accepting whatever you put in as the source timezone and then adding or subtracting the appropriate amount of hours to get to the destination timezone. The ConvertTimeZone doesn't check if the string matches the source timezone format, it only needs it to be in a datetime format.

Example of ConvertFromUTC when using addSeconds

Results showing that the addSeconds step doesn't have the 'Z' on the end therefore ConvertFromUTC errors.

Results of ConvertFromUTC