Change timezone on multiple colums, also empty

[RoelandH]

Hi everyone, 

I searched the webs, but I can't find the right solution, and before I start to build a huge Flow, I'd like to see if you have a solution.

Case: 
In a SharePoint list, employees fill in hours for registration, but, they don't work 24/7. Some work only 1 day, some 4 and also some work the weekends, while others do not. They fill in the colums as such:
Monday Start: 25-3-2021 10:00

Monday End: 25-3-2021 15:00

Activities: XXXXX

Tuesday Start: 

Tuesday End:
Activities: 

Wednesday Start: 27-3-2021 18:00

Wednesday End: 28-3-2021 01:00

Activities: XXXX

etc. etc.

The Flow is automated (when an Item is created) and via HTML made into a table and sent via E-Mail

Problem:
The time zone needs to be converted to UTC+1, and the format needs to be : dd-MM-yyyy hh:mm
But, the flow need to handle empty columns too

What did I try already?

When I tried to convert the time zone, it returns as faulty, because the empty columns are not converted, because there is no information.

I tried to run a condition, but similair problems occur here. (and it only has 12 rows, where I need 14)

[tom_riha · Accepted solution]

Hello @RoelandH ,

you can combine 2 expressions:

1. if(...) expression to check if the date is not empty, if it's empty, use 'null' value

if(empty(triggerOutputs()?['body/Date']),null,...)

2. and if it's not empty, use the convertFromUtc(...) expression to convert the date into CET time zone and format it.

if(empty(triggerOutputs()?['body/Date']),null,convertFromUtc(triggerOutputs()?['body/Date'],'Central Europe Standard Time', 'dd-MM-yyyy hh:mm'))

The triggerOutputs... is a dynamic content, you should replace it with your SP column(s).

Here's documentation of the convertFromUtc(...) if you're interested: Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

And the timezone values: Microsoft Time Zone Index Values | Microsoft Docs

[RoelandH]

Hello @tom_riha 

Thank you for your reply and help. 
I tried to replace the dynamic content with an expression.

The code should look like this: 

if(empty(triggerBody()?['Monday_x0020_start']),null,empty)

(with "monday_x0020_start" being the dynamic content)

I tried several options, but they are invalid, or they don't fail when running the flow..

Sorry, not that well known in the world of expressions..... 🙈

Also, since I'm in the EU, should I use semicolon (';') instead of column (',')?

[tom_riha · Accepted solution]

Hello @RoelandH ,

with the expression you shared the evaluation will go as below:

is triggerBody()?['Monday_x0020_start']) empty?
- yes, it's empty: use 'null' value
- no, it's not empty: use 'empty' value

 what you want to do is:

is triggerBody()?['Monday_x0020_start'] empty?
- yes, it's empty: use 'null' value
- no, it's not empty: convert the value from UTC into CET = convertFromUtc(triggerBody()?['Monday_x0020_start'],'Central Europe Standard Time', 'dd-MM-yyyy hh:mm')

Building it into an expression:

if(empty(triggerBody()?['Monday_x0020_start']),null,convertFromUtc(triggerBody()?['Monday_x0020_start'],'Central Europe Standard Time', 'dd-MM-yyyy hh:mm'))

[RoelandH]

Oh wow. It works perfect!! Thank you so much!!!