Thread: Excel help - converting date to text

The workbook has a lot of spreadsheets with the tab label being a week ending date. One of those worksheet tabs is, for example, 04-08-14.
The first worksheet is a summary sheet where I type in dates and it displays the summarised data from the relevant spreadsheet.

So, for example, in cell B2 I type in a date 4/8/2014.
Mr sheerluck's function =TEXT(B2,"dd-mm-yy") is in B6.
My function =SUM($'11-08-14'.H62:V62) currently works for 11/8/2014 but I need to replace the text 11-08-14 with the result from cell B6 (being 04-08-14).

Originally Posted by Bytemrk

Which version of Excel are you using Mick?

I have been using 2013 but I am saving it as 97/2000/XP for compatibility reasons.

Originally Posted by Mick_Marsh

So, for example, in cell B2 I type in a date 4/8/2014.
Mr sheerluck's function =TEXT(B2,"dd-mm-yy") is in B6.
My function =SUM($'11-08-14'.H62:V62) currently works for 11/8/2014 but I need to replace the text 11-08-14 with the result from cell B6 (being 04-08-14).

Well I don't understand. You have:-

In cell B2 a numeric data type - which you display formatted as a date, nevertheless the underlying data type remains numeric.
In cell B6 a string data type - the text representation of the formatted numeric from cell B2

And you want to sum the string data type (the contents of B6) with a what I assume to be numeric data types (the contents of H62:V62). That is =SUM(B6,H62:V62)

The sum of a 'string' and a 'numeric' does not make sense.

Extract date month and years into seperate cells then use the concatenate function to pull them together again as text with spacers.

Originally Posted by sheerluck

Strange. I tested it and it worked perfectly. And I just copied and pasted into my post.

+1.
To confirm sheerlucks formula, I copied-pasted into a blank worksheet, and it works fine.
To confirm it again, I pasted it into another cell, refererenced back to the same date cell and changed the formatting to date type. General type used in the first test cell. Both formats still work the same.

Are you referencing the date cell you want converted to text to a cell in the same sheet, or another sheet?

Originally Posted by Mick_Marsh

.....
The first worksheet is a summary sheet where I type in dates and it displays the summarised data from the relevant spreadsheet.

.....

I'm not 100% sure what you're trying to do here, but it kind'a sounds similar to my pay spreadsheet.

I have a summary sheet to quickly view monthly income data.
Each worksheet is also named for the end of any given week in any given month.
my weeks end on Sundays, so the workweek sheet is dated every Sunday's date.
eg. This coming Sunday is 28th so the current weeks' worksheet name is 28-08-16, next one is 04-09-16 .. etc.

For the summary, if I want to add(or subtract) any numeric data, I use the formula:

=SUM('07-08-16:28-08-16'!N16) ... exactly like that.
What this does is to add the values in N16(eg. GST collected, or gross pay, or allowances to whatever are all calculated in the cell N16, which is obviously the same payment type in each worksheet.

This formula using the date range from 7th Aug to 28th Aug literally refers to the names of the relevant worksheets.
The exclamation mark is vital to complete the link from those 4 worksheets in August, otherwise I also see the #REF error you refer too.

No other formatting(other than dates in one column) in the sheets have been made.
No need to convert anything to texts either.

Your worksheet needs are obviously different to mine, but maybe try using an exclamation mark where you have a fullstop between the sheet name and the two cells you've referenced.
And, you may not need the $ anchor reference either.

I think I'm understanding what you're trying to achieve. Your summary seems to be dynamic, where you want to type in a date and output the relevant data.
Mine is static. The data is set to display all the time, not dynamically when I want it too.
I'll have a bit of a muck around to see if I can get it to work your way too.

Originally Posted by Blakey

Extract date month and years into seperate cells then use the concatenate function to pull them together again as text with spacers.

Tried that.
Didn't work.

Originally Posted by Ferret

Well I don't understand. You have:-

In cell B2 a numeric data type - which you display formatted as a date, nevertheless the underlying data type remains numeric.
In cell B6 a string data type - the text representation of the formatted numeric from cell B2

And you want to sum the string data type (the contents of B6) with a what I assume to be numeric data types (the contents of H62:V62). That is =SUM(B6,H62:V62)

The sum of a 'string' and a 'numeric' does not make sense.

You misunderstand.
I wish to sum the numerics from H62 to V62 on the sheet that has the name as displayed in B6.

B6 is text 00-00-00 that is converted from the numeric formatted as date 00/00/00 in B2.