Hi

I am very new in access and im doing an application that concerns time manipulation. I dont know how to convert time to a number.

here is what i need to do:

a table has 3 fields:
timefield1 timefield2 timeDifference
10:00 11:00 1:00

now I need to convert the timeDifference field to a number so I could multiply it to a certain number example 5. I dont know the command to do it.

Can someone help?

Thank you

Hi J,

Access stores date/time values as 8-byte floating point numbers, and then formats the numeric value in accordance with your computer's regional settings. The integer part of the floating point number represents the number of days since December 31, 1899 and the fractional part represents the time of day (midnight is .0, 6 AM is .25, noon is .5 etcetera).

If you subtract two dates/times, you get a number that represents a number of days; to get the number of hours, multiply this by 24. You can check this by opening the Immediate window (Ctrl+G) and typing:

Print (#11:00:00 AM# - #10:00:00 AM#)

You 'll get 4.16666666666666E-02 meaning roughly 0.04166666. The difference is a bit more than 0.04 days or roughly 1 hour (24 * .04 rounds up to 1 hour).

If you type:

Print (#11:00:00 AM# - #10:00:00 AM#) * 24

you 'll get 0.999999999999999 (almost an hour).

In a query, you can use
Difference: ([EndTime]-[StartTime])*24

In a form or report, you can set the Control Source of a text box to
=([EndTime]-[StartTime])*24

In both cases, you can set the Format property to display the number of decimals you want.

HTH,

Bob

Hi Bob,

Thank you very much for the reply! however, I am missing something on this. I am doing the the time difference so I could multiply it to a certain rate. Example: rate amount is 5 dollars per hr.

timefield1 timefield2 timeDifference
10:00 11:30 1:30

Thus, I need to multiply this difference by the 5 dollars per hr rate which would be 6.5

When I use your equation for this example the result does not become 1:30 but instead goes to 1.5 since 30 minutes is half the hr. Thus I would be multiplying it by 1.5, NOT 1.30.

Is there anyway that the time difference be converted to as a number, as is? (example for 1:30, it converts to 1.30 and not 1.5 hrs)

Thank you bob!

Hi J,

You lost me. As far as I can make out, 10:00 is your start time and 11:30 is your end time. The difference in time between, say, 10:00 AM and 11:30 AM is not 1:30, but rather 1.5 hours, or 90 minutes. 1.5 hours * $5.00 per hour = $7.50, so your query would look like:

SELECT
  Table1.time_field1 AS [Start Time],
  Table1.time_field2 AS [End Time],
  Table1.rate_per_hour,
  ([time_field2]-[time_field1])*24*[rate_per_hour] AS Payroll
FROM Table1;

The calculated Payroll field will display $7.50. That's about the best I can do. If someone works from 10:00 to 11:30, and you pay them for 1.3 hours of work ($6.50), I think your going to run into trouble.

HTH,

Bob

Hi J,

You can do this by using the DateDiff function with the interval in second. With the result, you divide by 3600 (number of seconds in 1 hour) returning the number of hour.
The query would look like this:

SELECT
  Table1.time_field1 AS [Start Time],
  Table1.time_field2 AS [End Time],
  Table1.rate_per_hour,
  DateDiff('s',[time_field1],[time_field2])/3600 AS TimeDifference
  DateDiff('s',[time_field1],[time_field2])/3600 * [rate_per_hour] AS Payroll
FROM Table1;

HTH

Stéphane Lajoie

Thanks Bob and Stéphane for the reply.
Both solutions worked for me
Bob I did't know what I was thinking! I changed my code already hehe

I have one last problem. This field is populated by military time:
timefield1START timefield2END timeDifference
18:51 00:51

00:51 is 12:51AM, the following day. But when I used the solutions you guys suggested, the system assumes that 00:51 is 12:51AM the SAME day so the timedifference field results to 18.00 instead of 6.00.

Is there another way for me to do this without adding a day field on the form so that the system assumes that if the endtime field is less than the start time, it calculates the value for the NEXT day.

Hi J,

Either way (multiplying by 24 hours or 3600 seconds), you are still working with a time difference of 1.5 hours, not 1.3 hours, which is where I think you're stuck. Both Stephane's and my query multiply 1.5 hours of work times $5.00 for a total of $7.50, not $6.50.

HTH,

Bob

Hi Bob,

You guys were both right I changed my code already but my followup question was regarding the manipulation of dates on different days. Please read my last reply

Thank you!

Hi J,

Short of formatting your date/time fields as:

mm/dd/yyyy hh:nn

and entering the date information yourself, I'm clueless.

BTDHVM (bet that didn't help very much),

Bob

Actually I was thinking of having an If then statement:

If the endtime is less than the starttime it assumes that the endtime is for the NEXT day. Thus I do a calculation: subtract the starttime by 12:00AM. Then subtract 12:00AM by the endtime. Add these 2 values.

ex:

timefield1START timefield2END timeDifference
18:00 01:00

timefieldEnd is less than timefield1start , SO the sytem assumes that the timefieldEND is the NEXT day. Thus 24:00(12:00AM) - 18:00(timefield1START)= 6. Then 01:00(timefield2END) - 00:00(12:00AM) = 1

Add those 2: 6+1 = 7

The problem is I dont know how to add this code(if,then syntax) in Access since I am really new. I usually add the calculations in the control source of the properties for a field in the form. Would you knwo the syntax?

Thanks so much!