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
Resident Rockstar
Resident Rockstar

@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
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (3,166)