Calculate Time Diff with hhmm format? 1

[Lokoono]

I just ran into a problem today and I'm stumped on what to do as a fix.

I have about 400 records in a lone table where I have start and end work times for employees displaying in the hhmm format (for example: 1555 as start time and 2135 as the end).

I need to now calculate the difference between those times and update them into a new field in the table.

The fields are currently in as text fields in the database (they were originally just meant for display for the department). I can safely convert the fields to number fields, but not to date/time fields.

Does anyone have any ideas or solutions on how to calcualte the differences between the two times when they're displayed as hhmm?

I appreciate any assistance. Thanks!

 

[JerryKlmns]

Take a look at DataDiff and Format functions. There is also CDate function which might be of help.

BTW, Date/Time fields are used to store dates and times as a real number. Date variables are stored as 64-bit (8-byte) numbers. The value to the left of the decimal represents a date, and the value to the right of the decimal represents a time. It is just a "formated for human" number.

 

[SkipVought]

Hi,

Its a dumb thing to store TIME like 1555.

Forst you must convert the STRING of digits to a TIME VALUE...

Dim
TimeVal: TimeSerial(Mid([YourField],1,2),Mid([YourField],3,2),0)

on your two fields

then use DateDiff

Remember, that TIME VALUES are in units of DAYS.


Skip,

_{When a wee mystic is on the loose..
It's a Small Medium at Large!}

 

[Lokoono]

Skip,

I used to think it was a dumb way to store the times too. However, since we don't have hundreds of thousands of employees complaining about incorrect checks, I guess everything seems to be fine with the hhmm format. I think its also a way for our 4 or 5 different time systems (where employees clock in and out from) to communicate with the central system in a uniform fashion.

I just needed a way for Access to read it, convert it, and calculate it. Your answer really helped, so thanks.