Reading Date/Time string and converting to UTC

[Facilities_AV]

Hi Folks

Here is a little puzzle: I'm working with .csv data from a 3rd party that has an oddly formatted date, in the original .csv and in my final parsed json array (shout out to @DamoBird365 !) it looks like this:

"April     29, 2021 16:30",

...and what I really want to do is format the time and present it as 'hh:mm tt". BUT I can't figure out how to get my Flow to read the original as UTC so I can apply formatting.

I know there is a way, because excel is doing it automatically! When I open my .csv with Excel, I see the data in a standard UTC format:

So, dear colleagues, how can I get my flow to "read" a date written "month     dd, yyyy hh:mm" so I can format it using UTC rules?

Thanks very much in advance for your help!

Eric D - ACT Facilities AV

[v-LilyW-msft · Accepted solution]

Hi @Facilities_AV 

Thank you for posting.

If you want to convert the date to UTC format in Flow, you can use covertToUtc function to achieve. If there is any misunderstanding, please let me know.

Expression: convertToUtc(outputs('Compose'),'Morocco Standard Time','M/dd/yyyy HH:mm tt')

After flow runs, will get the date as bellow:

For more info about functions of Power Automate, you can refer to this document:

Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

For time zone names, Please refer to: 

Microsoft Windows Default Time Zones

Hope the content above may help you.

Best Regards

Lily

[Facilities_AV]

Hi Lily - thanks for the response!

I'm still a bit stuck - but maybe you can help me out?

As I'm building my output table, as before, if I put this in the "value" field, 

item()?['Booking End Date/Time (UTC)'], 

I get "April 29, 2021 16:30"

Then I tried "converttoUTC" with just a raw string as the input, and sure enough Flow read it as "time."

convertToUtc('April 29, 2021 16:30','Greenwich Standard Time','M/dd/yyyy HH:mm tt')

gave me an output of "4/29/2021 16:30 PM" (YAY that's what I want!)

BUT when I try to embed the "item()?" string in there like this:

convertToUtc(item()?['Booking End Date/Time (UTC)'],'Greenwich Standard Time','M/dd/yyyy HH:mm tt')

Then Flow fails! For this reason:

The template language function 'convertToUtc' expects its first parameter to be a string that contains the time. The provided value is of type 'Null'.

I have a feeling I'm making a very beginner mistake with the syntax - How can I correctly present the string to Flow so it is recognized as a time string to be converted?

Any clues?

Thanks very much!

Eric D - ACT Facilities AV

[v-LilyW-msft]

Hi @Facilities_AV 

Is your convertToUtc function put in a loop? When using the item(), please use it in a loop.

I tested it and it was successful.

This is the list I used for testing.

Below is my test flow details:

Expression: item()?['time']

Exoression: convertToUtc(item()?['time'],'Morocco Standard Time','M/dd/yyyy HH:mm tt')

The test result as below:

Hope the content above may help you.

Best Regards

Lily

[Facilities_AV]

Hi again Lily - as it turns out, your original response gave me the key to solve the problem. The "null" result I was getting was the result of my original test .csv having an additional return-line inserted into it accidentally - I assume it happened when I opened the file in OneDrive. As I tried your follow-up suggestion (putting it in a loop) unsuccessfully, I realized that the problem was actually my data. Anyway, once I un-corrupted my data, your suggestion led me to a solution that worked, and I back-tracked to my original solution, where your original suggestion did the trick perfectly!

Thanks so much for your support!

Eric d - ACT Facilities AV