Solved: Re: Adding delays to certain dates from Forms

LordChappers · ‎02-02-2022

I've been doing a lot of searching but I can't find an answer to this specific query.

We have a Form set up to let the IT team know when a member of staff is leaving. The Flow currently takes the info from the form and sends it as an email to the helpdesk to create a ticket.

I'd like for an Outlook calendar meeting to be booked into my colleague's calendar at 09:00, 90 days after the 'Leaving Date' cell of the form. Nothing I've tried (I'm pretty novice at this) has worked (I can get it to make the appointment 90 days from when the form was submitted, but can't figure out how to get it to add 90 days to the date provided on the form).

Any guidance would be greatly appreciated!

(also, is the calendarID '[upn]:\Calendar'?

Svenny · ‎02-02-2022

Ok, excellent!

What you can do is:

1) Click on the date fields within Create Event that you wish to add 90 days to.

2) In the box that pops up, click on the "Expression" tab.

3) In the "Expression" tab, type: addDays. The formula should auto-complete, or show as a suggestion as you start to type.

4) When you have selected the addDays expression, swap to the dynamic content tab and select "Leaving Date" to use it in the first part of the addDays expression.

5) To write the second part of the addDays expression, type a comma, and then enter the number of days you wish to add (90).

6) In the third part of the formula, you can format the date time if necessary.

In order to add 30 minutes to the end time of the calender event, you can also wrap the addDays expression you have written in the End Date field with an addMinutes expression. This will accomplish the same as you have by adding the hours and minutes behind the Leaving Date dynamic content.

The addDays expression will add the time, hour etc. values if you do not format it. Therefore, you need to choose between formatting the date and adding the time yourself, or adding the time using the expressions directly as described above.

Hope this works for you!

View solution in original post

Svenny · ‎02-02-2022

I have not worked a lot with calendars myself in Power Automate, but this sounds like something that should be possible as per your requirements.

Do you get any error messages when you attempt to use the date provided in the form? Is the date from the form available as dynamic content for you to work with?

Have you looked at the addDays formula? As long as the date is formatted correctly, this should add 90 days to the date you recieve in the form:

Example:

Formula used in the compose action:

addDays(triggerBody()['date'], 90)

LordChappers · ‎02-02-2022

Hi Svenny - thanks for the reply!

The date from the form (and all other aspects of it) are available as dynamic content - I have another (working) flow that takes this data and puts it in an email to the ticketing system.

I've not actually managed to get it to create an event at any time - I think I'm getting the calendar ID wrong. I was so focussed on getting it to insert it at the right date that I hadn't looked at that (or sent any test at all - I can't see how to add the formula you've shared as the event start time). I've now done several tests using different naming structures (UPN, displayname, [UPN]:\Calendar, etc) as the CalendarID with a normal time this afternoon to create it and no joy.

LordChappers · ‎02-02-2022

Okay, please disregard my previous reply, It's been a long day! I didn't realise that the calendar ID it was requesting showed me my (logged in account's) calendars, and that the meeting is going to be created in that account no matter what. I've now selected 'calendar' and added my colleague as a required attendee in the advances settings and tested - now working creating events using the leaving date as the date.

Now I just need to figure out how to add the 90 days to the start and end times. I can't figure out how to get what you've posted in your screenshot.

Svenny · ‎02-02-2022

Ok, excellent!

What you can do is:

1) Click on the date fields within Create Event that you wish to add 90 days to.

2) In the box that pops up, click on the "Expression" tab.

3) In the "Expression" tab, type: addDays. The formula should auto-complete, or show as a suggestion as you start to type.

4) When you have selected the addDays expression, swap to the dynamic content tab and select "Leaving Date" to use it in the first part of the addDays expression.

5) To write the second part of the addDays expression, type a comma, and then enter the number of days you wish to add (90).

6) In the third part of the formula, you can format the date time if necessary.

In order to add 30 minutes to the end time of the calender event, you can also wrap the addDays expression you have written in the End Date field with an addMinutes expression. This will accomplish the same as you have by adding the hours and minutes behind the Leaving Date dynamic content.

The addDays expression will add the time, hour etc. values if you do not format it. Therefore, you need to choose between formatting the date and adding the time yourself, or adding the time using the expressions directly as described above.

Hope this works for you!

LordChappers · ‎02-03-2022

That's fantastic, thanks Svenny!

I wasn't aware I could swap between expression and dynamic content for the same formula, this makes sense now!

I'm now able to make the appointment appear in 90 days time (to make it all day I had to make the appointment end date 91 days).

UPDATE: got it working to a specific time now, rather than all day using Svenny's advice and trial and error to get the right syntax: If anyone else comes looking for how to make it work, To make the event 09:00 until 09:30 these were the exact start and end time expressions I used:

Start: addHours(addDays(DYNAMIC CONTENT FOR LEAVING DATE,90),9)

End: addMinutes(addHours(addDays(DYNAMIC CONTENT FOR LEAVING DATE,90),9),30)