Convert Excel DateTime into displayable string in Flow

(0) Share

Report

Posted on by mfabian

This is my second day with PowerApps and I guess I'm missing something (if not to say alot 😞 )

Here comes my problem:

I have an flow which reads a datetime (LastEvent) from an excel worksheet and passes this date into an e-mail:

The e-mail contains the raw Excel datetime and therefore reads like this:

Last Run: 44074.5840277778

What I want to achieve is to format this excel-datetime which is a decimal in Excel (but for some unknown reason interpreted as String in Flow) into human readable form for the e-mail.

What I would expect is to either have a possibility to create or use an existing function, which converts an Excel-Datetime into a readable form. After all, both products (Excel and PowerApps) are Microsoft products.

1. Where do I get such a function and

2. Where Do I implement this function?

I assume that I could use this function directly on the e-mail and replace the raw "LastEvent" output from Excel with something like "convertExcelDatetime(LastEvent, 'dd.mm.yy hh:mm:ss')".

Is that correct? Or do I need to put one of the fancy actions between my "Get Last Event (Excel") and "Send an Email"? And if yes, which action would do the trick?

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

v-bacao-msft on at

Like (0)

Report

Hi @mfabian ,

Because the Date in the Excel table is actually stored as a number, in Flow we can only get the actual stored value instead of the date.

So we need to convert the number to date first, please try to refer to the following article to convert the number to date:

Handling Date-Time in Excel to SharePoint List through MS Flow

Hope this helps.

Best Regards,

Was this reply helpful? Yes No
biancoluke 6 on at

Like (2)

Report

For anyone who is still struggling try using the below:

addDays('01/01/1900',sub(int(items('Apply_to_each_2')?['Date of Infrigement']),2),'dd/MM/yyyy')

items('Apply_to_each_2')?['Date of Infrigement']) is the item/variable that contains the string. Example From Excel or Form or Sharepoint list.

Was this reply helpful? Yes No
Oxytetracyclio 3 on at

Like (1)

Report
Worked perfectly for me! Mine ended up looking like below:
addDays('01/01/1900',sub(int(items('Apply_to_each')?['End Date']),2),'dd/MM/yyyy')
'Apply_to_each' is the name of the action and 'End Date' is the name of the column in the xlsx that contains the desired dates. Thanks for posting this!

Was this reply helpful? Yes No