cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Excelrocks
Level: Powered On

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
Dual Super User

Re: Excel table add date to email help

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
Level: Powered On

Re: Excel table add date to email help

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
Dual Super User
Dual Super User

Re: Excel table add date to email help

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! 

Excelrocks
Level: Powered On

Re: Excel table add date to email help

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

Excelrocks
Level: Powered On

Re: Excel table add date to email help

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 🙂 

Excelrocks
Level: Powered On

Re: Excel table add date to email help

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

Excelrocks
Level: Powered On

Re: Excel table add date to email help

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

 

 

Dual Super User
Dual Super User

Re: Excel table add date to email help

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!

Community Support Team
Community Support Team

Re: Excel table add date to email help

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
firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (4,179)