military time function

is there a function to convert regular time entered into cell into military
time?

Hi Beth,

Beth wrote:
> is there a function to convert regular time entered into cell into
> military time?

It depends on what you're trying to do. If you need the military time as a
string (text), you could do this:

=TEXT(A1,"hh:mm:ss")

If you just need a cell that contains a time entered as 8:30 PM to display
20:30:00, you can simply format the cell:

1) Select the cell, right click, choose Format Cells...

2) Select "Time" from the listbox on the left, then choose the format
that looks like Military Time (3rd from last on my machine)

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

the time frame that we are use for these specific sheets is 6am - midnight.
So if 1:00 is entered I need it to automatically change to 13:00 it can never
be 1:00 (users don'yt know military time). And I was wondering how I could
make this happen in a certain cell.
Thanks for your response but I think it's more than a formatiing issue it
calls for an acutal function or macro maybe, after all data is entered then a
button is clicked and then all time entries are transformed in the designated
cells. I should have been more specific sorry any adive that you have would
be great, thanks.

"Jake Marx" wrote:

> Hi Beth,
>
> Beth wrote:
> > is there a function to convert regular time entered into cell into
> > military time?
>
> It depends on what you're trying to do. If you need the military time as a
> string (text), you could do this:
>
> =TEXT(A1,"hh:mm:ss")
>
> If you just need a cell that contains a time entered as 8:30 PM to display
> 20:30:00, you can simply format the cell:
>
> 1) Select the cell, right click, choose Format Cells...
>
> 2) Select "Time" from the listbox on the left, then choose the format
> that looks like Military Time (3rd from last on my machine)
>
> --
> Regards,
>
> Jake Marx
> MS MVP - Excel
> www.longhead.com
>
> [please keep replies in the newsgroup - email address unmonitored]
>
>

Beth,

Beth wrote:
> the time frame that we are use for these specific sheets is 6am -
> midnight. So if 1:00 is entered I need it to automatically change to
> 13:00 it can never be 1:00 (users don'yt know military time). And I
> was wondering how I could make this happen in a certain cell.
> Thanks for your response but I think it's more than a formatiing
> issue it calls for an acutal function or macro maybe, after all data
> is entered then a button is clicked and then all time entries are
> transformed in the designated cells. I should have been more specific
> sorry any adive that you have would be great, thanks.

You could do something like this with worksheet event routines, but here's
the big question: what do you want to happen if someone enters 6:00 - assume
it's 06:00 or change it to 18:00?

If you format the cells as military time, then Excel will display them
correctly for you. But that means the user would have to put the AM or PM
after the time so Excel could determine what they wanted.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Beth, I would provide a time entry popup form where user would select by
radio button the time. This can be very slick and useful or clumsy and
painful in the ... Be careful how you implement.