Calculating hours worked.

[txboy]

Hello all,

I have a Form where users enter the hours worked for each day.
I use a field on this form to calculate the total hours worked that day.

For Example: Begin Time:1500 End Time: 2300 Total Hours: 8.0 Hrs

The problem is when the user enters a Begin Time of 2300 hrs and an End Time of 0700 hrs my total hours end up with negatives or some other funky number.

I have searched everywhere and cannot find the soulution to my problem.

The table which stores this data has both time fields set to "double"

Thanks for you time!

 

[pbaldy]

I believe this works:

Format([StartTime] -1 -[EndTime], "Short Time")

 

[txboy]

Hello pbaldy,

Thanks for the quick response.

I tried the formula you suggested and got the following error:

"An expression you entered is the wrong data type for one of your arguments"

What's wrong??

 

[pbaldy]

Don't know without seeing your formula. If it's in a form textbox, it would need to be preceded by =.

 

[txboy]

I found the error code problem was with a macro.

After fixing that issue, the field "Total Hours Worked" will not total at all.

I wonder if the fields I have are set wrong. Both time fields are set to general number.

Sorry to be a pain, I have been working on this for days!

 

[pbaldy]

I would probably have them as Date/Time fields. The ones I tested on were. Can you post a sample db?

 

[txboy]

Let me change a few fields and see how that goes. I will post back later.

Thanks for your help!

 

[txboy]

Hey Paul,

After changing the fields from Double to Date/Time it works like a charm.

YOU DA MAN!

 

[pbaldy]

Excellent; glad we sorted it out. Welcome to the site by the way.

 

[sbuckingham25]

Hey Paul and txboy,
I am trying to do this too. I have a field right now that the user just types in the hours himself. It is bound to a table field. Do I put this formula in the format of the form field? I tried and nothing happened.

Can you talk me through it?

 

[pbaldy]

It could be used as the control source of a textbox, in a query, or in code.

 

[txboy]

Paul,

Is there any way to change the format of the formula to show "Total Hours Worked" as a number not a time format?

If employee "A" worked from 0600 to 0200 the Total Hours shows 08:00.
I would like the Total Hours field to show 8.0

Is this possible??

Thanks

 

[pbaldy]

Date/Time is stored as a Double, where the decimal portion is the time, so 8 hours would actually be stored as .3333. Try multiplying your value times 24 and formatting as a number and see if that gets you what you want.

 

[cooh23]

Paul,

Is there any way to change the format of the formula to show "Total Hours Worked" as a number not a time format?

If employee "A" worked from 0600 to 0200 the Total Hours shows 08:00.
I would like the Total Hours field to show 8.0

Is this possible??

Thanks

I did it with this formula:

TotalHours:(([EndTime]-[StartTime])*1440)/60

Hope you got it sorted out.

 

[txboy]

Thanks for the reply,

I tried that formula and got "NAME?" error.

 

[pbaldy]

Make sure you don't have a control with the same name as a field, which will throw Access off. By the way, multiplying by 1440 then dividing by 60 should yield the same result as multiplying by 24 as I originally suggested. Given that there are 2 math operations instead of 1, it will be less efficient.

 

[wiklendt]

Thanks for the reply,

I tried that formula and got "NAME?" error.

you would need to change the field names from [EndTime] and [StartTime] to whatever you have called your fields.