Explanation convert Date from Excel in Iso Format

(0) Share

Report

Posted on by HansenvomDeich

184

Hello,
I also always try to understand the things that I use by searching and then copy-pasting.

The following constellation:
Excel date in Cell:
25.01.2023
Input into Power Automate:
44951

Expression for converting and get the correct date without errors in Power Automate:
addDays('01/01/1900',sub(int(outputs('Compose')),2),'dd/MM/yyyy')

I had Chat GPT explain the formula to me in order to improve it for next time.

Why do 2 days have to be deducted? I suspect it's a "simple" computer language that I don't understand yet.

I would be happy if someone could explain it to me.
Thank You

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (2)

Verified answer

JonDoesFlow 1,304 on at

Like (0)

Report

Excel's got this funky way of marking the calendar's start line at "Day 0", which it thinks is January 0, 1900. But in reality, Day 1 is actually January 1, 1900. So, when you're trying to match dates from Excel's serial numbers to real dates, you're always a day ahead from the start.

Plus, Excel treats 1900 as a leap year, it wasn't. Thisthrows in an extra day if you're counting dates from way back then. So, between starting the count at "Day 0" and pretending 1900 is a leap year, Excel's date maths can get a bit skewed.

---------------------------------------------------------------------------

If I have helped you with an answer, please accept this post as a solution and thumbs up my reply. Thanks !

Blog - JonDoesFlow
Twitter - JonDoesFlow
YouTube - JonDoesFlow

Was this reply helpful? Yes No
Verified answer

HansenvomDeich 184 on at

Like (0)

Report

Oh wow, great 😄

Thanks !

Was this reply helpful? Yes No