converting hours and minutes to hours
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22541
Printed Date: 27 Apr 2024 at 8:41pm
Topic: converting hours and minutes to hours
Posted By: eckler78
Subject: converting hours and minutes to hours
Date Posted: 07 Feb 2018 at 7:50am
Hello,
I need to get the difference between two date and time fields. I have done so with the below formula, but it spits out the value in HH:MM. I need the output to be HH.HH, in hours only. So not 1:12 (one hour, 12 minutes), but 1.2 (1.2 hours).
CStr (Round (DateDiff ("n", {TA_LABOR.TIME_IN}, {TA_LABOR.TIME_OUT}) \ 60), "#") + ':' +
CStr (Round (DateDiff ("n", {TA_LABOR.TIME_IN}, {TA_LABOR.TIME_OUT}) MOD 60), "00")
Thank you for any help!
|
Replies:
Posted By: kevlray
Date Posted: 07 Feb 2018 at 12:46pm
|
The Round (DateDiff ("n", {TA_LABOR.TIME_IN}, {TA_LABOR.TIME_OUT}) \ 60) should get you the hours (if I am reading it correctly). So is this what you needed?
|
Posted By: eckler78
Date Posted: 08 Feb 2018 at 3:07am
|
Thank you kevlray. I have tried the formula you posted, but this returns just 1.00 hours. I need 1.2 hours. It appears to round down to the nearest hour. The formula I posted returns 1:12, one hour 12 minutes, I need this converted to 1.2 hours.
|
Posted By: kevlray
Date Posted: 08 Feb 2018 at 4:17am
|
Then remove the Round function at the beginning and see what you get.
|
Posted By: eckler78
Date Posted: 08 Feb 2018 at 4:23am
|
(DateDiff ("n", {TA_LABOR.TIME_IN}, {TA_LABOR.TIME_OUT}) \ 60) still returns 1.00
|
Posted By: eckler78
Date Posted: 08 Feb 2018 at 4:32am
time in field = 1/22/2018 7:52:42AM
time out field = 1/22/2018 9:04:03AM
|
Posted By: eckler78
Date Posted: 09 Feb 2018 at 5:18am
OK, i got the below formula to work, but can anyone simplify it so it can be understood?
cstr(int((DateDiff ("n", {TA_LABOR.TIME_IN}, {TA_LABOR.TIME_OUT})) / 60),0) & "." & right(cstr((tonumber(right("0" & cstr((DateDiff ("n", {TA_LABOR.TIME_IN}, {TA_LABOR.TIME_OUT})) mod 60,0),2))/60)),2)
|
|