How To Convert Number Field To Time Field For Calculation?

[Guest]

I got my data in 24 Hrs (Number Field) like 800 Hrs, 1400 hrs, 1432 Hrs.
Anyone know how do I convert the above to time field, hh:nn, so I can do a
calculation to get the duration. I am aware that if the field are all in
time field, we can use the Elapsed Time function to get the intervals.

 

[Jeff Boyce]

Seikyo

Access doesn't have a "time field" data type. It does have a date/time data
type.

Are you saying that your data is an integer or other numeric data type? If
so, to use the date/time functions, you'll also need a date.

 

[Tim Ferguson]

I got my data in 24 Hrs (Number Field) like 800 Hrs, 1400 hrs, 1432
Hrs. Anyone know how do I convert the above to time field, hh:nn, so I
can do a calculation to get the duration. I am aware that if the
field are all in time field, we can use the Elapsed Time function to
get the intervals.

VBA and Jet Date-Time fields store their values in units of Days: so you
can convert 0800 hrs = 8/24 days = 0.3333333

Since you seem to have packed a string value (yes, if 0845 means 08:45,
then it is a string value, not a number) then you have to do some
slicing:

dwMinutes = 60 * (dwTime div 100) + (dwTime mod 100)
dtDate = dwMinutes / 60! / 24!

I am not clear what use this will be, however, since VBA does not display
duration of more than 24 hours nicely at all: it'll try to give you a
date value somewhere in the middle of January 1900! If you really want to
use date-and-time maths, you are probably better off using a proper data
type. Consider:-

One Integer (or Long) field containing a suitable unit, say hours or
minutes. This makes validation easy, and maths trivial: but the user
interface takes some doing, since nobody wants to work out 8 hours in
minutes in their head every time.

Two integer fields, for hours and minutes: easy to validate, easy for
the user, but a bit more work when you do the maths.

A custom control. Since this question comes up here with regularity,
someone might have seen the value of developing an ActiveX control that
would take all the UI work out of solution 1. My C++ skills are nowhere
near that, but other people's are. Such a pity the CCRP project
disappeared, because it would have been a cinch for them. chiz chiz.

Hope that helps


Tim F

 

[superdee]

In access you can set the field format to Date/Time and specify in the
filed format definition that you want the short time format. This
gives hh:mm.

For any Date/Time field you can, when you define a query use the
Format function to change its format.

So you could have the update time filed automatically capture the
current time when the field is updated.

You can then query the field sorted into days , hours or whatever by
using Format(updatetime,"hh") for hours

I got my data in 24 Hrs (Number Field) like 800 Hrs, 1400 hrs, 1432 Hrs.
Anyone know how do I convert the above to time field, hh:nn, so I can do a
calculation to get the duration. I am aware that if the field are all in
time field, we can use the Elapsed Time function to get the intervals.

superdee

 

[Jeff Boyce]

The disadvantage to using a Date/Time field to store this is that the data
appears to be "duration", not "point in time". The example of 28 hours +
would result in Access storing something that appeared to be 4 hours + (and
would, if formatted for time only, hide the 24 day).

Jeff Boyce
<Access MVP>

 

[Guest]

But the problem is I am doing a TimeCard which got a 4 digit (Text Or Number)
TimeIn And 4 Digit (Text Or Number) TimeOut which I need to find the duration
between the 2 values. I have read around this forum and found out that in
order to change to date/time field, I need to convert the value from text to
date/time field(Short Time) before a calculation can be done. May I know how
it should be done?

 

[Douglas J. Steele]

What's the format of the 4 digits?

 

[Guest]

Something like 0730 to 1930 ... 4 Digit Time. I do not know how to perform
time calculation with these number....

 

[Guest]

Think I found a method to solve it but I am not sure of this syntax is
correct or not.

LEFT(IIf (Len([SCHD_START])=3), "0" & [SCHD_START], [SCHD_START], 2) & ":" &
RIGHT([SCHD_START],2)

[SCHD_START] is a text field and I am trying to add a ":" to the 4 digit
like 800 HRS to 08:00 HRS. In this way, I could append it directly to
Date/Time Field which will show me the correct time as 8:00 AM.

The error I got is " The Expression You Entered Has A Function Containing
The Wrong Number Of Arguments. " Could anyone help me check? Thanks

 

[Jeff Boyce]

Seikyo

So, will you NEVER have an "overnight" shift, that starts before midnight
one day, and ends after the next day starts?

The information you are getting is the hour and minute of events (hhmm). To
do math with these, you need a single unit of measure ... probably the
"minutes". One approach would be to convert the information you are
receiving into an "all minute" format, as follows:

identify the "hour" component (using something like Left(YourTime, 2)
convert it to an integer (CInt())
multiply by the number of minutes per hour ( x 60)
add the "minute" component (CInt(Right(YourTime,2))

The result is a number that represents the number of minutes past midnight.

Do this to both start and end values, then do your subtraction.

If you need to report the number of hours and minutes, convert your answer
using integer division ( \ ) and the Mod function.

 

[Tim Ferguson]

Something like 0730 to 1930 ... 4 Digit Time. I do not know how to
perform time calculation with these number....

'
' the ampersand after the 60 forces the whole calculation
' into Long Integer, which should be enough for 4000 years
'
wMinutes = 60& * CInt(Left(strTimeText,2)) + _
CInt(Right(strTimeText,2))

Hope that helps


Tim F

 

[Guest]

Thanks I get what you mean. ^_^

 

[Paul Overway]

Here is an easier way:

Format([SCHD_START],"##:##")

Then if you want to convert the result from above to a DateTime data type,
just use CDate(Format([SCHD_START],"##:##"))