Changing 'Elapsed Time' to decimal number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the 'Elapsed Time' function given on the 'Colums' page of
Microsoft, which works brilliantly.
I need to then use the result to calculate the amount billed. Do I simply,
format the text box as a Decimal or is it more complex than that?
 
Access (and Excel) stores Time values as the decimal portions of a day: 0.25
= 6 hours or 6am, etc., depending on how you choose to format/display it.
Therefore, simply converting it to a decimal format won't give you the
results you want.

To convert to numerical Hours, multiple a time value it by 24 first and then
format as a decimal. (0.25 * 24 = 6).

To convert to numerical Minutes, multiply a time value by 1440 (minutes in a
day) (0.25 * 1440 = 360)

HTH,
 
Thank you George,
I will try it out.

George Nicholson said:
Access (and Excel) stores Time values as the decimal portions of a day: 0.25
= 6 hours or 6am, etc., depending on how you choose to format/display it.
Therefore, simply converting it to a decimal format won't give you the
results you want.

To convert to numerical Hours, multiple a time value it by 24 first and then
format as a decimal. (0.25 * 24 = 6).

To convert to numerical Minutes, multiply a time value by 1440 (minutes in a
day) (0.25 * 1440 = 360)

HTH,
Click to expand...
 
George said:
Access (and Excel) stores Time values as the decimal portions of a day

To convert to numerical Hours, multiple a time value it by 24 first and then
format as a decimal. (0.25 * 24 = 6).
Click to expand...

Actually, under the covers DATETIME values are double precision float
(DOUBLE) and are stored as such. Multiplying a DOUBLE by an INTEGER
does not create a DECIMAL e.g.

SELECT TYPENAME(TIMESERIAL(6,0,0) * 24)

returns 'Double'.

Because the CDEC function is broken in Jet (grrr!), to cast as DECIMAL
you can divide then multiple by the same DECIMAL value. Any value will
do because Jet *natively* treats decimal (lowercase) values as DECIMAL
(uppercase) e.g.

SELECT TYPENAME(0.1)

returns DECIMAL. Thus:

SELECT TYPENAME(TIMESERIAL(6,0,0) * 24 / 0.1 * 0.1)

also returns DECIMAL.
 
I use a function that I created to divide the minutes section of the time by
60 e.g. 06:45 6 hrs 45 mins. = 6 + (45/60) to give a result of 6.75

If you like I will post this function to the group.
 
Back
Top