+ Start a Discussion
Leah MorganLeah Morgan 

Formula to convert decimal date/time to text date/time

Hello!

I have had a bit of a situation come up, and I'm hoping that someone with a higher Salforce IQ than me may be able to shed some light!

I have a workflow which runs like so: (Now()-On_Hold_Date_Time__c) where 'On Hold Date Time' is a Date/Time field that is autopopulated by a user action.  This creates a decimal numbe,  like .0034.  I had then created a second formula which took this number, multiplied it 3 times, and then output the number in a text field, this was that formula:
IF(ISBLANK(On_Hold_Total_Length_n__c) , "", TEXT( FLOOR(On_Hold_Total_Length_n__c) ) & " days " & TEXT( FLOOR( MOD( ( On_Hold_Total_Length_n__c) * 24, 24 ) ) ) & " hours " & TEXT( ROUND( MOD( ( On_Hold_Total_Length_n__c) * 24 * 60, 60 ), 0 ) ) & " minutes" )
This would output something similair to this: "2 Days 4 Hours 5 Minutes"

For some reason though the decimal output has now changed so this formula no longer works correctly and I need help figuring out what has happened.  I used to get an output of '1' for one day and .5 for 12 hours which made the math easy.  Now the output is unpredictable and does not fit into this formula.  I'm not sure what happened that would have changed it though.

​Any ideas would be greatly appreciated!
Best Answer chosen by Leah Morgan
Alain CabonAlain Cabon
Hi,

Have you got a sample of dates and wrong results?

You have used the published formula from here: 

Finding the Elapsed Time Between Date/Times​ (Difference dates2 below):
https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=0

but there are many problems not solved with this "simple" formula.

The first problem is the Daylight Saving Time (DST)

12 mar 2017 - Daylight Saving Time Started
5 nov 2017 - Daylight Saving Time Ended

https://www.timeanddate.com/time/change/usa/new-york

Why 9 hours? DST effect (just math:  124 days 8 hours 0 minutes)

User-added image

ERROR: Why 60 minutes? The formula is not precise enough.

User-added image

CORRECT:
User-added image


12 mar 2017 - Daylight Saving Time Started
5 nov 2017 - Daylight Saving Time Ended

15/02 < 12 mar and 17/12 > 5 nov => no DST effect
User-added image

 

All Answers

Alain CabonAlain Cabon
Hi,

Have you got a sample of dates and wrong results?

You have used the published formula from here: 

Finding the Elapsed Time Between Date/Times​ (Difference dates2 below):
https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=0

but there are many problems not solved with this "simple" formula.

The first problem is the Daylight Saving Time (DST)

12 mar 2017 - Daylight Saving Time Started
5 nov 2017 - Daylight Saving Time Ended

https://www.timeanddate.com/time/change/usa/new-york

Why 9 hours? DST effect (just math:  124 days 8 hours 0 minutes)

User-added image

ERROR: Why 60 minutes? The formula is not precise enough.

User-added image

CORRECT:
User-added image


12 mar 2017 - Daylight Saving Time Started
5 nov 2017 - Daylight Saving Time Ended

15/02 < 12 mar and 17/12 > 5 nov => no DST effect
User-added image

 
This was selected as the best answer
Leah MorganLeah Morgan
Thank you for your response.  I looked at my formula again a few hours ago, and I believe that I have found the problem.  My workflow which was pushing the date to the 'On Hold Date/Time' field was set to do so with the condition of only 'Status-On Hold'  Because of that it was updating whenever the user updating the page.  I added a condition to also only update if the 'On Hold Date/Time' field is Null, and it is now working correctly.

Again, thank you for the response! :)