cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brice235i
Level 8

Add Row into a Table is adding incorrect date value

Hi there, have a problem with a travel booking workflow.  A scheduled workflow populates an excel file each night with the new bookings so they can be bulk uploaded.   When someone selects the following day for flight arrival the departure date changes to the next day in Excel .  It is very peculiar and I cannot work out why this would be. Has anyone come across that?  If travel is on the same day it's fine...  so strange.

 
"DepartureFlightDepartureDate": "2020-01-31" (in SharePoint the date is 2020-01-30)
"DepartureFlightDepartureTime": "2200",
"DepartureFlightArrivalDate": "2020-01-31",
"DepartureFlightArrivalTime": "1000",
 
"ReturnFlightDepartureDate": "2020-02-03" (In SharePoint the date is 2020-02-02)
"ReturnFlightDepartureTime": "2000",
"ReturnFlightArrivalDate": "2020-02-03",
"ReturnFlightArrivalTime": "0100",
"ReturnFlightDepartureCity": "CNS",
 
 
10 REPLIES 10
Super User
Super User

Re: Add Row into a Table is adding incorrect date value

SharePoint dates are stored in UTC.

 

You will need to use convert timezone action to convert the time into the required timezone of your excel, i suspect they are different.

Community Support Team
Community Support Team

Re: Add Row into a Table is adding incorrect date value

 

Hi @Brice235i ,

 

This is a common situation because the time zone used in the SharePoint List and the default time zone in MS Flow are inconsistent, causing the date to differ.

 

Please try to use the Add to time action or addDays () function to adjust the date.

245.PNG

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Dual Super User
Dual Super User

Re: Add Row into a Table is adding incorrect date value

Hi @Brice235i 

 

I would rather suggest you to change the sharepoint site regional settings to match the time zone you are in so that you do not have to change the date time fields much often. 

 

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!

Brice235i
Level 8

Re: Add Row into a Table is adding incorrect date value

Capturereg.JPGHi there, the SharePoint site is already in the correct regional setting.  It is not an option to change to UTC.

Thanks anyway.

Brice235i
Level 8

Re: Add Row into a Table is adding incorrect date value

 

 

 

Super User
Super User

Re: Add Row into a Table is adding incorrect date value

hi @Brice235i the previous person does not understand unfortunately that regional settings are "display" settings. They change how the dates are displayed in the browser.

 

The time is still stored in UTC regardless. Where is your excel stored, as onedrive also has its own regional settings, as well. Your best bet is probally to make sure all regional settings are correct and also use convert timezone action if it is just plain text.

Community Support Team
Community Support Team

Re: Add Row into a Table is adding incorrect date value

 

Hi @Brice235i ,

 

Maybe you can directly try to adjust the time using the addDays() function. Considering that Date does not include time, you can directly set the time format to yyyy-MM-dd in the third parameter of the addDays() function.

 

I hope it helps.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Brice235i
Level 8

Re: Add Row into a Table is adding incorrect date value

ok, so thank you.  I didn't use Add Days, I used a convert timezone action and that is working to adjust the timezone.  Unfortunately though, when the value is added to excel, even though I specified dd/MM/yyyy in the convert action, the value appears in Excel as MM/dd/yyyy... also the excel date column is defintely set to Australian region with dd/MM/yyyy as the column format.  Totally confusing.

Super User
Super User

Re: Add Row into a Table is adding incorrect date value

is that when you view it in excel or excel web app?

 

if excel web app, make sure the regional setting is configured correctly.

 

I hate dates especially being in australia with all the US junk!

Brice235i
Level 8

Re: Add Row into a Table is adding incorrect date value

It is a shocker!  What I had to do was convert the excel column to text in order to get DD/MM/YYYY.  Even though I specified in the convert timezone action in Flow...  the Server generates the excel file - probably in the US.  Therefore the date switched to the server's local region.  The other thing I was thinking to do is to add a string variable after the convert timezone action then feed the date text string to the excel date column.  I'm hoping they are happy with the date in a text column though.  I think I'll raise this one with Microsoft.

Helpful resources

Announcements
firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

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!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (10,701)