User time input

[Guest]

Hopefully I'm posting this in the right area. What I have is a form that
will require the employee to input their start and ending time. Since most
do not have a clue as to military time format, I was trying to see if there
was a way to either put the time in a pop-up or list. The list or pop-up
would show for example 8:00pm but would input it into military time in the
cell. yes a newbie.

 

[Bob Phillips]

You could create a list of times in AM/PM format, and then link a Data
Validation cell to that list to allow the picking, then show the military
time in another cell

=--SUBSTITUTE(TEXT(G1,"hh:mm"),":","")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

Hopefully I'm posting this in the right area. What I have is a form that

will require the employee to input their start and ending time. Since
most
do not have a clue as to military time format, I was trying to see if
there
was a way to either put the time in a pop-up or list. The list or pop-up
would show for example 8:00pm but would input it into military time in the
cell. yes a newbie.

Why not highlight the entire column, right-click the selection and pick
Format Cells from the list that pops up... select Time from the Category
list and 13:30 (military time format) from the Type list. Then it won't
matter what format the time is entered in, it will show as military time.

Rick

 

[Bob Phillips]

As I understand it, military time is 1330, not 13:30.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

As I understand it, military time is 1330, not 13:30.

Okay, then instead of using Time format with 13:30 as the setting, what
about Custom formatting it as hhmm then? That should have the visual effect
the OP wants and still allow the user to input time anyway they want.

Rick

 

[Guest]

I agree with 'Rick R' and I disagree with 'Bob P'. For my understand it, the
military time is a 24 hrs time clock

e.g 10:00 pm, military time is 22:00

 

[Sandy Mann]

I agree with 'Rick R' and I disagree with 'Bob P'. For my understand it,
the
military time is a 24 hrs time clock

It always was when I was in the RAF but I think that *military time* is an
American thing:

Agreeing with Bob:
http://www.michigan.gov/documents/mdch/Military_Time_184276_7.pdf
http://www.easysurf.cc/cmtime.htm
http://usmilitary.about.com/od/theorderlyroom/a/militarytime.htm
http://www.spacearchive.info/military.htm

Agreeing with you:
http://en.wikipedia.org/wiki/24-hour_clock


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Roger Govier]

Hi

Take a look at
http://usmilitary.about.com/od/theorderlyroom/a/militarytime.htm

 

[Guest]

I'm sorry but I guess I asked the question wrong. let see if I can get this
right this time. There will be 3 columms Let say; A1 Start ,B1 Ending , & C1
total. I understand the military time but the employees (most) do not. what
I would like to do is have a pop up or list that would give the employee a
set of time that they could click on With out having to know millitary time,
sort of like the pop-up calendar or list of sites. I have tried the list
part but when I click on 8:00pm it enters the cell as 8:00am even when I
change the format. I have even tried the list with one row stating regular
time ie 8:00pm and the cell next to it stating 20:00 but it will only but in
the first column. Maybe this cannot be done, I don't know..

 

[Sandy Mann]

It works for me with a Validation list (referencing a list of cells)

part but when I click on 8:00pm it enters the cell as 8:00am

8:00pm is not an XL time 8:00 PM is try re-writing the list

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Rick Rothstein \(MVP - VB\)]

I'm sorry but I guess I asked the question wrong. let see if I can get

this
right this time. There will be 3 columms Let say; A1 Start ,B1 Ending , &
C1
total. I understand the military time but the employees (most) do not.
what
I would like to do is have a pop up or list that would give the employee a
set of time that they could click on With out having to know millitary
time,
sort of like the pop-up calendar or list of sites. I have tried the list
part but when I click on 8:00pm it enters the cell as 8:00am even when I
change the format. I have even tried the list with one row stating
regular
time ie 8:00pm and the cell next to it stating 20:00 but it will only but
in
the first column. Maybe this cannot be done, I don't know..

Try this first... click on the "A" in column A and sweep across to column
"B" so that both columns are highlighted. Now, right-click in the shaded
area and select Format Cells from the menu that pops up. Click on the word
"Custom" in the Category list; then click in the Type field and type hhmm in
it; then click OK. Now enter your time in one of the cells in either column
A or B as a time value (that is, with a colon, you have to use the colon so
the field knows it is a time value) using either 12 or 24 hour format and
see what ends up being displayed. This way, none of your users needs to know
about military time and, since the actual value in the cell is still a time
value, you can do straight time calculation on these values). Is that
acceptable?

Rick

 

[Guest]

Thank you very much, It worked great, I have spent a lot of time trying to
get this to and it was a space that defeated me. Now all I have to do it get
it to go past midnight to the next day and I'm set.
Thank you again....

 

[Sandy Mann]

If you mean calculate the time difference crossing midnight then with the
start time in B2 and the stop time in C2 then use:

=C2-B2+(C2<B2)

or:

=MOD(C2-B2,1)

and format as time.

If you are adding hours which amount to more then 24 hours then use a ustom
format of: "[h]:mm" (without the quotes). This will stop the hours rolling
over into day at 24 hours and keep them as hours.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk