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

[bvanessen]

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.

[bvanessen]

Hi all

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

[bvanessen]

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')

 

Please let me know if it works for you."

Thanks.

[arbangara99]

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.

[Anonymous]

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

[Toucan]

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.

[DorjeM]

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.

[DorjeM]

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.

[Hardesh15]

@DorjeM 

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

I faced this issue to send meeting invites.

Thanks

Hardesh

[DorjeM]

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

[Ianw3]

I can't believe I am getting this even after all this time.  There are years of comments online of people experiencing the same thing.  Each time I open a SharePoint list entry, the dates go back a day.  If I make changes to the list entry that I want to save, if I don't remember to reset the dates to the correct values, over time they just creep further and further back each time I edit and save a list entry. What a pain.