cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nicmcd
Frequent Visitor

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

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!

 

workflow date time.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
abm
Super User
Super User

Hi @nicmcd 

 

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

View solution in original post

15 REPLIES 15
abm
Super User
Super User

Hi @nicmcd 

 

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
nicmcd
Frequent Visitor

Thank you @abm !

nicmcd
Frequent Visitor

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?

Hi @nicmcd 

 

Check your column spreadsheet format?



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
nicmcd
Frequent Visitor

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

 

date format settings.JPG

Hi @nicmcd 

 

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

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
nicmcd
Frequent Visitor

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

 

 

Try this

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
nicmcd
Frequent Visitor

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.

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
nicmcd
Frequent Visitor

I wouldn't know how to do that unfortunately!

Create a new cell in Excel type as string.



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

@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?

flow_date.JPG

 

 

 

I am getting this error

flow_date_failed.JPG

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.



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials

@abm 

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

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (4,970)