You need to sign in to do that
Don't have an account?
Leah 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!
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!
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)
ERROR: Why 60 minutes? The formula is not precise enough.
CORRECT:
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
All Answers
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)
ERROR: Why 60 minutes? The formula is not precise enough.
CORRECT:
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
Again, thank you for the response! :)