function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
VSK98VSK98 

Calculate the hours between two datetime fields

Hi All,
I have created the formula field for calculating the hours between two datetime fields ........But it not showing exact hrs......My Piece of code
  
TEXT(DATEVALUE (Closed) - DATEVALUE (Open)- 0.2916)
  
IF(
Floor(DATEVALUE (closed)-DATEVALUE (open))*24)>9
(Floor((DATEVALUE (closed)-DATEVALUE (open))*24)),
"0"& TEXT(Floor((DATEVALUE (closed)-DATEVALUE (open))*24))
)

I tried both .......bt i couldn't get it


Pls suggest me

ADV THNXS
VSK98
Harsh Aditya 21Harsh Aditya 21
Hi VSK98,

Please try the below formula:
  
(Closed - open) *24

Harsh Aditya
AshlekhAshlekh
Hi.

I've created a Text type formula and able to see the difference.

I took this case - Calculate the time difference between created record and lastmodified record.

Here is formula.
  
TEXT(FLOOR((LastModifiedDate - CreatedDate)*24)) &' Hr '& TEXT(FLOOR(((((LastModifiedDate - CreatedDate)*24)-FLOOR((LastModifiedDate - CreatedDate)*24))*60)))&' Mins'

-Thanks
Ashlekh Gera
David SchmitkeDavid Schmitke
Thanks for this solutuon Ashlekh, very helpful! 
WillSh1WillSh1
Hi Ashlekh,

Thanks, great formula. Were you able to exclude weekends?

Cheers
Will
Sibluself SibluselfSibluself Sibluself
Hello
Thank you for help . (https://www.my-accountaccess.com/)
Jayesh KshirsagarJayesh Kshirsagar
And can we calculate seconds and miliseconds also ?
if yes then what will be the formula to calculate seconds and miliseconds.
Michael Farnsworth 3Michael Farnsworth 3
Missing brackets: In the IF statement, you're missing a closing bracket for the condition. It should be (Floor(DATEVALUE (closed)-DATEVALUE (open))*24)>9.
Incorrect operator: In the IF statement, you're using a closing bracket ) after 24 instead of an opening bracket (. It should be Floor((DATEVALUE (closed)-DATEVALUE (open))*24) > 9.
Decimal subtraction: Subtracting a decimal value like 0.2916 from the result of DATEVALUE might not give you the accurate hour calculation. It's better to use the DATETIMEVALUE function to convert both datetime fields to a single datetime value and then perform the subtraction.
Here's an updated version of your code with the above changes: 
TEXT((DATETIMEVALUE(Closed) - DATETIMEVALUE(Open)) * 24)
 
IF(
    (Floor((DATETIMEVALUE(Closed) - DATETIMEVALUE(Open)) * 24)) > 9,
    TEXT(Floor((DATETIMEVALUE(Closed) - DATETIMEVALUE(Open)) * 24)),
    "0" & TEXT(Floor((DATETIMEVALUE(Closed) - DATETIMEVALUE(Open)) * 24))
)
https://www.onecognizant.one/

Make sure the datetime fields Closed and Open are in the correct format and that the formula field is of the appropriate data type (e.g., Text).
alyyy ishealyyy ishe
the same problem i am facing with me new made asphaltcalculator (https://asphaltcalculators.com/) on my new website with help of online tool but the calculations and css i don't satisfy is there anyone who can help me out with this.