create calender item

[MobiTowner]

Hello community,

you might know how I can archive the following target :

Having SP list with several colums. This is for holiday approval.
Two colums hold start date ("start") and end date ("end") kind of "date and time" format "date only"
When I create a new holiday request and use "start" and "end" to create a calender item I miss the last day.
Example:
start = 01.01.2018
end = 04.01.2018
creates a calender out of office from 01.01.2018 0:00 until 03.01.2018 0:00 (german date format)

Maybe I have to add 24 hours (addHours) or one day (addDays) but I cannot figure out how to do.
Any help i greatly appreciated.

Thank you.

[faustocapellanj · Accepted solution]

Hi @MobiTowner,

When working with dates and times, all times in Flow are in Universal Time (UTC or GMT), and since your Date and Time column is set to Date Only, your column will have the date of 04.01.2018 plus a default time of 0:00 in your time zone. Flow will adjust the date and time to UTC and will subtract a certain number of hours, which will give you the date and time you are seeing: 03.01.2018 0:00.

There are two things you can do:

1. Add a Date Time - Convert time zone step to your Flow. In it, set your Base time to your End Date, then set your Source time zone to (UTC) Coordinated Universal Time, and finally, set your Destination time zone to your current time zone. Also, you need to select the Format string. Very important.
2. Set your Date and Time column to use both date and time. I know this will be an inconvenience since you don't really require to set a time when taking time off, but it's a workaround.

Honestly, I'd go with #1, even though it might seem inconvenient. I have added a link that explains #1 above in more details. Please let me know if you have any questions.

Working with Dates and Times inside of your flows

Regards,

Fausto Capellan, Jr

[v-xida-msft]

Hi @MobiTowner,

Where do you want to create your calendar event?

Could you please share a screenshot of your flow’s configuration?

Further, do you want to add one day or 24 hours based on end date?

I agree with @faustocapellanj’s thought almost, you could also take an alternative way to achieve your needs. I have made a test on my side and please take a try with the following workaround:

Within Start time field, type the following formula:

formatDateTime(triggerBody()?['Start_x0020_Date'],'yyyy-MM-ddT00:00:00Z')

Within End time field, type the following formula:

formatDateTime(addDays(triggerBody()?['End_x0020_Date'],1),'yyyy-MM-ddT00:00:00Z')

Note: I have made a test to create a Google calendar event on my side.

The flow works successfully as below:

Best regards,

Kris

[MobiTowner]

Hi Fausto and support team,

thanks for providing precious inputs. It took a lot of testing.

I got it working now but only by adding one minute to the "end" date.

If I work like Fausto I can see that the timestamp is calculated as expected (in Flow). But: Creating calender item did not behave that way.
I found out that a timestamp like 03.15.2018 00:00:00 is not valid to create a whole day event at the end of a vacation period (in Exchange).  A date like 03.15.2018 00:01:00 (one minute later) just works fine.

Fausto's test with Google calender did work as he said because Google might start a new day right at 03.15.2018 00:00:00.
 If you find the time, you can have a second check against a Exchange calender. Would be interesting what.
 

Again, thank you very much for helping!

[faustocapellanj]

Hi @MobiTowner,

I'm glad you got it working. Ever since I started working with Flow, I've noticed how the SharePoint date and time columns can be really tricky to work with in Flow due to the UTC format, especially when the column is set to Date Only. Also, can you please mark the post as solved so other people with the same issue can use the solution?

Thanks,

Fausto Capellan, Jr