cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Super User
Super User

Date Formatting Problem

I have a flow that extracts Data from a SharePoint List and creates new rows in an excel table,  One of the columns is for Date of Birth.

 

To ensure the correct format In that Column I use the expression:

 

formatDateTime(items('Apply_to_each')?['BirthDate'],'dd/MM/yyyy')
 
It works firn for dates where the day is bigger than the month EG: 15/10/2000  but if the date is less that the month it swaps them over.  EG DOB= 10 Jan but teh result I get 01/10/2000 which is the 1st of October
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Date Formatting Problem

My work around.  I had to make the column in Excel a simple Text Column.  I had to put the date into a compose first with teh proper format in a concat().  This worked

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Date Formatting Problem

Hi @leyburn19,

 

What's the formate of the BirthDate stored in SharePoint?

If it is like 10 Jan 2020, it shouldn't have the issue. Please refer to below:

Annotation 2020-07-29 105340.jpgAnnotation 2020-07-29 105339.jpg

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Super User
Super User

Re: Date Formatting Problem

OK This is frustrating.  I have followed the flow step and can see it is putting the date in the correct format into excel,  yet the output excel file changes the dates where the day is below 12 to the reverse format - see image of output  Dates where the day are above 12 are correct

 

How is it possible for excel to actually display two different formats which is set to English (Australian).  I have tried many things in this from even taking out of one drive and putting teh excel file in SharePoint Folder.  I have reset the the Column Format.  Nothing works.  I thought it may be an older excel version so redid the table in a later version.  What is going on?

 

Dateexample.PNG

 

 

 

 

Highlighted
Super User
Super User

Re: Date Formatting Problem

My work around.  I had to make the column in Excel a simple Text Column.  I had to put the date into a compose first with teh proper format in a concat().  This worked

View solution in original post

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

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!

Top Solution Authors
Top Kudoed Authors
Users online (8,265)