Excel column date to outlook calendar event (how to format the date)

[mooler]

Hello!
I've scanned the community for a couple of days and been trying to solve this but comes up short.
I've got a excelfile with tables where one of the columns is a date formated one. Via a flow I'd like this date to transfer to a outlook calendar event. 
This to make my teacher be able to just fill in the excel file and automatically it would create a event.
So far the event is created on the right day but I can't find a way to get the time of day to transfer from excel to calendar.
This is what I got and this gives me a event in the calendar but obvioulsy without specific time. The formatDateTime is:

formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['endDate']),-2)), 'yyyy-MM-dd')
But as soon as I try to edit the date format of the excel date column to include time I get an error that the integer is invalid. So I tried to replace int with float but to no avail.

I could use a hint how to proceed with this to get it working properly. New to PA so it's a bit tedious and slow 🙂 

[v-alzhan-msft]

Hi @mooler ,

Please take a try with expression below:

formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['endDate']),-2)), 'yyyy-MM-ddTHH:mm:ss')

Best regards,

Alice       

Community Support Team _ Alice Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mooler]

Yes I have tried that solution before but it gives me the followin error. 

This is the expresson for the startDate:

formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['startDate']),-2)), 'yyyy-MM-ddTHH:mm:ss')

And this is the expression for the endDate:

@{formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['startDate']),-2)), 'yyyy-MM-ddTHH:mm:ss')}

The format in excel for the date is still the same as before, could it be that I need to edit the columnformatting in excel? As I said I'm fairly new to this kind of connectors so I do not see the problem as of now.

[kdavis91]

@mooler ,

Can you provide the Output from one of the failed runs to see what format Power Automate was receiving the date from Excel?

Normally, dates are stored as numbers for an even day, and with decimals if there is a time component. Since you tried using float instead of int, I assume you know this, but I figured I would throw it in anyway.

If the error is that the Int() function isn't getting an integer, we need to know what it IS getting.

also, what error do you get when using float()?

thanks,

Kyle

[mooler]

From a failed run I don't get any output the error message just the "invalid template" error that I posted. Same if I replace int with a float.

If I use:

formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['endDate']),-2)), 'yyyy-MM-dd')

and format the excel column without a specific time (just date):

 
Then I get integer output as:

The flow then runs and creates a event in Outlook but the event is created at 00.00 as the output states.
Then if I just edit the excel column to add a time to the date as below:

I don't alter anything else, when I then run the flow I get:

If i try all the date formats in the excel colum I get that same error.
If I edit the "inte" to a "float" i get the invalidtemplate error.

So how the heck do I get the output accepted with the time as well? It probably is some simple thing I'm missing but after all the trial and errors I've tried I'm probably blid to what is missing!

[kdavis91]

@mooler ,

have you tried just removing the int() part of the expression altogether?

since excel stores the DateTime as a number anyway, forcing the type conversion may be unneccessary... 

I'm not sure this is the problem...i'm just troubleshooting at this point.

Kyle

[mooler]

I'm greatful for any input!

Unfortunately that is not a valid expression. 

formatDateTime(addDays('1900-01-01', add('startDate'),-2)), 'yyyy-MM-dd')

And this give the same invalid template error as before: 

formatDateTime(addDays('1900-01-01', 'startDate',-2), 'yyyy-MM-dd')

 If I use just the dynamic content variables i get a "bad request" error. This was my first choice before diving in to the expressions part of this. 
Like this:

The input:

and output error:

