cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BastienT
Frequent Visitor

Date and time format issue for an HTML table

Morning All,

 

I'm looking for assistant in order to manage properly date and time format in one of my flows.

 

1st situation: i have a "start date" (with date and minute) provided by an Excel file : 43956.3333333333

and i'm putting it in an HTML table. Adding the date in the HTML table is ok, but i didn't manage so far to make this "start date" appearing with the right format and not "43956.3333333333", any recommendation?

i tried to use: "formatdatetime" (struggling with ISO 8601) or"addDays" with something like : addDays('1899-12-30',item()?['Start date'],'yyyy-MM-dd')

with no success, what could be the correct formula?

 

2nd situation: i have, for the same purpose, a duration ( number of second) that i need to translate in hh+min, what could be the correct formula to setup in the HTML table to have the right format?

 

Thanks in advance for your guidance!

Bastien

1 ACCEPTED SOLUTION

Accepted Solutions

So i found the solution by myself

 

To convert "43956.3333333333" on the fly in the HTML table in a proper format, the (not perfect) formula could be:

concat(formatDateTime(addDays('1899-12-30',int(first(split(item()?['Start date'],'.'))),'yyyy-MM-dd'),'dd-MM-yyyy'),' ',formatDateTime(addSeconds(concat(formatDateTime(utcNow(),'yyyy-MM-ddT'),'00:00:00'),int(first(split(string(mul(sub(float(item()?['Start date']),int(first(split(item()?['Start date'],'.')))),86400)),'.')))),'HH:mm'))

 

result ==> 05-05-2020 08:00

 

To convert the duration provided in seconds "31500" on the fly in the HTML table, i'm using:

formatDateTime(addSeconds(concat(formatDateTime(utcNow(),'yyyy-MM-ddT'),'00:00:00'), int(item()?['Business duration'])),'HH:mm')

 

result: 08:45

 

Thanks for your guidance

View solution in original post

4 REPLIES 4
manuelstgomes
Super User
Super User

Hi @BastienT 

 

I have a whole article explaining why that happens, a template and how to convert it to a date.

https://manueltgomes.com/microsoft/powerautomate/convert-excel-number-to-date/

 

Can you please check if and let me know if you have any questions?

 

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

Hi @manuelstgomes ,

 

Thanks Manuel, very interesting! Finally i can see that i was on the right direction for my first case 🙂

 

In your Flow template, the number is given at the beginning as an input. In my situation i'm getting my "start date" from an excel file and furthermore i have several rows with different "start date" to manage on the fly during the HTML Table creation, so i don't know how to integrate your conversion that is in 3 steps.

i can initialize the first variable that's for sure, but for your second and third step we need to have the number before which i don't

 

So several thought:

- Can i do 2nd and 3rd step in once?

- Shall i update the full excel file first (damned)?

- Can i call your template when i need the conversion (really, can we?)

 

quick view of my current flow:

2020-08-20 15_07_55-Flow _ Microsoft Teams.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

i assume that for my case N°2 which is convert second (duration value above) in hh:mm it will have to be a bit different, isn't it?

if anyone is having a solution for that.

 

My HTML table in my email is the following

HTML table 2.png

And as you can see dates are not structured.

Any idea of the proper formula that can do the job for the three?

 

for the moment i'm fighting with something like: 

addDays('1899-12-30',item()?['Start date'],'yyyy-MM-dd')
but with no result
 
"Start date" and "end date" is a date with hours
and "duration" in hh:mm but in seconds
 
Thanks!!

So i found the solution by myself

 

To convert "43956.3333333333" on the fly in the HTML table in a proper format, the (not perfect) formula could be:

concat(formatDateTime(addDays('1899-12-30',int(first(split(item()?['Start date'],'.'))),'yyyy-MM-dd'),'dd-MM-yyyy'),' ',formatDateTime(addSeconds(concat(formatDateTime(utcNow(),'yyyy-MM-ddT'),'00:00:00'),int(first(split(string(mul(sub(float(item()?['Start date']),int(first(split(item()?['Start date'],'.')))),86400)),'.')))),'HH:mm'))

 

result ==> 05-05-2020 08:00

 

To convert the duration provided in seconds "31500" on the fly in the HTML table, i'm using:

formatDateTime(addSeconds(concat(formatDateTime(utcNow(),'yyyy-MM-ddT'),'00:00:00'), int(item()?['Business duration'])),'HH:mm')

 

result: 08:45

 

Thanks for your guidance

View solution in original post

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (2,754)