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.
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.
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.
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!
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.
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.
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.
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!
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.
Features releasing from October 2020 through March 2021
We are excited to announce the launch of Power Virtual Agents Community. Check it out now!
We've updated and improved the layout and uploading format of the Power Automate Cookbook!
Fill out a quick form to claim your user group badge now!