Formatting a date as yyyymmdd + ##
 

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

Formatting a date as yyyymmdd + ##
 

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

"Steve" wrote:

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

Formatting a date as yyyymmdd + ##
 

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,

"bj" wrote:

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

"Steve" wrote:

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

Formatting a date as yyyymmdd + ##
 

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")
--
David Biddulph
"Steve" wrote in message
...
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,

"bj" wrote:

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

"Steve" wrote:

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

Formatting a date as yyyymmdd + ##
 

Now it's perfect. Thanks for the help.

Steve

"David Biddulph" wrote:

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")
--
David Biddulph
"Steve" wrote in message
...
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,

"bj" wrote:

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

"Steve" wrote:

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

Formatting a date as yyyymmdd + ##
 

Hi Steve,

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

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

"Steve" wrote in message ...
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

Formatting a date as yyyymmdd + ##
 

Maybe even more perfect, keeping as numeric for sorting.

Much thanks,

Steve

"macropod" wrote:

Hi Steve,

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

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

"Steve" wrote in message ...
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