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. 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (4,852)