Time Format

[Guest]

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.

 

[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

 

[Guest]

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.

 

[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

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.

 

[Guest]

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

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

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.

 

[Peo Sjoblom]

Thanks for the feedback


--
Regards,

Peo Sjoblom

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

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

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.