Time Format

T

Titanium

I am trying to convert a number of cells to the military format. However,
when I try to accomplish this using Format -> Time -> "Military format type"
the result is 0:00:00.

I would certainly appreciate any help on this. Thank you in advance.
 
P

Peo Sjoblom

Do you just mean 24 hour format then that is the default format of Excel and
you can change the default settings in windows control panel under regional
settings or just use hh:mm as opposed to hh:mm AM/PM So if you have 12:00 AM
in a cell and convert it to 24 hour format it will be displayed as 00:00
which is what it is, you seem to want 24:00 which you will get if you type
in 1 (one day is 24 hours) and use a custom format of [hh]:mm

however military time is when you write (and presumably say) 08:00 AM as 800
or 08:00 PM as 2000 (I believe they say Twenty Hundred) then there is no
built in format for this

You need to use either formulas or macros
 
T

Titanium

Yes, I meant military time display as in 8pm to equal 20:00 read twenty
hundred hours. I have a row of times set such as 1700, 0800 etc and need them
to be understood as a time in the format 00:00:00 = hh:mm:ss.

What I need is a formula or macro to make this happen.

Thanks in advance.

Peo Sjoblom said:
Do you just mean 24 hour format then that is the default format of Excel and
you can change the default settings in windows control panel under regional
settings or just use hh:mm as opposed to hh:mm AM/PM So if you have 12:00 AM
in a cell and convert it to 24 hour format it will be displayed as 00:00
which is what it is, you seem to want 24:00 which you will get if you type
in 1 (one day is 24 hours) and use a custom format of [hh]:mm

however military time is when you write (and presumably say) 08:00 AM as 800
or 08:00 PM as 2000 (I believe they say Twenty Hundred) then there is no
built in format for this

You need to use either formulas or macros



--
Regards,

Peo Sjoblom



Titanium said:
I am trying to convert a number of cells to the military format. However,
when I try to accomplish this using Format -> Time -> "Military format
type"
the result is 0:00:00.

I would certainly appreciate any help on this. Thank you in advance.
 
P

Peo Sjoblom

here's a formula


with these in A1:A3

2330
1400
700



this formula in B1

=--TEXT(A1,"00\:00")

it's important to format the cell as time first [hh]:mm or hh:mm

copy down to B3 will return


23:30
14:00
7:00



--

Regards,

Peo Sjoblom


Titanium said:
Yes, I meant military time display as in 8pm to equal 20:00 read twenty
hundred hours. I have a row of times set such as 1700, 0800 etc and need
them
to be understood as a time in the format 00:00:00 = hh:mm:ss.

What I need is a formula or macro to make this happen.

Thanks in advance.

Peo Sjoblom said:
Do you just mean 24 hour format then that is the default format of Excel
and
you can change the default settings in windows control panel under
regional
settings or just use hh:mm as opposed to hh:mm AM/PM So if you have 12:00
AM
in a cell and convert it to 24 hour format it will be displayed as 00:00
which is what it is, you seem to want 24:00 which you will get if you
type
in 1 (one day is 24 hours) and use a custom format of [hh]:mm

however military time is when you write (and presumably say) 08:00 AM as
800
or 08:00 PM as 2000 (I believe they say Twenty Hundred) then there is no
built in format for this

You need to use either formulas or macros



--
Regards,

Peo Sjoblom



Titanium said:
I am trying to convert a number of cells to the military format.
However,
when I try to accomplish this using Format -> Time -> "Military format
type"
the result is 0:00:00.

I would certainly appreciate any help on this. Thank you in advance.
 
T

Titanium

You're formula worked perfectly! Thank you SO much!

Peo Sjoblom said:
here's a formula


with these in A1:A3

2330
1400
700



this formula in B1

=--TEXT(A1,"00\:00")

it's important to format the cell as time first [hh]:mm or hh:mm

copy down to B3 will return


23:30
14:00
7:00



--

Regards,

Peo Sjoblom


Titanium said:
Yes, I meant military time display as in 8pm to equal 20:00 read twenty
hundred hours. I have a row of times set such as 1700, 0800 etc and need
them
to be understood as a time in the format 00:00:00 = hh:mm:ss.

What I need is a formula or macro to make this happen.

Thanks in advance.

Peo Sjoblom said:
Do you just mean 24 hour format then that is the default format of Excel
and
you can change the default settings in windows control panel under
regional
settings or just use hh:mm as opposed to hh:mm AM/PM So if you have 12:00
AM
in a cell and convert it to 24 hour format it will be displayed as 00:00
which is what it is, you seem to want 24:00 which you will get if you
type
in 1 (one day is 24 hours) and use a custom format of [hh]:mm

however military time is when you write (and presumably say) 08:00 AM as
800
or 08:00 PM as 2000 (I believe they say Twenty Hundred) then there is no
built in format for this

You need to use either formulas or macros



--
Regards,

Peo Sjoblom



I am trying to convert a number of cells to the military format.
However,
when I try to accomplish this using Format -> Time -> "Military format
type"
the result is 0:00:00.

I would certainly appreciate any help on this. Thank you in advance.
 
P

Peo Sjoblom

Thanks for the feedback


--
Regards,

Peo Sjoblom



Titanium said:
You're formula worked perfectly! Thank you SO much!

Peo Sjoblom said:
here's a formula


with these in A1:A3

2330
1400
700



this formula in B1

=--TEXT(A1,"00\:00")

it's important to format the cell as time first [hh]:mm or hh:mm

copy down to B3 will return


23:30
14:00
7:00



--

Regards,

Peo Sjoblom


Titanium said:
Yes, I meant military time display as in 8pm to equal 20:00 read twenty
hundred hours. I have a row of times set such as 1700, 0800 etc and
need
them
to be understood as a time in the format 00:00:00 = hh:mm:ss.

What I need is a formula or macro to make this happen.

Thanks in advance.

:

Do you just mean 24 hour format then that is the default format of
Excel
and
you can change the default settings in windows control panel under
regional
settings or just use hh:mm as opposed to hh:mm AM/PM So if you have
12:00
AM
in a cell and convert it to 24 hour format it will be displayed as
00:00
which is what it is, you seem to want 24:00 which you will get if you
type
in 1 (one day is 24 hours) and use a custom format of [hh]:mm

however military time is when you write (and presumably say) 08:00 AM
as
800
or 08:00 PM as 2000 (I believe they say Twenty Hundred) then there is
no
built in format for this

You need to use either formulas or macros



--
Regards,

Peo Sjoblom



I am trying to convert a number of cells to the military format.
However,
when I try to accomplish this using Format -> Time -> "Military
format
type"
the result is 0:00:00.

I would certainly appreciate any help on this. Thank you in advance.
 

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

Top