Converting a SharePoint list date to correct time zone for an Excel spreadsheet

(0) Share

Report

Posted on by roxy1

195

Hi,

I'm running a scheduled flow that creates an worksheet of specific items in a SharePoint List.

The trouble I am having is that the SharePoint list is correct in it's regional timezone (UTC +8) in a column which has the Task Completed date and time (column formated to date and time), however when getting the date results in the spreadsheet it changes to the date to be UTC. I've played around with trying to use an expression to convert the time zone which I've had no luck with:

formatDateTime(convertTimeZone(utcNow(), 'UTC', 'W. Australia Standard Time’), 'dd/MM/yyyy HH:mm:ss')

I have also tried the action convert time zone but I am getting no where and need to ask everyone out there for help!

Screen shot of part of my current flow. It is the select action of 'Date Completed' that currently often changes it to the day before:

Current expression used is:

formatDateTime(item()?['TaskCompleted'],'dd/MM/yyyy')

An example of an item in the SharePoint list with the task completed date and time of (annoyingly in formate of MM/dd/yyyy):

And result when it has been added to the spreadsheet and emailed (date only is required for the results):, it has changed the date to the date before!

Categories:

Building flows

All responses (4)

Answers (1)

Sort by

Amit_Sharma 1,569 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @roxy1
Try With ConvertFromUtc() Function.
convertFromUtc(utcNow(), 'W. Australia Standard Time', 'dd/MM/yyyy HH:mm:ss')

Using one Compose Action Please Confirm In your flow It Convert Perfectly.

Best Regards.
roxy1 195 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Amit_Sharma thanks for your suggestion. This expression works when testing in a compose action.

I need to apply it to change the date of the 'Task Completed' column, for all items. Can I somehow add this in there?
Verified answer

roxy1 195 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Amit_Sharma I've managed to solve my own query!

I used expression
formatDateTime(addhours(item()?['TaskCompleted'],8),'dd/MM/yyyy')

(Where task completed is the column, and 8 is the amount of hours my time zone is ahead of UTC!)
DavyBruny 5 on at

Like
a
(1)

Report
Copy link

Link copied!

Thanks for sharing the addHours function. I used it as the basis for this expression to try to handle Daylight Savings time in EST:
if(and(or(and(equals(substring(utcNow(),5,2),'03'),equals(substring(utcNow(),8,2),'12')),greater(substring(utcNow(),5,2),'03')),or(and(equals(substring(utcNow(),5,2),'11'),equals(substring(utcNow(),8,2),'05')),less(substring(utcNow(),5,2),'11'))),addhours(convertFromUtc(utcNow(),'Eastern Standard Time'),4,'M-d-yyyy h:m tt'),addhours(convertFromUtc(utcNow(),'Eastern Standard Time'),5,'M-d-yyyy h:m tt'))
to add 4 hours when Daylight Savings Time and 5 hours otherwise. It has to be updated each year since Daylight Savings Time changes a few days.