Excel help - converting date to text

[Mick_Marsh]

Does anybody know how to convert a date

4/8/14

to text

04-08-14

in an excel spreadsheet?

[sheerluck]

It's just formatting Mick. Right mouse click on the cell (or highlight the range of cells then right click on the area) and go to formatting. There will be a date option, with a hundred different dates displays.

[sheerluck]

Or are you trying to export it all in text or other for at, and it's giving you a weird 68*** type number?

[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.

[Bytemrk]

Mick,

Do you actually want excel to see it as text?... or you want it to look like you second example ?

As the others have said, right click the cell and choose "Format Cells" then you can either change it to text.. or under date... choose the style of date you want ..that way the spreadsheet still understands it's a date if you need it to.

[Mick_Marsh]

Mick,

Do you actually want excel to see it as text?... or you want it to look like you second example ?

As the others have said, right click the cell and choose "Format Cells" then you can either change it to text.. or under date... choose the style of date you want ..that way the spreadsheet still understands it's a date if you need it to.
Thankyou for reading my post Mr Bytemrk.
Yes, indeed, I want excel to see it as text.
It is not a formatting issue. I know how to format dates.
As we know, excel stores dates as a serial date which is how many days from 0-Jan-1900. 41855 returns a date of 4/8/14 (or 4/8/2014). This figure is useless to me as I want to use the date as text in functions.
It's easy to convert text to date but that is opposite to what I require.

I can extract the day, month and year in separate functions but it extracts 4/8/14 as 4, 8 and 2014 when I need 04, 08 and 14 to put them in a function to return 04-08-14. 4-8-2014 as text doesn't cut it.

[sheerluck]

Here ya go Captain Grumpy ;)

=TEXT(B2,"dd-mm-yy") where B2 is the cell reference to be converted.

Difficult to tell someone's level of Excel proficiency in the space of a few words.

[Mick_Marsh]

Thankyou Blind Freddy.

I had tried that. Sadly, it doesn't work.
Although it displays as required in a cell, it doesn't display as that in a function. It comes up with #REF when I try to use that cell in a function.

[Disco-tastic]

What function are you trying to use it in?

[sheerluck]

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

[Bytemrk]

Which version of Excel are you using Mick?

[Mick_Marsh]

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).

[Mick_Marsh]

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.

[Ferret]

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.

[Blakey]

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

[AK83]

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?

[AK83]

.....
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 :confused: 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.

[Mick_Marsh]

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.

[Mick_Marsh]

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.

[Disco-tastic]

PM sent. Hope I can help.

[Ferret]

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.

In that case

=SUM(INDIRECT("'"&B6&"'!H62:V62"))

Note that there are single quotes here, enclosed by double quotes. So, assuming the contents of cell B6 is 00-00-00 then INDIRECT("'"&B6&"'!H62:V62") resolves to the string '00-00-00'!H62:V62 which becomes the argument of the SUM function.

If you make the contents of cell B6 the string '00-00-00' (note: single quotes) rather than just 00-00-00 (note: no quotes) then you can simplify everything to just

=SUM(INDIRECT(B6&"!H62:V62"))

[Homestar]

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

[Boxhead63]

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:D

[Mick_Marsh]

I have had no success.

Here, have a play.

[Ferret]

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

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

Answer is 500

[Blknight.aus]

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) (http://excel.tips.net/T002334_Converting_Numbers_to_Strings.html) theres the simplest way I could find to do it for you.

[Disco-tastic]

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

[Mick_Marsh]

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.

[Toxic_Avenger]

When something goes wrong in excel...

https://www.aulro.com/afvb/images/imported/2016/08/327.jpg