Answered

Date Format from Excel to HTML Table

(0) Share

Report

Posted on 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 topics

I have the same question (0)

All responses (5)

Answers (2)

LeeHarris 1,026 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Neicilef 16 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Verified answer

LeeHarris 1,026 on at

Like (1)

Report
Copy link

Link copied!

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))

Was this reply helpful? Yes No
Verified answer

Neicilef 16 on at

Like (1)

Report
Copy link

Link copied!

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')

Was this reply helpful? Yes No
HarshavardhanG 49 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No