#VALUE error with Text function

[FrankNStein]

I want to convert a date like 15.09.2015 into a text string in order to get rid of weekend gaps in a figure.

According to support pages, that is done by TEXT(Value,format). Examples look like:
B5: 15.09.2015
A5: TEXT(B5,"dd/mm/yyyy")

My version works with semicolons instead of commas, so I tried:

A5: TEXT(B5;"dd.mm.yyyy")
A5: TEXT(B5;"dd/mm/yyyy")
A5: TEXT(B5;"dd.mm.yy")

All of them return a #VALUE error.

So how do I do it and what is the problem? I use Excel 2010 non commercial version

Thx

Frank

[Tony Valko]

The #VALUE! error means the date in B5 is not a true Excel date value.

Is dd.mm.yyyy (15.09.2015) a valid date format in your location?

[FDibbins]

Hi, welcome to the forum

1st, are you sure your date really is a date, and not text, looking like a date? Test with =ISNUMBER(B5)....FALSE indicates B5 is already text.

Where is this data coming from, and what exactly are you trying to do?

[AlKey]

Your dates are most likely a text entry

try this formula

=TEXT(DATE(RIGHT(B5,4),MID(B5,4,2),LEFT(B5,2)),"dd.mm.yyyy")

Data Range

	A	B
5	15.09.2015	15.09.2015

[Tony Valko]

If the entry in B5 is really a text entry you might be able to do this:

=TEXT(B5+0;"dd.mm.yyyy")

[FrankNStein]

Thx for the quick replies!

It is a number, formated as date. If I extract it as an integer, it shows the number of days since Jan 1st 1900. ISNUMBER(B5) returns TRUE

So, it is definitely not a text.

I suspected that at first myself and tried to extract strings with right/mid/left but I just got the parts of an integer (number of days since Jan 1st 1900) instead of parts of a text string of the date shown in the cell.

[Tony Valko]

Another possible solution...

Select the cell(s) that contain the date(s).
Goto the Data tab>Text to columns>click Finish

Sometimes this will convert text dates into true numeric dates.

In Excel dates are really just numbers formatted to look like dates for our human consumption.

These numbers are commonly referred to as the date serial number. The date serial number is the count of days starting from a base date. In Windows Excel the default base date is January 1, 1900. So, January 1, 1900 is date serial number 1.

Jan 1 1900 = date serial number 1
Jan 2 1900 = date serial number 2
Jan 3 1900 = date serial number 3
Jan 4 1900 = date serial number 4
Jan 5 1900 = date serial number 5
etc
etc
Jan 1 1975 = date serial number 27395
Jan 1 2000 = date serial number 36526
Jan 1 2014 = date serial number 41640

You can see the date serial number by entering some date in a cell and then changing that cells format to General or Number.

[FrankNStein]

Nice try, but didn't work. BTW I can transform the date column into Text, if I choose Format "0" (I checked it with ISNUMBER, which then gives back a false, but the number shown is 42285 for the Oct. 6th 2015).

And yes, 6.10.2015 is a valid date format in my excel version.

If nothing works I will have to chop up the numbers myself, which will be a nightmare to program.

[Tony Valko]

Maybe this...

=TEXT(DAY(B5);"00.")&TEXT(MONTH(B5);"00.")&YEAR(B5)

[FrankNStein]

Maybe this...

=TEXT(DAY(B5);"00.")&TEXT(MONTH(B5);"00.")&YEAR(B5)

Works, but now my problem is, that the plot reverses i.e. latest exchange price is now to the left of the plot not to the right.

Different question, but...Excel the neverending story...

[Tony Valko]

now my problem is, that the plot reverses i.e. latest exchange price is now to the left of the plot not to the right.

Sorry, don't know what that means.

[FrankNStein]

Sorry, don't know what that means.

The sorting of the values is: Latest prices on top. oldest on the bottom (or end of the sheet in row 2000+). Plot datas are usually arranges as top numbers with x value 1, bottom number in this case with x value 2000+. I found the solution anyway, thx, so no worries there.

Essentially my problem ist soved with your proposal, thx for that.

[Tony Valko]

Essentially my problem ist soved with your proposal, thx for that.

Good deal. Thanks for the feedback!

[FDibbins]

Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like? That way, we donthave to guess

[Tony Valko]

The date format codes are not universal. They are location dependent.

I see your location is Switzerland and wonder if in fact your date format codes are:

d = day
m = month
y = year

We can do some testing to find out.

Create these named formulas. These use Excel4 macro functions and will return the date format codes based on your regional settings.

Name: YearCode
Refers to: =INDEX(GET.WORKSPACE(37);19)

Name: MonthCode
Refers to: =INDEX(GET.WORKSPACE(37);20)

Name: DayCode
Refers to: =INDEX(GET.WORKSPACE(37);21)

Then, enter these formulas in some cells:

=YearCode
=MonthCode
=DayCode

Then, try to use those date format codes in the TEXT function.

[Jonmo1]

Even if the value in B5 was just a text string...
TEXT(B5;"dd.mm.yy") would NOT return a #Value! error.
It would simply return the exact same text string that exists in B5

I've only been able to produce the #Value! error with that formula if B5 actually contains the #Value! error.


Is it really this formula exactly that is returning #Value! ?
=TEXT(B5;"dd.mm.yy")

Or are you also doing some sort of math operation with the result of that function? like
=TEXT(B5;"dd.mm.yy")+10

Or maybe it's another cell that then refers to the cell with the TEXT formula that is doing some math and returning the #Value! error.

[FrankNStein]

Is it really this formula exactly that is returning #Value! ?
=TEXT(B5;"dd.mm.yy")

Yes

Or are you also doing some sort of math operation with the result of that function? like
=TEXT(B5;"dd.mm.yy")+10

Or maybe it's another cell that then refers to the cell with the TEXT formula that is doing some math and returning the #Value! error.

Definitely no, exactly as above first example.

[XOR LX]

Agreed with Tony re post #11, and a little surprised you haven't responded to that post yet.

I certainly don't propose TEXT solutions with "dd", "mm" and "yy" whenever I post on a French forum, knowing of course that "day", "month" and "year" are not universal, and hence neither are "dd", "mm" and "yy" ("jj", "mm" and "aa" for French-language Excel users if anyone's interested).

Regards

[Tony Valko]

...if anyone's interested

Yes, I am!

Thanks for that.

I'm going to see if I can find a source that might show what these codes are for various countries.

[XOR LX]

I'm going to see if I can find a source that might show what these codes are for various countries.

Not a bad idea at all. It's a shame that this excellent tool:

http://en.excel-translator.de/translator/

isn't capable of such translations, nor of array constants (e.g. {1,2,3} to {1\2\3}), for which it might also be useful to have a list (confusingly, I think the separator for column vectors with some systems is not the semi-colon but the period, e.g. the equivalent of {1;2;3} is {1.2.3}).

Cheers