Formatting Formula for Dates

[NorthwesternWildcats]

knowledgeable excel users,

I am using this formula in cell "A2" of a worksheet =IF(COUNTIF(Count,"(1)")=0,"","(1) Financials as of "&Info!C26)

Where Info!C26 is a cell formatted with a specific date. However, when i use the formula, and the value of Info!C26 is returned, I get the value of the Date (like "36171"). What i really want is the exact value of what's in Info!C26 (the actual date). I have cell "A2" formatted as a "Date", but haven't found any solutions...ideas?

 

[msvec]

try playing with this:
MONTH(Info!C26)&"-"&DAY(Info!C26)&"-"&YEAR(Info!C26)

 

[NorthwesternWildcats]

it worked! thanks for the quick reply

 

[WillR]

=IF(COUNTIF(Count,"(1)")=0,"","(1) Financials as of "&TEXT(Info!C26,"dd/mm/yyyy"))

Even shorter :wink:

 

[Barry Katcher]

You could also try using one of these in the last part of your formula. Replace the references in my formulas to C2, C3 or C4 with your Info!C26.

P.S. Will, you just lost your penguin. It just passed my front yard, heading for the beach.

New Book.xls
	B	C	D	E
1
2		6/30/2003	(1)Financialsasof:06/30/2003
3		6/30/2003	(1)Financialsasof:June30,2003
4		6/30/2003	(1)Financialsasof:30-Jun-2003
5
Sheet4