Format date gotten from excel table

(0) Share

Report

Posted on by wlinb

105

Hi,

I created a flow to send a table via email. One of the column is date but some dates are not in the correct format.

Output in the email:

The last review date is suppose to be 7/9/2024 but it becomes 45542.

Anyone knows how can I format the date to make it consistent?

Thank you!

Categories:

General topics

All responses (5)

Answers (1)

Sort by

Sundeep_Malik 6,501 on at

Like
a
(2)

Report
Copy link

Link copied!
Hey @wlinb

When handling Excel dates you will have to do 2 things.

1) When using List rows in a table, In date time format always give ISO8601

2) Second thing instead of directly writing next review date write an expression similar to below:

if(empty(item()?['NextReviewDate']),null,addDays('1899-12-30',int(item()?['NextReviewDate']),'yyyy-MM-dd'))

Giving you links where you can read more about it:

https://www.shanebart.com/ms-flow-excel-dates/

https://tomriha.com/how-to-get-date-from-excel-as-a-date-in-power-automate-flow/

https://www.youtube.com/watch?v=uFZxXMuLj-E

I hope this helps 🙂
wlinb 105 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Sundeep_Malik ,

Thank you for your advice.
Not all of my date output is integer, so this formula is not working for me.
I have set the date time format to ISO8601 and all my dates in the excel to the right format.

Right now I'm getting this if I input the next review date directly.
Do you know how can I remove the T*******Z and get only the date itself?
I tried to use formatdatetime() but it only gives me one date for all the rows
Verified answer

Sundeep_Malik 6,501 on at

Like
a
(0)

Report
Copy link

Link copied!

@wlinb

The expression for next review date would be

formatDateTime(item()['nextreviewdate'], 'MM-dd-yyyy')

Replace item()['nextreviewdate'] with the exact column name or just copy the dynamic expression you have currently written in select action and replace it with the above. As your field name has spaces could be possible it has x200 something in the field too.

If you don't know how it is add value coming from list rows action in a compose and see what field name you are getting.

And also replace the format with whatever format you want.
wlinb 105 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Sundeep_Malik ,

It works. Thank you!
zeeman05 8 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @wlinb

I am having the same issue as you. My values are coming in the T*******Z format as well. I performed all the above troubleshoot. However, were not able to fix the issue.

Any help is much appreciated @Sundeep_Malik @wlinb

Best