BE AWARE that "Date" and "Time" are reserved words and built in functions and shouldn't be used for object names.
Originally Posted by
MsAxes
<snip>Any idea how I can get 9 hours and not 33?<snip>
Since 33 is greater than 24 hours, you need to subtract 24.
In the query "Differences", change the "Diff" column to
Code:
Diff: DateDiff('h',[Local]![Hours],[HoursAdjusted]![NewTime])-(24*(Abs(DateDiff('h',[Local]![Hours],[HoursAdjusted]![NewTime])>24)))
From left to right:
DateDiff('h',[Local]![Hours],[HoursAdjusted]![NewTime]) -->> calculates the difference
(24*(Abs(DateDiff('h',[Local]![Hours],[HoursAdjusted]![NewTime])>24))) -->> calculates the difference and checks if the diff is greater than 24. Results in TRUE (-1) or FALSE (0). the ABS() function changes TRUE from -1 to 1. Then the result is multiplies with 24. This gives you 24 or 0, which is subtracted from DIFF (33) to return 9.
Originally Posted by
MsAxes
<snip> also, if there are minutes and seconds, I would like the difference.
When you subtract two times, you get an elapsed time or duration. You cannot (and should not) write a duration using colons. Anything with a colon is a time.
So you cannot say it took you 1 hour and 16 minutes and write it 1:16:00.
See the article by Allen Browne Calculating elapsed time
Also, see the Alternative functions at the bottom of the page.