Exporting calendar data to excel and time zone not converting + last day of the month trigger

[Corinaas]

I'm trying to create an automated flow to export calendar data on the last day of the month for the last two weeks of days and populate an excel document. I'm having trouble with both how to set up the Switch control as well as convert to local time zone from UTC. 

For the EOM trigger, I have the following:

Establish start of month and start of month +1 day and if they are the same then terminate but if they are different (January vs. February) then proceed with flow. For some reason, it's still running the flow. 

Secondly, for the convert time zone function, I cannot seem to figure out where and/or how to add it. I have a compose date format field, but when I try to add the "convertfromUtc" function it's not working  -- formatDateTime(items('Apply_to_each')?['start'], 'MM-dd-yyyy' -- how if I should add here?

I've also tried adding the "convert time zone" module in various spots using all variables - the "outputs", "Compose - Date Format", "Start Time"/ "End Time" and all run but the dates/times in the destination file are still UTC. I also separate to per field to convert. No combination appears to work. 

[ekarim2020]

(1) Power Automate has a startOfMonth() function. 

startOfMonth(utcNow())
2022-01-01T00:00:00.0000000Z

startOfMonth(utcNow(),'yyyy-MM-dd')
2022-01-01

(2) Here is an interesting post for on how to get the Last day of current month or previous month: Power Automate: Useful Date Expressions 

(3) To convert from UTC to your local time-zone, you can use the convertFromUtcNow() function:

convertFromUtc(utcNow(),'AUS Eastern Standard Time')
2022-01-24T06:42:38.8195773

convertFromUtc(utcNow(),'AUS Eastern Standard Time','yyyy-MM-dd')
2022-01-24

The different destination time zones are listed here: Time Zones

(4) When comparing time dates you should compare dates without the time component (eg. 2021-01-23 and 2021-01-23T08:00:00.0000 are different date time values).

Try modifying the flow with the above functions and let us know how you get on.

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[Corinaas]

Hi Ellis, I was able to get the end of month issue solved - thank you. But I am still blocked on the time conversion. Does it matter where in the flow it is added? I've tried in various locations and my results are still in the UTC time zone. 

The "Compose - Convert Time Zone1" is - 

convertFromUtc(utcNow(),'Pacific Standard Time')

The "Compose - Convert Time Zone2" is - -

onvertFromUtc(utcNow(),'Pacific Standard Time','MM-dd-yyyy')

I've further tried adding the full time string (with hours, min, seconds) after the "add row into excel" section and none are working. I'm thinking where is important then I can map to the same date/time format. 

[ekarim2020]

May be the following example will help:

(Don't try to use these dummy credit card numbers!😆)

This is the Excel file:

The Date formats for the Excel columns are:

This is the sample runtime output:

The example shows UK Date column is calculated as 01/24/2022 by the convertFromUtc(utcNow(),'Pacific Standard Time','MM/dd/yyyy') function (which isn't the UK date format). Then note the date format in Excel for UK Date column - as see how it is now formatted as 24/01/2022 (dd-MM-yyyy).

I think you will need to show me the full details of the flow actions and expressions used in each step, including in the Add a row action. 

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.