Answered

How to handle the date format and Decimal from the excel

(0) Share

Report

Posted on by AnupVerma1988

Hello there,

I am using a power automate flow to filter unique rows from excel table based one email id and send email to respective users with the HTML table.

The issue i am getting is the output for date and numbers are not in formatted properly. Can someone please guide.

Below is the flow i created.

Flow Issue.JPG Flow 1.JPG Flow 2.JPG Flow 3.JPG Flow 4.JPG

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

Sort by

Ellis Karim 12,044 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Try the following:

(1) In Excel, select the Date column (e.g. StartDate in the example below) and make sure that the column type is set to Date.

(2) For the values such as 200824.5008 - this looks like an Excel serial number for date time. This could be converted to a time but for 200824.5008 I am getting 31/10/2449 12:01:09. So I'm not sure what these values represent. See the following post 6 Ways to Fix Dates Formatted as Text in Excel and Excel Time Calculation Tricks.

(3) In the List rows present in a table, make sure that ISO 8601 is set:

(4) See Easily convert Excel Dates to usable Power Automate Dates

https://www.tachytelic.net/2020/11/convert-excel-dates-power-automate/

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

Was this reply helpful? Yes No
AnupVerma1988 8 on at

Like (0)

Report
Copy link

Link copied!

Thanks Ekarim for your reply.
I have changed the date settings and also set the Date time at ISO 8601. However the issues still persist.
also for Point 2:- This is a total amount Column .200824.5008. I want to set it to two decimal places. For example
200824.50.

Was this reply helpful? Yes No
Ellis Karim 12,044 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

There is a formatNumber function. The number format specifiers (e.g. currency, percent etc) are listed here: Standard numeric format strings | Microsoft Docs.

For the Excel data formatting issue, please see again: Easily convert Excel Dates to usable Power Automate Dates where @Paulie78 shows how to deal with this type of problem (section on Validate the Excel data first):

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

Was this reply helpful? Yes No
Verified answer

abm abm 32,985 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hi @AnupVerma1988

@ekarim2020 Thanks for the updates. Anup contacted me via my YouTube Channel.

The issue was date format was General in Excel sheet and it was inconsistent. Other issue was some of fields were blank. This is resolved via an IF Expression.

if(empty(item()?['Revised PO Start date']),'', item()?['Revised PO Start date'])

Thanks

Was this reply helpful? Yes No
AnupVerma1988 8 on at

Like (1)

Report
Copy link

Link copied!

Thank you very much for the help Abm alias Ajith. Really appreciate it. Thank you for digging to the actual issue which helped in correcting my flow. The issue is sorted now.
I would also like to thank @ekarim2020 for the valuable inputs.

Thanks,
Anup.V

Was this reply helpful? Yes No