{"statusCode":400,"headers":{"Pragma":"no-cache","x-ms-request-id":"9a5710cf-b99e-49b1-95ff-f678985c58ac","Strict-Transport-Security":"max-age=31536000; includeSubDomains","X-Content-Type-Options":"nosniff","X-Frame-Options":"DENY","Cache-Control":"no-store, no-cache","Set-Cookie":"ARRAffinity=fa9050a86380ecce883d8ff2e805dd22d58cc29332b6761a0fc08c7d82b3fa74;Path=/;HttpOnly;Secure;Domain=office365-ne.azconn-ne-01.p.azurewebsites.net,ARRAffinitySameSite=fa9050a86380ecce883d8ff2e805dd22d58cc29332b6761a0fc08c7d82b3fa74;Path=/;HttpOnly;SameSite=None;Secure;Domain=office365-ne.azconn-ne-01.p.azurewebsites.net","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"false","Date":"Mon, 26 Oct 2020 18:42:49 GMT","Content-Length":"289","Content-Type":"application/json","Expires":"-1"},"body":{"status":400,"message":"String was not recognized as a valid DateTime.\r\nclientRequestId: 9a5710cf-b99e-49b1-95ff-f678985c58ac","error":{"message":"String was not recognized as a valid DateTime."},"source":"office365-ne.azconn-ne-01.p.azurewebsites.net"}}

[kdavis91]

@mooler ,

I was thinking more something like this, only removing the int() portion of your above expression and leaving everything else (maybe you already tried this):

formatDateTime(addDays('1900-01-01', add(items('Apply_to_each')?['endDate'],-2)), 'yyyy-MM-dd')

If that doesn't work, I would suggest you put a compose action block in your flow somewhere before the error and pass in one of our dates from Excel to see what its output is....

[mooler]

Yes, I did try that one and it gives a "invalid template" error. 
If I put a compose between the "get row" and the "apply to each" I get the following.
Input:

va[{"@odata.etag":"","ItemInternalId":"ed853fa9-c30e-4a12-a575-9e1d9a91d452","eventName":"Historia","startDate":"43831.4166666667","endDate":"43831.4583333333","arskurs":"8A","rooms":"13"}]

Output:

va[{"@odata.etag":"","ItemInternalId":"b2e8a8ed-103b-4296-a95e-c7d59c9b7a21","eventName":"Historia","startDate":"43831.4166666667","endDate":"43831.4583333333","arskurs":"8A","rooms":"13"}]

 It seems fine to me, but then again I do not know what format outlook demands for it to work.

This whole idea originated from a article where the user just used dynamic content variables and it worked. https://www.ariclevin.com/Flow/Post/outlook-events-onedrive-excel 

[kdavis91]

@mooler ,

For some reason, I cannot see the message you posted in between my last two responses, but I do remember you mentioning that you originally tried using the dynamic content and it didn't work, but I cannot remember what you said regarding why it didn't work. could you please provide that information again?

It is odd since, as you said, the article is just using the dynamic content.

Unfortunately, changing the date format in excel will not change how the date/time is stored in Excel. It will still be 4XXXX.XXXXX

thanks,

Kyle

[mooler]

Strange it must not have been posted.
Anyway, yes I did try the dynamic content route first as this article explained https://www.ariclevin.com/Flow/Post/outlook-events-onedrive-excel 

This is the dynamic content way.

The output error sais wrong date format:

{"statusCode":400,"headers":{"Pragma":"no-cache","x-ms-request-id":"4934d0b8-198f-44aa-8fc4-637bfde4bf19","Strict-Transport-Security":"max-age=31536000; includeSubDomains","X-Content-Type-Options":"nosniff","X-Frame-Options":"DENY","Cache-Control":"no-store, no-cache","Set-Cookie":"ARRAffinity=2dec40ee271a11b22b991f97343df846ec15685ecd916e17e0cdc237e9bff764;Path=/;HttpOnly;Secure;Domain=office365-ne.azconn-ne-01.p.azurewebsites.net,ARRAffinitySameSite=2dec40ee271a11b22b991f97343df846ec15685ecd916e17e0cdc237e9bff764;Path=/;HttpOnly;SameSite=None;Secure;Domain=office365-ne.azconn-ne-01.p.azurewebsites.net","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"true","Date":"Mon, 26 Oct 2020 19:46:59 GMT","Content-Length":"289","Content-Type":"application/json","Expires":"-1"},"body":{"status":400,"message":"String was not recognized as a valid DateTime.\r\nclientRequestId: 4934d0b8-198f-44aa-8fc4-637bfde4bf19","error":{"message":"String was not recognized as a valid DateTime."},"source":"office365-ne.azconn-ne-01.p.azurewebsites.net"}}

The input and output in a compose is:
Input:

va[{"@odata.etag":"","ItemInternalId":"86df82b7-140c-48f9-8308-f4f66f070ac2","eventName":"Historia","startDate":"43831","endDate":"43831","arskurs":"8A","rooms":"13"}]

 Output:

va[{"@odata.etag":"","ItemInternalId":"86df82b7-140c-48f9-8308-f4f66f070ac2","eventName":"Historia","startDate":"43831","endDate":"43831","arskurs":"8A","rooms":"13"}]

[kdavis91]

@mooler ,

I'm sorry, but I'm at a loss...

this is past my area of experience...

@v-alzhan-msft , do you have any suggestions?

thanks,

Kyle

[kdavis91]

@mooler ,

the only other thing i can think of, is putting the date from excel into a compose or a variable, and then trying to reference that in your formatDateTime() function...

[mooler]

I'm just as lost, the integer generates fine but the create event connector to Outlook doesn't accept it. Go figures 😄

[kdavis91]

This is probably the point where we need to ask someone like  @Jcook, @Pstork1 , or @ScottShearer 

[Pstork1]

You are on the right track, but the CreateEventv4 action requires that the start and end times be in ISO 8601 format and they must include both Date and Time.  Try using the following codes.

formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['startDate']),-2)), 'o')

formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['endDate']),-2)), 'o')

The 'o' specifies that the output is in ISO 8601 time/date format. 

[mooler]

Ok! Unfortunately I don't get a success using that expression. 
I did put a compose between to get the input and output. This is what is shown.

Input:
[
  {
    "@odata.etag": "",
    "ItemInternalId": "5dce440f-b11a-4f09-b336-bb094babcd5e",
    "eventName": "Historia",
    "startDate": "27/10/2020  10:00:00 AM",
    "endDate": "27/10/2020  11:00:00 AM",
    "arskurs": "8A",
    "rooms": "13"
  }
]

Output:
[
  {
    "@odata.etag": "",
    "ItemInternalId": "5dce440f-b11a-4f09-b336-bb094babcd5e",
    "eventName": "Historia",
    "startDate": "27/10/2020  10:00:00 AM",
    "endDate": "27/10/2020  11:00:00 AM",
    "arskurs": "8A",
    "rooms": "13"
  }
]

But the error is this:

And this is where I wonder if this could be due to the language settings in excel. My excel is in swedish but PA is in english (I've tried to force all apps to english but word en excel just won't change language).
INT is apparently HELTAL in swedish excel. Could this be the cause? I'll be damned if I know how to get excel to be english though 😄

*edit* did manage to get english as global language and checked that the INT variable was the prefered language in excel. But still get the same error.

[Pstork1]

I took a closer look at what you are doing.  The Format isn't your problem.  Its this part of the formula

int(items('Apply_to_each')?['startDate']),-2)

The startDate is a DateTime value and you can't use int() with that in Power Automate.  Are you trying to adjust the time from the Excel Spreadsheet by subtracting two hours?  If so, then use this in place of the int()

addHours(items('Apply_to_each')?['startDate'],-2)

[mooler]

Thanks for trying to help!
Unfortunately this did not work, I'm so far into this I'm loosing sight over what has worked and what doesn't 😄

If I try your example:

Just adding:
addHours(items('Apply_to_each')?['startDate'],-2)
or trying it like this:
formatDateTime(addDays('1900-01-01', addHours(items('Apply_to_each')?['startDate'],-2)), 'o')

I get:
 

So then I'm back with the ISO problem of the date value. I could use a fresh start to all this all I want to do is get a row from excel -> create a event in outlook. Next step is to figure out how a event is removed or edited if a row is edited or removed.
Any help is appreciated! 

[Pstork1]

What exactly are you trying to do with the startdate?  Why are you both adding hours and adding days?  If you can explain what you are attempting I can try to help.  But you are now getting a different error so we are definitely working on the section of the formula that is the problem.