Power Automate - General Discussion

Answered

Date Format from Excel to HTML Table

Like (0) Share

Report

Posted on 4 Feb 2019 10:15:17 by Neicilef

Hi,

After investigations in this forum and tests (Function formatDateTime) without no concrete results, I'am asking for help.

I have an Online Excel Table with Date.

Picture 1

I use this flow :

Picture 2

NB : I know the flow isn't maximized. It's just an example to create this case.

My objective : Send a weekly email with filtered informations and DATE

Problem : Dates aren't kept in a good format

Picture 3

How can I keep the date format from an excel to an HTML Table ?

Thanks for your help.

Best Regards,

Categories:

General Questions

All responses (5)

Answers (2)

HarshavardhanG 49 on 16 Nov 2020 at 05:41:51

Like (0)

Report

Re: Date Format from Excel to HTML Table

I have tried all the above solutions, but can't seem to get it working.

I have raised a new post here: https://powerusers.microsoft.com/t5/Building-Flows/Format-timestamp-from-Excel-Online-to-send-mails/...

Can someone please guide me?
Verified answer

Neicilef 16 on 04 Feb 2019 at 16:13:21

Like (1)

Report

Re: Date Format from Excel to HTML Table

Hi @LeeHarris,

I'm using indeed Excel Online.

Thanks for the trick (Function addDays), it's almost working now.

With the expression
addDays('1900-01-01',int(item()['Date']))
the result will be in the HTML Table : 2019-02-02T00:00:00.0000000

Just for the format, I'm using now the expression :
addDays('1900-01-01',int(item()['Date']),'yyyy-MM-dd')
The result will be in the HTML Table : 02-02-2019

BUT I had 2 days of difference between the date in the Excel and the HTML Table.
So I'm using now :
addDays(addDays('01-01-1900',int(item()['Date'])),-2,'dd-MM-yyyy')
Verified answer

LeeHarris 1,026 on 04 Feb 2019 at 15:35:25

Like (1)

Report

Re: Date Format from Excel to HTML Table

Hi @Neicilef

If you are using the Excel Online connector please take a look at this similar issue.

https://powerusers.microsoft.com/t5/Building-Flows/Excel-dates-turned-into-integers/td-p/130364

There is a comment suggesting the following expression as a workaround to the way Excel Online returns an integer representing the date as the number of days since 01/01/1900.

addDays('1900-01-01',int(YourDateHere))
Neicilef 16 on 04 Feb 2019 at 13:12:21

Like (0)

Report

Re: Date Format from Excel to HTML Table

Hi @LeeHarris

Thank you for taking time.

Below, my first test with input and ouput :
1st Test Input
1st Test Output
Result : Not working

Below, the test after your post
Our Test Input
Our Test Output
Result : Not working
LeeHarris 1,026 on 04 Feb 2019 at 10:34:55

Like (0)

Report

Re: Date Format from Excel to HTML Table

Hi @Neicilef

You should be able to control the format of your date field by using the FormatDateTime expression e.g.

formatDateTime(triggerBody()['date'],'yyyy-MM-dd'). You mention you have looked at this already but it didn't work. What output did you get from this expression?

The triggerBody()['date'] parameter is your Dynamic value from Excel so you would need to replace this. The 'yyyy-MM-dd' indicates that the date will be displayed in the format 2019-02-04. As long as the value from Excel is a valid date time, the format expression should ensure that the output in your HTML table is consistent.

More information on date formats can be found here.

Community site session details

Date Format from Excel to HTML Table

Helpful resources

News and Announcements

Quick Links

Understanding Microsoft Agents - Introductory Session

Warren Belz – Community Spotlight

Congratulations to the April Top 10 Community Stars!

Leaderboard

Featured topics

Product updates

Community site session details

Date Format from Excel to HTML Table

Helpful resources

News and Announcements

Quick Links

Understanding Microsoft Agents - Introductory Session

Warren Belz – Community Spotlight

Congratulations to the April Top 10 Community Stars!

Subscribe to this forum!

Select categories

Leaderboard

Featured topics

Product updates