Converting Time to Numbers?

[Guest]

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

 

[Joseph Meehan]

Hello:

I have a simple employee database that tracks starting/ending time
each day. Right now, since Access doesn't have a time field,

Access does have a time field type.

From the Access Help file:

~~~~~~~~~~~~~~~~~~~~~~

Format Property - Date/Time Data Type
You can set the Format property to predefined date and time formats or use
custom formats for the Date/Time data type.

SettingDescription

Long TimeSame as the setting on the Time tab in the Regional Settings
Properties dialog box in Windows Control Panel.
Example: 5:34:23 PM.
Medium TimeExample: 5:34 PM.
Short TimeExample: 17:34.

hHour in one or two digits, as needed (0 to 23).
hhHour in two digits (00 to 23).
nMinute in one or two digits, as needed (0 to 59).
nnMinute in two digits (00 to 59).
sSecond in one or two digits, as needed (0 to 59).
ssSecond in two digits (00 to 59).
tttttSame as the Long Time predefined format.
AM/PMTwelve-hour clock with the uppercase letters "AM" or "PM", as
appropriate.
am/pmTwelve-hour clock with the lowercase letters "am" or "pm", as
appropriate.
A/PTwelve-hour clock with the uppercase letter "A" or "P", as appropriate.
a/pTwelve-hour clock with the lowercase letter "a" or "p", as appropriate.
AMPMTwelve-hour clock with the appropriate morning/afternoon designator as
defined in the Regional Settings Properties dialog box in Windows Control
Panel.


Custom formats are displayed according to the settings specified in the
Regional Settings Properties dialog box in Windows Control Panel. Custom
formats inconsistent with the settings specified in the Regional Settings
Properties dialog box are ignored.

~~~~~~~~~~~~~~~~

Note: the help file displays this in a more readable fashion that I can
do in the newsgroup.

Using a real time field, or at least formatting the fields you do use
carefully will allow you to do calculations later when someone decides they
want that as well.

I suggest you may want to also include the date for much the same reason
and it is part of the same field if you care to use it.

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.)

 

[Guest]

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.

 

[Guest]

[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

 

[Joseph Meehan]

[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

Give it a chance. I believe you will see the advantages of the combined
function. After all dates are just times and times are just subdivisions of
dates, having them in one field is more logical and less troublesome in the
long run.

 

[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.

 

[Guest]

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