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

Formatdatetime is occasionnaly confusing month and date

Hi,

I have a flow designed to fill an excel file when a record is modified in Salesforce.

I need to fill date details in my excel file and i face a problem with fomatTimeDate function.

 

When there is no possible confusion to sort out what is month and what is day in the Salesforce date the fonction works fine. But when a confusion is possible then the function is confusing month and day. Here is my examples for better understanding.

 

When is Salesforce my date is 

"Certification_Date__c":"2021-02-06T09:00:00Z"

Actually : Feb, 6th, 2021 9:00 am 

 

In my flow I have this formula in my "update a row" (excel online):

if(equals(coalesce(triggerOutputs()?['body/Certification_Date__c'],'NULL'),'NULL'),'',formatdatetime(addHours(triggerOutputs()?['body/Certification_Date__c'],1,'yyyy-MM-ddTHH:mm:ssZ'),'dd/MM/yyyy HH:mm'))
 
and in excel the date is wrong reversing month and day  :  2/6/21 10:00 (June 2nd, 2021 !!!).
 
But when is Salesforce my date is :
"Certification_Date__c":"2021-02-13T20:00:00Z"

Actually : Feb, 13th, 2021 20:00 pm (with a 13 there is no confusion with month)

 

In the exact same flow the same formula in my "update a row" (excel online):

if(equals(coalesce(triggerOutputs()?['body/Certification_Date__c'],'NULL'),'NULL'),'',formatdatetime(addHours(triggerOutputs()?['body/Certification_Date__c'],1,'yyyy-MM-ddTHH:mm:ssZ'),'dd/MM/yyyy HH:mm'))
 
returns a good date in excel without reversing month and day  :  13/6/21 21:00 (June 13th, 2021 !!!).
 
Has anyone faced this and found a solution?
 
Thanks for your help.
 
 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
FredD44
Frequent Visitor

@jinivthakkar Thks for your reply, I spent some more time on my flow and found out that the problem is in excel. The formatdatetime in the flow is correct but filling my excel table excel does wrong with the info when it's confusing day less than 12 is concidered as the month ...) I tryed to change the format in my flow to 'dd/MMM/yyyy HH:mm' so the month is in letter. Then excel stopped its confusion. 

View solution in original post

2 REPLIES 2
jinivthakkar
Super User
Super User

@FredD44 I have not faced this issue but you can format as per your requirement to avoid the confusion 🙂 

 

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

FredD44
Frequent Visitor

@jinivthakkar Thks for your reply, I spent some more time on my flow and found out that the problem is in excel. The formatdatetime in the flow is correct but filling my excel table excel does wrong with the info when it's confusing day less than 12 is concidered as the month ...) I tryed to change the format in my flow to 'dd/MMM/yyyy HH:mm' so the month is in letter. Then excel stopped its confusion. 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (3,307)