Converting Time to Numbers?

[Robert T]

Hello:

I have a simple employee database that tracks starting/ending time each day.
Right now, since Access doesn't have a time field, I'm using date/time fields
to enter the employee's starting and ending time every day.

Everything works well, however, I decided that I would rather have a field
that tracks only TIME, so I need to convert the time fields to numeric ones
and I can't figure out exactly how to do the conversion.

I am creating several new NUMERIC and TEXT fields. Note that I don't have to
worry about employees going past midnight.

[1] Start_Time_Hrs [numeric]
[2] Start_Time_Min [numeric]
[3] Start_AMPM [TEXT]
[4] End_Time_Hrs [numeric]
[5] End_Time_Min [numeric]
[6] End_AMPM [Text]
[7] Total_Time [Numeric]

I'm not sure if I will need the Start_AmPm and End_AmPM fields, but I threw
them into the mix for now.

Instead of going back over several months of data, I would like to convert
the current data in the Date/Time fields to numeric fields. I am giving
examples below.

In addition, I'm guessing the expressions I need will go on the form?

7:30 am ->convert to 3 different values:
7 [hours] 30 [minutes] AM

4:00 pm -> convert to 3 different values:
4 [hours] 0 [minutes] PM

I looked at the LEFT function but I must be doing something wrong.
In order to convert minutes, I need a formula to capture the data to the
right of the colon in the Date/Time field.

Once I get the above going, I'm sure I can come up with an expression to
substract the Start_Time from the End_Time to get the total hours and minutes
worked.

Thanks in advance for your help,
Robert

 

[Douglas J Steele]

There are built-in functions Hour, Minute and Second that will extract the
appropriate part from your date/time field.

Note that Hour(#4:00 PM#) will return 16 (as will Hour(#4:01 PM#),
Hour(#4:02 PM#), etc.)

 

[Robert T]

Hello Douglas:

Will those functions give me the ability to calculate hours and minutes,
converting them into numeric values such as 7.5 hours? If so, then there is
no need to convert the date/time fields to numeric ones as mentioned in the
beginning of this thread?

If that's the case, that was a very nifty tip Douglas.

Thanks,
Robert

 

[Douglas J Steele]

There's no reason to add new fields, if that's what you're asking.

Date/Times are stored as 8 byte floating point numbers, where the integer
part represents the date as the number of days relative to 30 Dec, 1899, and
the decimal part represents the time as a fraction of a day. (This is easy
to check: try using a format of yyyy-mm-dd hh:nn:ss on one of your times,
and you'll see 1899-12-30 ....)

That means that a time of 7:30 AM will be stored as 0.3125 (and 7:30 AM
today, 23 Jun, 2005 will be 38526.3125)

Since there are 86,400 seconds in a day, you can multiply the time by 86400
to determine the total seconds, or by 1440 (number of minutes in a day) to
determine minutes. .3125 * 1440 = 450, which is the number of minutes in 7
hours, 30 minutes.

I truly believe you should be including the date along with the time,
though.

 

[Robert T]

[I truly believe you should be including the date along with the time,
though.]

I have a separate date field. I learned how to program with another PC
Database application where the Date and Time fields were separate. I like
that type of setup and feel much more comfortable, as opposed to combining
the date and time in the same field.

Thanks for all of your valuable help,
Robert

 

[Douglas J Steele]

As Joseph suggests, reconsider!

It makes for far easier SQL, for instance, when the date and time is
combined. You can use

WHERE StartDate > #2005-06-24 08:00#

rather than having to do

WHERE StartDate > #2005-06-24# OR (StartDate = #2005-06-24# AND StartTime >
#08:00#)

It also makes date/time arithmetic easier.

For display/presentation purposes, you can always use the DateValue and
TimeValue functions to separate them into date-only and time-only parts.

 

[Robert T]

Hello Joseph and Douglas:

OK, you guys convinced me, I'm going to give it a shot. In addition, the SQL
point you made sound like a real good reason to try such.

Once again guys, I truly appreciate your help and advice.

Robert