Unanswered

Create event with the correct date and time from SharePoint List

(0) Share

Report

Posted on by ElleB

Hi,

I'm unable to get both the correct date and time when using "Create an Event." The calendar event uses information from a SharePoint list (populated by an MS Form). The MS form has a date field but the time is a text field. When the activity date is the start/end time, the calendar invite has the correct date but the default time of 12:00 am. When the activity start/end time is used, the appointment date is today's date with the correct time.

How can I get both the correct date and start and end time on the calendar appointment?

SharePoint List

Flow using start/end time - correct time but the default is today's date

Flow using activity date-correct date, the default time

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (1)

v-bofeng-msft on at

Like (0)

Report

Hi @ElleB ,

Because the time displayed in SharePoint is local time (according to SharePoint's time zone settings)

when Flow reads this time value and passes it to flow, this time will be converted to UTC time. For example:

I'm assuming your SharePoint set timezone is UTC+8 and the start time in SharePoint is 2022-04-25T09:00:00 . And then you will get 2022-04-25T01:00:00 in your flow. Because the date offset in the UTC+8 area is 8 hours, the corresponding UTC time needs to be subtracted by 8 hours, so the date we get in Flow is 8 hours less. To make up for the missing 8 hours, we need to add another 8 hours.

Best Regards,

Bof

Was this reply helpful? Yes No
ElleB 14 on at

Like (0)

Report

Hi,
Thanks for the reply. Time zone is not the issue. The event date and time are recorded differently and in different fields. Event date is pulled from MS Forms date picker and event time is a text field. using date in "Create an Event" only produces the date, not the time on the calendar, and using the event time produces the correct time but the incorrect date (the default is today's date since there is no time picker in MS forms.) It looks like I need to combine the date and time variables as in the example linked below but need assistance writing the correct formula. https://powerusers.microsoft.com/t5/Building-Power-Apps/New-variable-that-combines-Date-and-Time-variables/td-p/153544.

Was this reply helpful? Yes No
takolota1 4,974 Moderator on at

Like (1)

Report

@ElleB

You should be able to use the concat expression to combine the correct date with the correct time. Set it to only take the “yyyy-MM-dd” from the correct date and only take “HH:mm” from the correct time.

Was this reply helpful? Yes No
Verified answer

ElleB 14 on at

Like (1)

Report

@takolota @v-bofeng-msft
There is probably a much more elegant solution with fewer steps but here's what worked for me:
1. Remove time from date. (Date is from a date picker and automatically includes a default time)
formatDateTime(variables('EventDate'),'yyyy-MM-dd')

2. Convert Start and EndTime ( text fields) to military time so that the hour is recorded. This solves for the AM/PM problem.
formatDateTime(variables('StartTime'),'HH:mm:ss')

3. Combine (concat) the new new start/end time with the new date.
concat(outputs('Compose'),'T',outputs('Compose_3'))

4. In Create event, use the new "outputs" from the conversions of start and end time as the start and end time.

Was this reply helpful? Yes No