Thread: Excel help - converting date to text

Good to see some Excel genius's here - I may be calling on you all at some stage...

Originally Posted by Homestar

Highlight the box or boxes, right click then click 'format cells'. In the category list there is 'text'. Click on this, then 'ok'. What you type in that box now will be as you hit the keys.

Well it sorted out the same issue i have been having. I have done 2 Excel courses and didn't use them for a while and now i use them every night and have had a struggle with the formatting. Who said you won't learn anything on AULRO.

Cheers
Rob

I have had no success.

Here, have a play.

In cell N6 on sheet "Summary 2013-2014" paste formula

=SUM(INDIRECT("'"&G6&"'!"&P6))

Answer is 500

Whats screwing you up is the date is representing as a number entered in the numeric format from way back when in 1900

Excel is not calculating your date with the value of 30/3/14 its using 41728.
What you want it to look for is the 30/03/14 in the sheet tab title.

in theory if you make the cell format a custom date presentation 30-03-14 isnt available in my version. Then use the =text function and that works.

unless you have a version of excel thats 2007 or newer, or an updated version of 98/2000 then to do what you want you have to do it with a macro.. Theres a few cut and pasteable ones on the interweb.

Converting Numbers to Strings (Microsoft Excel) theres the simplest way I could find to do it for you.

Originally Posted by Ferret

In cell N6 on sheet "Summary 2013-2014" paste formula

=SUM(INDIRECT("'"&G6&"'!"&P6))

Answer is 500

And in J6 use this formula

=INDIRECT("'"&G6&"'!W38")

INDIRECT is a pain to use. You need to use it within other excel functions in order to replicate the text as if you typed it yourself. Something that I've found useful is to click on the "Formula" tab in excel and click the "insert function" button in the left of the ribbon. It will then give you a real time output of what you're entering will look like. You can then insert that within your excel function, just like Ferret has done. You may need more than one INDIRECT Function within a cell, depending on how complicated your cell equation is. For example, the following cell text:

=CEILING(N5+INDIRECT("'"&I1&"'!R4")-INDIRECT("'"&I1&"'!R5")-0.05,0.25)

would be equivalent to:

=CEILING(N5+'19 Aug'!R4-'19 Aug'!R5-0.05,0.25)

if Cell "I1" contained the text "19 Aug".

Cheers

Dan

Originally Posted by Ferret

In cell N6 on sheet "Summary 2013-2014" paste formula

=SUM(INDIRECT("'"&G6&"'!"&P6))

Answer is 500

Thanks Ferret.
This worked a treat.

Initially it didn't work as I cut and pasted the formula. I had to retype the formula.