DATEVALUE won't convert date to serial

[zanshin777]

I want to convert date in the format "dd.mm.yyyy" into serial.

Column B includes dates in the format "dd.mm.yyyy" I write a formula which is "=DATEVALUE(B2)" in a cell on column C. I get "#VALUE" error.

How to do that?

[Glenn Kennedy]

Is the data a real date or text that looks like a date? can you post a sample in an Excel sheet?

[Glenn Kennedy]

Try this, formatted as general:

=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

[Tony Valko]

This works for me...

=--SUBSTITUTE(A1,".","/")

Format as Date

[Ali Kırksekiz]

Please Login or Register  to view this content.

Format as Date

[zanshin777]

They won't work.

[Glenn Kennedy]

We had all assumed (as you hadn't posted any sheet...) that you had text that looks like a date. However what you have are real dates. to see the number, just format the cells as general, not as date...

[zanshin777]

I tried to format cells of Excel as Text then made cut and paste. I get same results.

I tried to format cells after cut and paste then it turned dates Excel made up into serial numbers. So both won't work.

[Glenn Kennedy]

OK. Let's start again. EXACTLY what do you want as an output? Is it hte number that corresponds to the dates? See sheet. If not please show us what you want - not in words, but the ACTUAL text/word/number that you want to see.

[bebo021999]

Is the attachment in #6 manual fixed version?
Post original file again.

[zanshin777]

No, the file on #6 is the original version of file.

I want to convert the dates on column B into Serial on column D.

[Pete_UK]

I think the answer to your problem really depends on what you mean by "serial" - please expand on this.

If you want the serial number that the date represents, then all you need in D2 is this formula:

=B2

Format that cell as General, then copy it down.

If you have a different meaning for the word "serial", then please tell us what that meaning is.

Hope this helps.

Pete

[zanshin777]

The data on the B column should be used on the x Axis on the charts.

The right-hand chart is mine. As you see, there, the data on the x Axis includes dates from the B column.

The data on the x Axis of the book's chart looks different. The book says you should turn the dates into serial and refer those in the chart as far as I understand.

How do I enable the data on the x axis of my chart look like that?

[FDibbins]

change the format of the axis to Date 3/14 (to just show m/dd)

[Pete_UK]

Click on your chart, then move the cursor to hover over the X-axis area and right-click and choose Format Axis from the dialogue box. On the Number tab, choose the third one listed (i.e. 3/14), then click OK or Close. No need to use column D.

Hope this helps.

Pete

[FDibbins]

Click on your chart, then move the cursor to hover over the X-axis area and right-click and choose Format Axis from the dialogue box. On the Number tab, choose the third one listed (i.e. 3/14), then click OK or Close. No need to use column D.

Hope this helps.

Pete

Good explanation Pete, thanks

[sandy666]

What is DATEVALUE()
next click here

[zanshin777]

I formatted the x axis data. It works.

How do I enable the chart seize the data? (As you see there are gaps at the left and right.)

I made Format Axis - Axis Options;

Minimum - Fixed : 5.2
Maximum - Fixed : 7.26

Then the data got lost.

[Pete_UK]

You need to enter those values as full dates, i.e. including the year (otherwise Excel will assume the current year). Or you can enter the serial values for those dates - 2/05/2010 = 40300, 7/26/2010 = 40385.

Hope this helps.

Pete

[zanshin777]

How to know that 2/05/2010 = 40300 and 7/26/2010 = 40385? How to convert them into serial (like 40300)?

[FDibbins]

format them as general
or, if you want to see that value in another cell, just type the reference =A1 (if that is where the date is), and then format that cell to general

[zanshin777]

One last question about this topic.

There is 0.7 increase between the x Axis data values of the chart.

However when I set "0.7" (Format Axis - Axis Options - Major Unit - Fixed : 0.7) It looks like mess, It won't work. How to make 0.7 increase?

[FlameRetired]

Try setting the Major unit to 7.0

That sets them a week apart.