Military time

E

el zorro

I've developed a data input form for users who insisted
on using military time (e.g, 3:30 p.m. = 1530). They
don't use any colons, they just type the numbers. Seemed
to work fine, except that now I have been asked to do a
calculation that subtracts the end time for an event from
the start time, to get elapsed time. Of course, it doesnt
work with this military time format. For example, one
hour would be represented by the number 100, but a half
hour could be 30 or 70, depending on the times.

SO, does Access have a conversion function that can deal
with this?
 
A

Arvin Meyer

el zorro said:
I've developed a data input form for users who insisted
on using military time (e.g, 3:30 p.m. = 1530). They
don't use any colons, they just type the numbers. Seemed
to work fine, except that now I have been asked to do a
calculation that subtracts the end time for an event from
the start time, to get elapsed time. Of course, it doesnt
work with this military time format. For example, one
hour would be represented by the number 100, but a half
hour could be 30 or 70, depending on the times.

SO, does Access have a conversion function that can deal
with this?
Click to expand...

No, but you can write one if you really want to. But listen up: First,
Military time does not have a 70 as a half hour. Military time is:

00:01 to 24:59

So even if you leave out the colon, 1:30 AM is 0130. Now all you have to do
is parse out the 2 numbers on either side of the real or imaginary colon and
use them with date math. But it gets even easier. If you can talk your
"little girlie men" into using real military time instead of being woosies,
Access will read it and do the math with it's simple functions.

Access knows what 15:30 is. So you can use an input mask to force entry in
true military manner be proud, be tough, and use:

Short Time:

00:00;0;_

as an input mask and Short Time as a format, and you too will be able to do
standard date math (up to 24 hours only) on your field.

Have fun! <LOL>
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
G

Guest

Thanks... yes, I understand that 70 is not a valid number
in military time, but if you subtract 1530 from 1600, for
example, you get 70 "minutes" instead of a half hour.
Therein lies the rub-- I can't esily use their format for
calculations.

I think the work habits of the users are too firmly
entrenched for me to dare to fool with. If I force an
input mask on them, they might rebell. Even if it puts
the colon in for them, I think the format you suggest
would force them to type leading zeroes (i.e., 0730
instead of 730). So, I'm heading in the direction of your
suggestion to convert the number to a time format
recognizable by Access. I'll have to think about how to
do that... I think the answer is going to be pretty
complicated since the number of digits can vary... Any
thoughts?
 
M

Matt

All good Arvin, except it's 00:00 (ie Midnight - the start of a new
day) to 23:59 (ie 11:59pm - the end of that day)...

Matt

;-)
 
J

John Vinson

Thanks... yes, I understand that 70 is not a valid number
in military time, but if you subtract 1530 from 1600, for
example, you get 70 "minutes" instead of a half hour.
Therein lies the rub-- I can't esily use their format for
calculations.
Click to expand...

If you use a Date/Time datatype for the field rather than a Text or
Number data type, 1530 will be stored (correctly) as 0.6458333333333.
All date/time fields, regardless of format, are translated to a Double
Float number, a count of days and fractions of a day since midnight,
December 30, 1899. If you use

DateDiff("n", [starttime], [endtime])

and you've entered 1530 in starttime and 1600 in end time (using the
suggested input masks) you'll get 30 as a result.
 
M

Matt

I think it's even a little easier than that:

Date/Time Field Format: hhnn
Input Mask: 99:99:"00";0;_
Default Value: Time()

This stores the time and allows accurate calculations (I assume that
all times entered occur on the same day...).
It also allows the users to enter the time as HHMM (eg "2359") in any
form fields - sure they _see_ an input mask that looks like:
__:__:00
but they just type the 4 digits and that's all that's displayed - try
it and you'll see what I mean...

HTH

Matt
 
M

Mike M.T.

Try this:

Public Function RegularTime(strTime As String) As Date
Dim strHour As String, strMinute As String
strMinute = Right$(strTime, 2)
strHour = Left$(strTime, Len(strTime) - 2)
If Left$(strHour, 1) = "0" Then
strHour = Right$(strHour, 1)
End If
RegularTime = TimeSerial(strHour, strMinute, 0)
End Function

To test this you can use:

Private Sub TestTime()
MsgBox RegularTime ("0345")
End Sub

You will get 03:45:00
 
E

el zorro

You people are awesome! Based n your input, my plan is to
change the data type from number to date/time (using an
imput mask and forcing the users to enter leading zeroes)
and to convert the old times to date/time using the
function suggested by "Mike M.T."

Regarding the latter, is it possible to use that function
with the update query, or will I need to apply it some
other way?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Entering military time into a userform 3
Adding/Subtracting military time 0
calculate time elapsed inbetween days 3
Formatting Average Time 1
Calculating time 3
Calculate elasped time 3
Time sheets 6
time math 2

Top