Convert Date to Text

[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

 

[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

 

[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

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

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

 

[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)

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

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

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