cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Excel table add date to email help

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?

MS flow diagram.JPG

 

9 REPLIES 9
Dual Super User II
Dual Super User II

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!

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

Hey @Excelrocks 

 

You can check the screenshots below: 

ffer.PNGffer1.PNG

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! 

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

Excel column names.JPG

 

 

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

MS flow diagram 08112019.JPG

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

 

MS flow - date issue.JPG

 

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 🙂 

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'

 

MS flow email body.JPG

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 

 

Compose.JPG

 

 

This is the email body:

email body wrong date format.JPG

 

 

And this is the flow detail:

 

flow output.JPG

 

 

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!

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

Helpful resources

Announcements
PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (7,683)