Convert Date to Text

J

Jeff Granger

I want to use a date field to produce an invoice number.

Can anyone tell me how to enter a date in a date formatted field e.g.
31/12/06 and have "061231" appear in another cell, but formatted as text?
(It needs to be text because my next stage will be to add the first three
characters of the company name [concatenate], which I'm OK with.
Thanks

Jeff
 
P

Pete_UK

Jeff,

try this:

="ABC"&TEXT(A1,"yymmdd")

where your date is in cell A1 and your company initials are ABC - you
could get these from another cell, eg B1, so the formula becomes:

=B1&TEXT(A1,"yymmdd")

Hope this helps.

Pete
 
J

Jeff Granger

Pete

That does the trick. Thanks.

Only problem I have now is that the cell I'm putting the formula in insists
on displaying the formula not the result!

Any thoughts on that one?

Jeff


Pete_UK said:
Jeff,

try this:

="ABC"&TEXT(A1,"yymmdd")

where your date is in cell A1 and your company initials are ABC - you
could get these from another cell, eg B1, so the formula becomes:

=B1&TEXT(A1,"yymmdd")

Hope this helps.

Pete

Jeff said:
I want to use a date field to produce an invoice number.

Can anyone tell me how to enter a date in a date formatted field e.g.
31/12/06 and have "061231" appear in another cell, but formatted as
text?
(It needs to be text because my next stage will be to add the first three
characters of the company name [concatenate], which I'm OK with.
Thanks

Jeff
Click to expand...
Click to expand...
 
D

Dave Peterson

Format the cell as General (format|Cells|number tab). Then reenter the formula.

Or

Tools|Options|View tab
Make sure the Formulas box is unchecked

Or

Make sure you don't have anything before the leading equal sign (no space
character)

Jeff said:
Pete

That does the trick. Thanks.

Only problem I have now is that the cell I'm putting the formula in insists
on displaying the formula not the result!

Any thoughts on that one?

Jeff

Pete_UK said:
Jeff,

try this:

="ABC"&TEXT(A1,"yymmdd")

where your date is in cell A1 and your company initials are ABC - you
could get these from another cell, eg B1, so the formula becomes:

=B1&TEXT(A1,"yymmdd")

Hope this helps.

Pete

Jeff said:
I want to use a date field to produce an invoice number.

Can anyone tell me how to enter a date in a date formatted field e.g.
31/12/06 and have "061231" appear in another cell, but formatted as
text?
(It needs to be text because my next stage will be to add the first three
characters of the company name [concatenate], which I'm OK with.
Thanks

Jeff
Click to expand...
Click to expand...
Click to expand...
 
Top