cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MobiTowner
Level: Powered On

create calender item

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: create calender item

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


Fausto Capellan, Jr


Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!

Proud to be a Flownaut!


View solution in original post

4 REPLIES 4
Super User
Super User

Re: create calender item

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


Fausto Capellan, Jr


Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!

Proud to be a Flownaut!


View solution in original post

Community Support Team
Community Support Team

Re: create calender item

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:18.JPG

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:19.JPG

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
MobiTowner
Level: Powered On

Re: create calender item

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!

Super User
Super User

Re: create calender item

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


Fausto Capellan, Jr


Did I answer your question? Mark my post as a solution! Did my response help? Please give it a thumbs up!

Proud to be a Flownaut!


Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,285)