cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Date value being changed when a SharePoint list item is sent for approval via Flow

Hi guys

 

I have a bit of a weird issue with Flow and a Sharepoint list. 

 

When entering items into a custom list I have created, there are 2 columns that take date values. If I set these to my required value and save the entry, everything behaves as expected and the list entry is added with the correct dates.

 

I've created a Flow process which sends an approval email out to the user based on another column inside this list. As soon as the Flow begins and sends out the approval email, both date values in the list entry will change to be a single day before what had previously been entered and saved. 

 

I've checked all regional settings on the site and on the user I'm testing with. Both are set to the same timezone (Auckland, New Zealand, UTC+12), however the issue keeps occuring. 

 

Does Flow use it's own regional settings which is pushing the date out by a few hours?

 

Any ideas?

 

Thanks.

9 REPLIES 9
Highlighted
New Member

Re: Date value being changed when a SharePoint list item is sent for approval via Flow

Hi all

 

Still looking for some assistance with this. Anyone have any ideas?

Highlighted
New Member

Re: Date value being changed when a SharePoint list item is sent for approval via Flow

Hi all

 

I have recevied a response from Microsoft directly regarding this. 

 

For anyone else who comes across this issue, here is the response/reasoning:

 

"Hello

 

I verified and reviewed your flow.

This is a known issue that flow reads dates in UTC time format. And therefore, it updates the date as 1 day past.

 

In order to come over the issue, you can either change Sharepoint Regional settings to show UTC time or update the dates using the formula as showed in the snapshot.

addminutes(<Trigger>,0,'MM/dd/yyyy')

 

 unnamed.jpg

 

Please let me know if it works for you."

 

Thanks.

Highlighted
Advocate I
Advocate I

Re: Date value being changed when a SharePoint list item is sent for approval via Flow

What can I do to display the dates properly in the approval email?

 

The date values entered in Sharepoint list is included in the approval mail triggered by new item creation in Sharepoint.

 

Any idea? Currently if one enters 03/31/2017, in the approval email it is displayed as 03/30/2017.

 

Highlighted
Regular Visitor

Re: Date value being changed when a SharePoint list item is sent for approval via Flow

Hi @bvanessen,

 

Did you manage to get this to work for you? Depending on the parameters I put in, it either does nothing or I get really unexpected results (ie date going back months, instead of forward one day).

I would love to hear if you've managed to figure out how to get around this.

 

Cheers

Highlighted
Frequent Visitor

Re: Date value being changed when a SharePoint list item is sent for approval via Flow

I have the same problem and had no success with the 'adddays' solution.

 

My workaround is to create a calculated columns in Sharepoint, where you can use good old Excel like functions to add a day. I've found it also helps to convert them text before passing them onto flow so that you have some control of the format displayed in the email using something like:

=text(<date column>+1,"dd/mm/yyyy")

 

You can then create a second column which is formatted in the ISO std way to pass back to sharepoint to overwrite the original date column to account for Flow's change of the date

=text(<date column>+1,"yyyy-mm-dd") 

 

I make these calculated columns hidden to avoid confusion for the users.

Highlighted
Frequent Visitor

Re: Date value being changed when a SharePoint list item is sent for approval via Flow

Similar and I think related, but I'm posting in the hope it helps someone.

Microsoft forms form that staff fill in with a date they'll be onsite,

PowerAutomate takes the form date, gets user, department and manager information, and populates a Sharepoint Online List in a site collection set to EN-NZ and the New Zealand timezone.

In the list everything looks fine, the date entered in the form is the date in the date/time column in the list.

BUT

When a list view is created that groups by the Date column the date displayed in the GroupBy heading (with total) is the day prior to the date the group by column is based on !?##

e.g.

I put in the form saying I'll be onsite 29 May 2020

A list row is inserted by PowerAutomate with the value of 29/05/2020 in my DateOnSite date/time column

When I create a view grouped by DateOnSite column my row appears under a grouping labeled >DateOnSite: 28/05/2020 (3)  where 3 is the total number of rows with 29/05/2020 as the DateOnSite column.

 

And the solution : thanks to this thread I tried a calculated text column of =text([DateOnSite]+1,"dd/mm/yyyy") , which when I grouped by the new column put my 29/05/2020 record under a grouping of 31/05/2020.

So ....

I now have a date/time calculated column of =[DateOnSite] called DateOnSiteNZ

When I group by DateOnSiteNZ, my 29/05/2020 record shows up under a grouping of >DateOnSiteNZ: 29/05/2020 (3)


What gives Microsoft ? It seems like the values are stored in an Excel type way (some id number for a date) then displayed in a particular format BUT that there is a bug in the groupby function which doesn't obey the Locale or Language settings of the site the list is in !!

How old is this product ? How many countries is it used in ?? Come on, tons of MS staff come from outside the US ?? Really ?

All the best, grumpy in the South Pacific.

 

Highlighted
Frequent Visitor

Re: Date value being changed when a SharePoint list item is sent for approval via Flow

I've just had a call with Microsoft and we can't recreate the issue.

So it seems that my issue where a list group by function was reporting the day prior to the day actually in the list column, was something that existed and was then fixed.

 

Thanks to Syed for calling and going through this issue with me.

Highlighted
Super User
Super User

Re: Date value being changed when a SharePoint list item is sent for approval via Flow

@DorjeM 

 

Use Convert Time Zone(either action/expression) and then FormatDateTime Expression.

I faced this issue to send meeting invites.

 

Thanks

Hardesh

Highlighted
Frequent Visitor

Re: Date value being changed when a SharePoint list item is sent for approval via Flow

Many thanks @Hardesh15 ,

I tired to replicate the issue with a Microsoft Support person just yesterday and we couldn't replicate it in his test environment or in my tenant.

But I'll keep your suggestion in mind, we're just getting into using PowerAutomate and living in NZ I know the timezone issue will come up again and again 🙂

 

Dorje

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!

Users online (6,832)