Date manipulation Access 2003

[PaulW]

I have an application with a "date in", "Time In", "Date out" and "Time out". I need to be able to calculate the elapsed time for each set. The data entries are a combination of Date from Now(), Time from Now(), mm/dd/yyyy and HH:MM A/P. I know that Now() is a real number (5.5), not sure of the format but I can find this out. How is the best way for me to convert all record fields to the same format and do the required calculations. (I am an old time Cobol programmer.)
TIA
PaulW

[HansV]

Welcome to Eileen's Lounge!

You don't really need separate Date in and Time in fields - a date/time field in Access can hold a complete date plus time value, e.g. 17-Feb-2010 11:40.
The same goes for Date out and Time out.

But if you have separate fields, the time elapsed would be [Date out]+[Time out]-[Date in]-[Time in]. You can format this as a time.
If you want to know the (decimal) hours elapsed, you could multiply by 24 and format the result as a number instead of as a time:

24*([Date out]+[Time out]-[Date in]-[Time in])

Dates are stored as the number of days since December 30, 1899.
Times are stored as a fraction of a day, i.e. 6:00 AM = 0.25, and 12:00 PM = 0.50.

[PaulW]

Hi Hans,

Thanks for the speedy answer. It was so speedy that it got to me before I was ready for it. I tried it and it works very well.

I am happy that you are on this board now. I'll try not to be a pest.

[Jezza]

Paul

The beauty of this board is you are never a pest, ask away

[PaulW]

I have followed Hans' recommendation and have had a certain amount of success. I now find that the "medium" time I specified in the field definition of the table is coming out with "long" time. My screen shows a one minute difference, but when converting to HH:MM I have a problem with the rounding showing no minutes. 360.433333335693 should be six hours and one minute if you look directly at the screen. Is anyone familiar with the long/medium time problem I seem to be having?
TIA

[HansV]

I'm afraid I don't understand. Is 360.433333335693 a number of minutes? If so, 360 corresponds to 6 hours, but since 0.433333335693 is less than 0.5, it is rounded to 0.

BTW you can specify any format you like for the calculated field - you're not limited to those from the dropdown list in the Format property. You can enter a custom format too.

[PaulW]

Hi Hans,

I am sorry that I was unclear. I only want to use the hours and minutes in the calculations, not the seconds. Is there a (clean) way to get rid of the seconds on capture or on calculating? To restate: the user sees 12:00 as time in and 18:00 as time out. The report shows 6:01 due to the seconds rounding.
TIA

[HansV]

Could you post a small compacted and zipped database that demonstrates the problem? Thanks in advance.

[PaulW]

Hans,

Attached please find a zipped version of my .mdb.

Please refer to report rptHoursWithinDates. The detail lines in question are for Badge numbers 1 and 4. In the case of badge number 1, the user would like to see 2 minutes as the elapsed time. Instead, we see 3 minutes. In the case of badge number 4, the user would like to see 3 minutes as the elapsed time. Instead, we see 2 minutes as the elapsed time.

If there is a way for me to do the calculations only using hours and minutes, the results would be as desired. How can I accomplish this?

Many Thanks . . .

[HansV]

You could change the definition of the calculated Nummin field in the query 'qry-show time in and out' to

Nummin: Int(1440*([tbl370DateOut]+[tbl630TimeOut]))-Int(1440*([tbl360DateIn]+[tbl620TimeIn]))

This rounds both time in and time out down to the nearest minute.

[PaulW]

Hans,

Thank you very much. Why couldn't I have thought of that?

Regards