Excel Online dates are converted to integers in Send Email

Like (0) Share

Report

Posted on 6 Mar 2020 06:38:38 by Community Power Pla...

Hi,

I'm new to building flows, and i've set up an excel table for Excel and Flow to be connected via OneDrive.

The challenge im facing is that the date format in excel is converted to integers via the emails sent.

I noted that this problem exist only when i'm using "List rows present in a table", but if i'm only using "Selected row" flow, i'm able to select formatted dates. Need some help here, please 🙂

The flow i have as below:

I have the same question (0)

All responses (4)

Answers (1)

efialttes 14,756 on 06 Mar 2020 at 07:00:51

Like (0)

Report

Re: Excel Online dates are converted to integers in Send Email

Hi!
There are several posts covering this issue. This is one of them
Hope it helps
https://powerusers.microsoft.com/t5/Using-Flows/Excel-online-business-converts-date-to-days-So-how-do-i-compare/td-p/247418

Was this reply helpful? Yes No
Verified answer

v-alzhan-msft on 06 Mar 2020 at 07:02:12

Like (1)

Report
Re: Excel Online dates are converted to integers in Send Email

Hi @Anonymous ,

Please refer to screenshot below to create the flow:

You could get the date in the Compose 2 action with expression below:

formatDateTime(addDays('1900-01-01',add(int(outputs('Compose')),-2)),'yyyy-MM-dd')

Best Regards,

Alice

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
efialttes 14,756 on 06 Mar 2020 at 07:11:21

Like (0)

Report

Re: Excel Online dates are converted to integers in Send Email

Hi!
The explanation of this is... you need to calculate the nr of days between the desired date and the one Excel uses as start point to generate the integer values. The date calendar in Excel starts on January 1st, 1900. Please also note, that, according to this post:

https://www.epochconverter.com/seconds-days-since-y0

"Days Since 1900-01-01 (+2) is used in the Microsoft Excel date functions such as DAY(serial_number) and WEEKDAY().

Note, Excel's serial number is 2 higherthan the number on this page.

In Excel January 1 is serial number 1. In this converter midnight January 1, 1900 is 0, after 1 day it is midnight on January 2. To correct this you have to add/subtract 1.Excel incorrectly sees 1900 as a leap year (for compatibility with Lotus 1-2-3) so you have to add/subtract 1 to all days when using in Excel. Also see: Excel incorrectly assumes that the year 1900 is a leap year"

Hope this helps

Was this reply helpful? Yes No
Community Power Pla... on 06 Mar 2020 at 07:14:25

Like (0)

Report

Re: Excel Online dates are converted to integers in Send Email

@v-alzhan-msft, Thank you, this works like a charm!

Was this reply helpful? Yes No