Date format - outlook event date adding to a row in excel

[nicmcd]

I'm looking to change the date format of a flow that takes start time and end time of an outlook calendar event and adds into a table in an excel spreadsheet.

This currently imports in this format 2020-02-07T16:00:00. I need this in just a date format like dd/mm/yyyy. I know I need a formatdatetime expression but not sure what this would be.  Below is how far I am. Someone suggested "formatDateTime(triggerBody()?['DateTimeReceived'],'MM/dd/yyyy')" on a similar issue but this was for an email recieved trigger and not a event created. Can anyone help? Thanks!

[abm · Accepted solution]

Hi @nicmcd 

Try formatDateTime(triggerBody()?['start'],'dd/MM/yyyy') & formatDateTime(triggerBody()?['end'],'dd/MM/yyyy')

[nicmcd]

Thank you @abm !

[nicmcd]

Hi there,

Although the formula below is working it will not put the date format as dd/MM/yyyy.

formatDateTime(triggerBody()?['start'], 'dd/MM/yyyy')

I've checked the spreadsheet and it is in this format in the settings. The flow also accepts in the test the format of dd/MM/yyyy but when I check the spreadsheet is has reverted to MM/dd/yyyy.

Any ideas?

[abm]

Hi @nicmcd 

Check your column spreadsheet format?

[nicmcd]

Checked that. It's in the format dd/MM/yyyy. Strange one.

[abm]

Hi @nicmcd 

Change the current format to something else and check the results.

Thanks

[nicmcd]

Sadly still the same issue. When I change the format it still mistakes the month for the date. ☹️

[abm]

Try this

https://www.youtube.com/watch?v=ngAE0dME9RA

[nicmcd]

Tried this and still no luck. dd/MM/yyyy is still importing into the spreadsheet as MM/dd/yyyy. I've tested this a few times and the issue seems to present when the dd has a zero in front so any dates 01-09.

[abm]

Could you try mapping this cell to a string type instead of date? 

[nicmcd]

I wouldn't know how to do that unfortunately!

[abm]

Create a new cell in Excel type as string.

[TannaC]

@abm 

I am having a similar issue with the date. I am trying to grab the outlook calendar once a month and write each event to a row in Excel, instead of when the event is created. I am getting an error message though and wondering if there is something I need to change in formatDateTime(triggerBody()?['end'],'dd/MM/yyyy' to make it match what I am trying to do?

I am getting this error

[abm]

Hi @TannaC 

You are trying to format the trigger end body which is null hence you getting this error. Make sure you have the value for the end date.

[TannaC]

@abm 

Can you give me an example of what it should look like if done correctly?