Formatting a date as yyyymmdd + ##

[Steve]

I have the date 7/21/07(seniority date) formatted as yyyymmdd = 20070721
In the next column I have other numbers , e.g. 1 thru 13 ( seniority numbers)
I need in the 3rd column the seniority date 20070721 and the seniority
number 13 combined to = 2007072113

Formula =H2&I2 is producing 3928413

and

I can't figure out to get that formatting to produce 207072113, which would
further need to be able to be sorted.

Thanks,

Steve

 

[bj]

if you want it as text
=text(H2,"yyyymmdd")&I2

 

[Steve]

Almost perfect.
I have the I column formatted as 00, so that 7 =07, 13=13, etc.
The below formula is producing 2007072113 correctly when 13 is in I2, but
the incorrect 200707217 when 07 is in the I column. I need the entire # as 10
characters. ( 2007072107) - with the zero in front of the last 7.

Thanks again,

 

[David Biddulph]

So do it in exactly the same way as you did for the date. If the formatting
of cell I2 is 00, then use that format in a TEXT function for I2, just like
you used the format of H2 in the text function for H2.
Hence:
=TEXT(H2,"yyyymmdd")&TEXT(I2,"00")

 

[Steve]

Now it's perfect. Thanks for the help.

Steve

So do it in exactly the same way as you did for the date. If the formatting
of cell I2 is 00, then use that format in a TEXT function for I2, just like
you used the format of H2 in the text function for H2.
Hence:
=TEXT(H2,"yyyymmdd")&TEXT(I2,"00")

 

[macropod]

Hi Steve,

Here's a way that keeps the result as a numeric:
=VALUE(TEXT(H2,"yyyymmdd"))*100+I2

Cheers

 

[Steve]

Maybe even more perfect, keeping as numeric for sorting.

Much thanks,

Steve

Hi Steve,

Here's a way that keeps the result as a numeric:
=VALUE(TEXT(H2,"yyyymmdd"))*100+I2

Cheers
--
macropod
[MVP - Microsoft Word]
-------------------------

I have the date 7/21/07(seniority date) formatted as yyyymmdd = 20070721
In the next column I have other numbers , e.g. 1 thru 13 ( seniority numbers)
I need in the 3rd column the seniority date 20070721 and the seniority
number 13 combined to = 2007072113

Formula =H2&I2 is producing 3928413

and

I can't figure out to get that formatting to produce 207072113, which would
further need to be able to be sorted.

Thanks,

Steve