Change Date within a sentance

[Miya]

Hi, is there a way i can make the 00/00/00 point to say I4, so it changes to 31/01/11

Excel Workbook
	B	C	D	E	F	G	H	I
4	Please see rec dated 00/00/00 saved in S:\Test							31/01/2011
Sheet1

 

[GK039]

Change the cell B4 to:

="Please see rec dated " & I4 & " saved in S:\Test"

George

Hi, is there a way i can make the 00/00/00 point to say I4, so it changes to 31/01/11

Excel Workbook
	B	C	D	E	F	G	H	I
4	Please see rec dated 00/00/00 saved in S:\Test							31/01/2011
Sheet1

 

[MrKowz]

Try:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Please see rec dated 31/01/2011 saved in S:\Test</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">31/01/2011</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet2</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B4</th><td style="text-align:left">="Please see rec dated "&I4&" saved in S:\Test"</td></tr></tbody></table></td></tr></table><br />

 

[mole999]

if your using I4 then you could

=concatenate"Please see rec dated " & I4 & " saved in S:\Test"

 

[barry houdini]

If I4 is a real date then you'll need TEXT function, e.g.

="Please see rec dated "&TEXT(I4,"dd/mm/yyyy")&" saved in S:\Test"

 

[Miya]

What about if i named the range in I4 to rngCurrentDate? Would it still work with named ranges?

 

[schielrn]

What about if i named the range in I4 to rngCurrentDate? Would it still work with named ranges?

Yes it will still work with named ranges. Just replace I4 with the named range. And also take note to what Barry said.

 

[Miya]

If I4 is a real date then you'll need TEXT function, e.g.

="Please see rec dated "&TEXT(I4,"dd/mm/yyyy")&" saved in S:\Test"

Hi Barry do i use the named range like this?

="Please see rec dated "&TEXT("rngCurrentDate","dd/mm/yyyy")&" saved in S:\Test"