You need to sign in to do that
Don't have an account?
sam_Admin
Help with Formula Field on date time fields
I have 2 date time fields, iam trying to calculate the time in days and hours with formula field in 2 decimals, below is the formula i have but right now it only displays in round number, how can i update it so it shows in days and hours
I have 2 date time fields, iam trying to calculate the time in days and hours with formula field in 2 decimals, below is the formula i have but right now it only displays in round number, how can i update it so it shows in days and hours
```
CASE(MOD( DATEVALUE(CreatedDate) - DATE(1985,6,24),7),
0 , CASE( MOD( DATEVALUE(Time_to_completed__c) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( DATEVALUE(Time_to_completed__c) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( DATEVALUE(Time_to_completed__c) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( DATEVALUE(Time_to_completed__c) - DATEVALUE(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( DATEVALUE(Time_to_completed__c) - DATEVALUE(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( DATEVALUE(Time_to_completed__c) - DATEVALUE(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( DATEVALUE(Time_to_completed__c) - DATEVALUE(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+
(FLOOR(( DATEVALUE(Time_to_completed__c) - DATEVALUE(CreatedDate) )/7)*5)
```
I have 2 date time fields, iam trying to calculate the time in days and hours with formula field in 2 decimals, below is the formula i have but right now it only displays in round number, how can i update it so it shows in days and hours
```
CASE(MOD( DATEVALUE(CreatedDate) - DATE(1985,6,24),7),
0 , CASE( MOD( DATEVALUE(Time_to_completed__c) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,5,5,5,6,5,1),
1 , CASE( MOD( DATEVALUE(Time_to_completed__c) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,4,4,4,5,4,6,5,1),
2 , CASE( MOD( DATEVALUE(Time_to_completed__c) - DATEVALUE(CreatedDate) ,7),1,2,2,3,3,3,4,3,5,4,6,5,1),
3 , CASE( MOD( DATEVALUE(Time_to_completed__c) - DATEVALUE(CreatedDate) ,7),1,2,2,2,3,2,4,3,5,4,6,5,1),
4 , CASE( MOD( DATEVALUE(Time_to_completed__c) - DATEVALUE(CreatedDate) ,7),1,1,2,1,3,2,4,3,5,4,6,5,1),
5 , CASE( MOD( DATEVALUE(Time_to_completed__c) - DATEVALUE(CreatedDate) ,7),1,0,2,1,3,2,4,3,5,4,6,5,0),
6 , CASE( MOD( DATEVALUE(Time_to_completed__c) - DATEVALUE(CreatedDate) ,7),1,1,2,2,3,3,4,4,5,5,6,5,0),
999)
+
(FLOOR(( DATEVALUE(Time_to_completed__c) - DATEVALUE(CreatedDate) )/7)*5)
```
Can you check the below link where it shows how to calculate difference between two datetime fields with hours as well.
https://help.salesforce.com/s/articleView?id=sf.formula_examples_dates.htm&type=5
https://www.biswajeetsamal.com/blog/difference-between-two-datetime-fields-in-salesforce-formula-field/
let me know if you face any issues.
If this solution helps, Please mark it as best answer.
Thanks,
Try Below Formula Example Please Mark It As Best Asnwer If It Helps
Thank You!
I tried that before but that's text. iam looking to display the result in number field