Excel table add date to email help

[Excelrocks]

Hi team

I'm very new to MS flow but reasonably experienced with excel. I love the potential it offers and can see it automating the email process I currently do manually where I emailed people based on data (numbers and dates) contained in an excel file.

I have an excel table being stored on onedrive. And yes this has dates recording events in the past (payment dates).

I have successfully created a flow reading the table but the common 'excel date as numbers' appears in the email body. I have several colums in my excel file and the date column is the problem child 🙂

It is with much embarassment that I have spent today trying to work out what formula to use and where in my flow do I place the formula which fixes the excel date problem. I've worked out that there's nothing I can do at the source (excel file) to fix this is there?

Can I request your help on where in the flow I edit the flow so that the date is corrected? I'm lost at where to place the formula.

And what formula do I use?

[yashag2255]

Hey @Excelrocks 

This is a very known issue with the MS Flow and Excel online connectors. The date/ time datatype columns from excel always return a number instead of the date time string. There are two wasy you can address this: 

1. Select the entire date column from excel and change the datatype to simple plain text. 

2. Refer to a few responses here to get the expression to convert that number to a readable date time string https://powerusers.microsoft.com/t5/Building-Flows/Excel-dates-turned-into-integers/td-p/130364

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[Excelrocks]

Thankyou

I thought the excel connector was discontinued?

If someone could please provide the place in the flow and the correct formula that would be very very awesome

[yashag2255]

Hey @Excelrocks 

You can check the screenshots below: 

The expresison used is: 

addDays('1899-12-30',int(items('Apply_to_each')?['Column4']))

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS! 

[Excelrocks]

Yashag - thankyou for your patience.

OK I have attempted to add the actions as I think your image is depicting.

I'm still getting the same problem in the email - date appears as numbers 😞

Here are the 4 column names in the excel file/table

And here is my attempt to add in what I thought you mean:

[Excelrocks]

OK played around with this for a few more hours and have the following:

The issue is the date in the email body appears as numbers still. And it also appears as above that the date is USA not english based. 

Hope someone can help me some more 🙂 

[Excelrocks]

So below is the body of the email.

At compose 2 there is a date being produced. (see above image)

I have saved the excel data for date as text

It is still being inserted into the body of the email as numbers

I'd like the date to be like '10 October 2018'

[Excelrocks]

OK did some more time on this. 

Removed one of the compose steps and that enabled me to use the output to add to the body of the email.

So I have the date in the email body but the date format is horrible

How do I (and why does MS make this so dam hard) correct the date format in the body of the email to something a human would read?

Eg how do I make it like dd/mm/yyyy or even say 10th October 2018?

The frustration on this is enormous - this was meant to be a straighforward matter but MS have made it anything but.

Hope one of you good peoples can help 

This is the email body:

And this is the flow detail:

[yashag2255]

Hey @Excelrocks 

That is the long date format that you are getting. Now make this small change and you should be good to go: 

formatDateTime(yourcurrentexpression, 'dd/MM/yyyy')

Or

formatDateTime(yourcurrentexpression, 'dd-MMM-yyyy') sample-> 12-Nov-2019

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[v-litu-msft]

Hi @Excelrocks,

Has your problem been solved?
If your problem has been solved, You could go ahead and mark @yashag2255 's post as solved by clicking “Accept as Solution” so that this thread will be marked for other users to easily identify!

Best Regards,
Community Support Team _ Lin Tu