I am a meeting planner and regularly get arrival information from my clients
in miliatry format - sometimes even without a colon. I am trying to make a 4
digit
number already on a spreadsheet into a time format. Does anyone know how?
Example: 1135 (should be 11:35 am)
When you click the cell and change format to time - it formats it, but loses
the 1135 time and makes it 12:00 AM.

Hi Kerry,

in an auxilar column use

=time(left(a2,2),right(a2,2),0)

and format

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Kerry O'Brien" escreveu:

I am a meeting planner and regularly get arrival information from my clients
in miliatry format - sometimes even without a colon. I am trying to make a 4
digit
number already on a spreadsheet into a time format. Does anyone know how?
Example: 1135 (should be 11:35 am)
When you click the cell and change format to time - it formats it, but loses
the 1135 time and makes it 12:00 AM.

Thank you so much.

"Marcelo" wrote:

Hi Kerry,

in an auxilar column use

=time(left(a2,2),right(a2,2),0)

and format

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Kerry O'Brien" escreveu:

I am a meeting planner and regularly get arrival information from my clients
in miliatry format - sometimes even without a colon. I am trying to make a 4
digit
number already on a spreadsheet into a time format. Does anyone know how?
Example: 1135 (should be 11:35 am)
When you click the cell and change format to time - it formats it, but loses
the 1135 time and makes it 12:00 AM.

u r welcome

thanks for the feedback, glad to help
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Kerry O''''Brien" escreveu:

Thank you so much.

"Marcelo" wrote:

Hi Kerry,

in an auxilar column use

=time(left(a2,2),right(a2,2),0)

and format

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Kerry O'Brien" escreveu:

I am a meeting planner and regularly get arrival information from my clients
in miliatry format - sometimes even without a colon. I am trying to make a 4
digit
number already on a spreadsheet into a time format. Does anyone know how?
Example: 1135 (should be 11:35 am)
When you click the cell and change format to time - it formats it, but loses
the 1135 time and makes it 12:00 AM.

You have the solution in a diffrent reply.

but an explanation of why you get 12:00 am

Time and Date formats both treat the underlying general value as a day
value
ie now is 38993.8694444444

The 1135 gets treated as a date ie 2nd August 1903 (uk dates)
and the time element is 0, ie 12:00 AM

Steve

On Tue, 03 Oct 2006 19:10:02 +0100, Kerry O'Brien <Kerry
wrote:

I am a meeting planner and regularly get arrival information from my
clients
in miliatry format - sometimes even without a colon. I am trying to
make a 4
digit
number already on a spreadsheet into a time format. Does anyone know
how?
Example: 1135 (should be 11:35 am)
When you click the cell and change format to time - it formats it, but
loses
the 1135 time and makes it 12:00 AM.

Good afternoon, Marcelo. If I have a column with date & time, could this be
modified to insert the ":" between the hours, such as 1835 to 18:35? The
data currently had date and time in it, not just the time. Thanks! -Bill

"Marcelo" wrote:

u r welcome

thanks for the feedback, glad to help
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Kerry O''''Brien" escreveu:

Thank you so much.

"Marcelo" wrote:

Hi Kerry,

in an auxilar column use

=time(left(a2,2),right(a2,2),0)

and format

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Kerry O'Brien" escreveu:

I am a meeting planner and regularly get arrival information from my clients
in miliatry format - sometimes even without a colon. I am trying to make a 4
digit
number already on a spreadsheet into a time format. Does anyone know how?
Example: 1135 (should be 11:35 am)
When you click the cell and change format to time - it formats it, but loses
the 1135 time and makes it 12:00 AM.

Marcelo if your still yout there can you tell me what I would need to add to
this format to include the seconds of time?

"Marcelo" wrote:

Hi Kerry,

in an auxilar column use

=time(left(a2,2),right(a2,2),0)

and format

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Kerry O'Brien" escreveu:

I am a meeting planner and regularly get arrival information from my clients
in miliatry format - sometimes even without a colon. I am trying to make a 4
digit
number already on a spreadsheet into a time format. Does anyone know how?
Example: 1135 (should be 11:35 am)
When you click the cell and change format to time - it formats it, but loses
the 1135 time and makes it 12:00 AM.

Where are the seconds? Are you now entering 6 digits? Also, the formula you
have will fail if you have fewer than 4 digits in the time (ie, anything
before 10:00 am).

For times entered in hhmm format, use:

=time(int(a1/100),mod(a1,100),0)

For times entered in hhmmss format, use:

=time(int(a1/10000),int(mod(a1,10000)/100),mod(a1,100))

Both formulas will support either 0, 1 or 2 digits for the hour.

Regards,
Fred.

"Arlene" wrote in message
...
Marcelo if your still yout there can you tell me what I would need to add
to
this format to include the seconds of time?

"Marcelo" wrote:

Hi Kerry,

in an auxilar column use

=time(left(a2,2),right(a2,2),0)

and format

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Kerry O'Brien" escreveu:

I am a meeting planner and regularly get arrival information from my
clients
in miliatry format - sometimes even without a colon. I am trying to
make a 4
digit
number already on a spreadsheet into a time format. Does anyone know
how?
Example: 1135 (should be 11:35 am)
When you click the cell and change format to time - it formats it, but
loses
the 1135 time and makes it 12:00 AM.