Date and time format issue for an HTML table

[BastienT]

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

[manuelstgomes]

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

[BastienT]

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:

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?

[BastienT]

if anyone is having a solution for that.

My HTML table in my email is the following

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

[BastienT · Accepted solution]

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