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

[roxy1]

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!

[Amit_Sharma]

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]

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?

[roxy1 · Accepted solution]

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]

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.