Prev: Add same formula to every cell in the range
Next: shared workbook - shared workbook options grayed out
From: ahdoj on 3 Mar 2010 16:29
I'm sure the answers here, but I can't sort it out.

I have data entered as general number with no punctuation that needs to be
converted to military time. How do I convert it without losing the time
entered?
From: T. Valko on 3 Mar 2010 16:51
How about posting *SEVERAL* representative samples and let us know what
results you expect.

--
Biff
Microsoft Excel MVP


"ahdoj" <ahdoj(a)discussions.microsoft.com> wrote in message
news:7A4F62E7-7095-4BC6-93FF-44DBD42B0BA7(a)microsoft.com...
> I'm sure the answers here, but I can't sort it out.
>
> I have data entered as general number with no punctuation that needs to be
> converted to military time. How do I convert it without losing the time
> entered?


From: מיכאל (מיקי) אבידן on 3 Mar 2010 17:06
I suppose you will have to run some calculations of those cells - therefor,
if cell A1 holds: 1300 then in cell B1 type:
=1*(REPLACE(REPT(0,4-LEN(A1))&A1,3,0,":"))
and custom format cell B1 as: hh:mm
If you don't need to run any calculations - try custom format cell A1, as:
##\:##
(without the need of B1)
Micky


"ahdoj" wrote:

> I'm sure the answers here, but I can't sort it out.
>
> I have data entered as general number with no punctuation that needs to be
> converted to military time. How do I convert it without losing the time
> entered?
From: "David Biddulph" groups [at] on 3 Mar 2010 17:22
If you've got 1730 and you want 17:30, use =--TEXT(A2,"00\:00") and format
as time.
--
David Biddulph


"ahdoj" <ahdoj(a)discussions.microsoft.com> wrote in message
news:7A4F62E7-7095-4BC6-93FF-44DBD42B0BA7(a)microsoft.com...
> I'm sure the answers here, but I can't sort it out.
>
> I have data entered as general number with no punctuation that needs to be
> converted to military time. How do I convert it without losing the time
> entered?


From: Fred Smith on 3 Mar 2010 19:25
Assuming you're entering hours and minutes (no seconds), convert with:
=time(a1/100,mod(a1,100),0)

Regards,
Fred

"ahdoj" <ahdoj(a)discussions.microsoft.com> wrote in message
news:7A4F62E7-7095-4BC6-93FF-44DBD42B0BA7(a)microsoft.com...
> I'm sure the answers here, but I can't sort it out.
>
> I have data entered as general number with no punctuation that needs to be
> converted to military time. How do I convert it without losing the time
> entered?

 | 
Pages: 1
Prev: Add same formula to every cell in the range
Next: shared workbook - shared workbook options grayed out