Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
ShowAll Questionssorted byDate Posted
VSK98

# 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

VSK98
Hi VSK98,

`(Closed - open) *24`

Ashlekh
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 Schmitke
Thanks for this solutuon Ashlekh, very helpful!
WillSh1
Hi Ashlekh,

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

Cheers
Will
Sibluself Sibluself
Hello
Thank you for help . (https://www.my-accountaccess.com/)
Jayesh Kshirsagar
And can we calculate seconds and miliseconds also ?
if yes then what will be the formula to calculate seconds and miliseconds.
Michael 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 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.