cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Super User II
Super User II

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
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (73,581)