Format an Access calculation to display in full 24 hour mode

[Guest]

How can a formula be formatted to display in 24 hours only. Using short time
format when you add up hours in excess of 24 it reverts to day/hour format.
i.e 25 hours 30 min shows as 01:30 (1 day, 1 hour & 30mins). I am using the
database to add up employee hours who are paid on an hourly rate & these can
range from 6 to 50+.

Phil

 

[Rick Brandt]

How can a formula be formatted to display in 24 hours only. Using
short time format when you add up hours in excess of 24 it reverts to
day/hour format. i.e 25 hours 30 min shows as 01:30 (1 day, 1 hour &
30mins). I am using the database to add up employee hours who are
paid on an hourly rate & these can range from 6 to 50+.

Phil

An Access DateTime is for storing a point in time, not an amount of time.
As you've seen you can fake it for durations as long as they don't exceed 24
hours. For durations you are better off storing them in a Number field
(total minutes for example) or using a design where a beginning and ending
DateTime is stored and then you calculate the duration by comparing those
two values. Once you have the duration in minutes a simple expression will
let you display it is hours and minutes.

 

[Guest]

Rick
I'm struggling with this one, I have a start time field (formatted short
time) and a finish time, same format, and a 'finish time minus start time'
calculation field formatted short time i.e 15:15 - 07:45 = 7:30, how would I
use a number field?

Thanks

Phil

 

[George Nicholson]

One way to get a hh:mm:ss total to display hours greater than 24 in Access:
Int(CSng(Interval*24)) & ":" & Format(Interval, "nn:ss")
Where Interval is some measure of time (a sum, difference, etc.)

http://support.microsoft.com/default.aspx?scid=kb;en-us;210276
How to store, calculate, and compare Date/Time data in Microsoft Access
(the above is a variation from the "ElapsedTime" function in that article)

And/or:
http://office.microsoft.com/en-us/assistance/HA011102181033.aspx
"On time and how much has elapsed"

HTH,

 

[Douglas J. Steele]

Actually, that won't work George: you're getting 24 hours for each day in
interval, but you're missing the hours themselves.

Int(CSng(Interval*24)) + Hour(Interval) & ":" & _
Format(Minute(Interval), "00") & ":" & _
Format(Second(Interval), "00")

 

[Rick Brandt]

Rick
I'm struggling with this one, I have a start time field (formatted
short time) and a finish time, same format, and a 'finish time minus
start time' calculation field formatted short time i.e 15:15 - 07:45
= 7:30, how would I use a number field?

DateDiff() function. It will give you the difference in whatever increment you
want.

 

[George Nicholson]

Right you are!

(as always)

Thanks

 

[Guest]

Doug-

I am having this same problem and you reply sounds very helpful. My problem
is that I am not too familiar with Visual Basic and code. I work in design
view when creating and modifying my databases. Can you tell me how to solve
this problem in design view or give a little more detail as to how I can use
that code in Visual Basic? Thanks in advance.

 

[Joel den Braber]

Doug-

I am having this same problem and you reply sounds very helpful. My problem
is that I am not too familiar with Visual Basic and code. I work in design
view when creating and modifying my databases. Can you tell me how to solve
this problem in design view or give a little more detail as to how I can use
that code in Visual Basic? Thanks in advance.

 

[Douglas J. Steele]

I'm sorry: I don't understand what you mean by "work in design view".

 

[Guest]

Doug

The link to the web page & the sample database has answered my query,
thanks. However, I now have another problem when I try to multiply hours by
hourly rate it is not giving the correct answer, I guess it is some sort of
formatting problem but I can't crack it, any ideas please.

Phil

 

[Douglas J. Steele]

What sort of error? It's hard to offer suggestions without knowing what the
problem is...

 

[Guest]

Doug
I am trying to find a calculation to muliply the total hours (derived by
using 'HoursAndMinutes format), by a currency rate i.e £3.55, to give a total
wage. Even though I format the field as currency, because I am using the
'HAM' format, this overrides it and I get a result that is incorrect showing
as xx:xx.
Hope this makes sense!

Phil