cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Converting time zones - Sharepoint List to Flow

Good day. 🙂 I have been struggling trying to figure out the right way to convert time zones for a flow. I have a SharePoint list for new hires for HR. HR staff adds a new hire with their respective start date (no time just MM/DD/YY). I then have a flow that populates an email with the new hire information. I have converted the time zone from UTC to my regional time zone (Eastern UTC -5) as described in MS instructions. However, when the time is converted it is off by a few hours and sends the email with the incorrect date.

 

The date also changes depending on when the HR staff submits the form. EDITED TO ADD: Sreenshot of SP list entry:

dateentered.PNG

 

Sometimes it makes it a day before or after. The time of the SharePoint site is set to my region as well.

 

What could be wrong? HR has stopped using the form as it is creating issues for our whole company. 😞 

 

Flow-Fail.PNG

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Anonymous,

 

Please refer to screenshot below to create the flow:

Capture.PNG

 

The expression in the "Base Time" field as below:

addHours(triggerBody()?['StartDate'],5)

The flow would run successfully as below:

Capture.PNG

 

Best regards,

Alice

View solution in original post

12 REPLIES 12
WDRC
Helper V
Helper V

@Anonymous

 

Try using a compose action like:

 

convertTimeZone(utcnow(),'UTC','W. Europe Standard Time','dd MMMM yyyy')
 
It worked for me in similar situatiion that you seem to have.
 
Regards,
 
Andrew
Anonymous
Not applicable

Thank you. Not there yet... That worked for the entry added in the morning hours (around 8 am EST) but not for the one added in the pm hours (around 5 pm EST). The email for the afternoon came out with the date after the correct state date. Math anyone? 😕 

TIA! Woman Happy

Hi @Anonymous,

 

Please refer to screenshot below to create the flow:

Capture.PNG

 

The expression in the "Base Time" field as below:

addHours(triggerBody()?['StartDate'],5)

The flow would run successfully as below:

Capture.PNG

 

Best regards,

Alice

View solution in original post

Anonymous
Not applicable

I think this may be the answer! I will run trials today to make sure but... Why would convert time zone work then? Inquiring minds would like to learn. 🙂 Thanks so much!

Hi @Anonymous,

 

Your local time (in sharepoint) is 5 hours later than the utc time (in flow), and flow would always use utc time, so whenever you get the time from sharepoint to flow, the time would always 5 hours later.

 

So you could add the time difference (5 hours) to the sharepoint time as me in the flow.

 

Best regards,

Alice

@v-yuazh-msft , hello.  I have the same problem and it does not appear to work.

 

I used the exact formula you posted and got, I believe, the exact result you got:
Screen Shot 2020-11-26 at 12.06.36 PM.png

Then in the post to sharepoint calendar, I got these results:
Screen Shot 2020-11-26 at 12.09.53 PM.png

 

In the Calendar, it shows the UTC and essentially is ignoring the time zone conversion and the offset in your suggestion.  I've checked my global settings and my SharePoint world is set to PST for the time zone.

 

The formula I used is this as copied from what you showed.  This is only the startdate.  I'm testing one thing at a time to try and fix this problem:

Screen Shot 2020-11-26 at 12.13.56 PM.png

Perhaps @Expiscornovus or @Pstork1 may have thoughts on this.  

Expiscornovus
Super User II
Super User II

Hi @RickSegal

 

Can you try and use the Universal sortable date/time pattern instead? It looks like the Calendar is expecting that format.

formatdatetime_u.png

Hi @Expiscornovus ,
Nope.  The calendar still has it showing a day earlier.  Really weird.

Expiscornovus
Super User II
Super User II

Hi @RickSegal,

 

I see you are adding 8 hours and converting from UTC-11 to UTC-08. 

 

Can you as a test just use a formatDateTime function in a compose action instead of that Convert Time Zone action above. Put this expression in your compose action and try to put that in either the start or end time of your calendar.

 

 

formatDateTime(addHours(triggerBody()?['StartDate'],8), 'u')

 

 

 

@Expiscornovus , hello.

 

I tried it and it didn't work.  The formula used was:

formatDateTime(addHours(triggerBody()?['StartDate'],8),'u')
The input output are shown in the pictures.
You can see the outputs from the create item command.
The yes side of the flow is also shown in the pictures.
I'm still ending up with trying to start on the 18th and it shows up as the 17th.
thx

Screen Shot 2020-11-26 at 6.11.42 PM.jpgScreen Shot 2020-11-26 at 6.12.17 PM.jpgScreen Shot 2020-11-26 at 6.15.29 PM.jpg

@Expiscornovus, also to answer your original question.  I'm doing this convert because I need to have the vacation calendar to be PST (here on the west coast of Canada).  The inbound to the flow shows up as UTC-11 which is not PST, hence the conversion.

@Expiscornovus , we have lift-off!

Always the blindingly obvious, eh?  When I read your comment about getting rid of the convert and just do the formula you suggested, well, I got confused and forgot basic programing.  By making a condition with the formula and then writing the dates on the SharePoint list, I essentially overwrote the formula.  In other words, I need to pair this down to two actions as seen here:

Screen Shot 2020-11-26 at 10.33.39 PM.jpg

 Then, inside the create item action, the formula goes into the start/end date locations like this:

Screen Shot 2020-11-26 at 10.35.09 PM.jpg

 When you do that, you get the expected time adjustment and the calendar entries are correct.  One important weird thing I have not been able to figure out is why the time offset needed to be different for the start and end date.  The "8" for the startdate worked fine.  It didn't for the enddate so I upped it to 10 and that offset worked.  

 

In any event, thank you for pushing me in the right direction, sir! Truly appreciated.

 

 

 

 

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Users online (21,205)