Unanswered

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

(0) Share

Report

Posted on by 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 🙂

Screen Shot 2020-10-26 at 09.34.54.png Screen Shot 2020-10-26 at 09.24.33.png Screen Shot 2020-10-26 at 09.14.10.png

Categories:

Using connectors

I have the same question (0)

All responses (23)

Answers (0)

v-alzhan-msft on at

Like (0)

Report
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.

Was this reply helpful? Yes No
mooler 21 on at

Like (0)

Report
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.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@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

Was this reply helpful? Yes No
mooler 21 on at

Like (0)

Report
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!

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@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

Was this reply helpful? Yes No
mooler 21 on at

Like (0)

Report
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"}}

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
@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....

Was this reply helpful? Yes No
mooler 21 on at

Like (0)

Report
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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@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

Was this reply helpful? Yes No
mooler 21 on at

Like (0)

Report
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"}]

Was this reply helpful? Yes No