Calling today's date in a formula

[ashish514]

I need to prepare a daily report of the last day everyday next morning. In the report I have to change the date in a number of headings daily. To reduce my work I created the formulas to change the text automatically.

For Example take the heading- Total Sales on 25/2/2013 ($)

I made the formula- = "Total Sales on" & A1 & "($)" where A1 is the cell where I have to change the date daily so that all the headings are changed automatically.

But I have to write the date in text format otherwise the formula returns a numerical value and my heading looks like this: Total Sales on 41330 ($).

Now I have thought of another way wherein I don't even have to change the date daily. This could be done by using Today function either in the formula or in cell A1. So, I type ="Total Sales on" & TODAY()-1 & "($) in the formula. But again it is returning the numerical value of the date i.e. 41330 and my heading becomes "Total Sales on 41330 ($)". What will I have to do to get "Total Sales on 25/2/2013 ($)" on its place???

 

[gnrboyd]

This should do it:

="Total Sales on " & TEXT(TODAY()-1,"d/m/yyyy") & "($)"

I added the TEXT function to your formula to convert the numerical date into text so it appears in the format you need.

 

[ashish514]

Thanks a lot. It did

 

[gnrboyd]

Glad it worked for you.