Unanswered

Change timezone on multiple colums, also empty

(0) Share

Report

Posted on by 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)

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (2)

Verified answer

tom_riha 10,185 Most Valuable Professional on at

Like (0)

Report
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

Was this reply helpful? Yes No
RoelandH 39 on at

Like (0)

Report
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 (',')?

Was this reply helpful? Yes No
Verified answer

tom_riha 10,185 Most Valuable Professional on at

Like (1)

Report
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'))

Was this reply helpful? Yes No
RoelandH 39 on at

Like (0)

Report

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

Was this reply helpful? Yes No