How to handle the date format and Decimal from the excel

[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.

[ekarim2020]

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.

[AnupVerma1988]

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.

[ekarim2020]

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.

[abm · Accepted solution]

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

[AnupVerma1988]

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