LinkBack URL
About LinkBacks
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?
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?
Is the data a real date or text that looks like a date? can you post a sample in an Excel sheet?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Try this, formatted as general:
=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))
This works for me...
=--SUBSTITUTE(A1,".","/")
Format as Date
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Format as Date
=TEXT(A1,"00\.00\.0000")+0
They won't work.
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...
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.
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.
Is the attachment in #6 manual fixed version?
Post original file again.
Quang PT
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.
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
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?
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, thanksOriginally Posted by Pete_UK
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
change the format of the axis to Date 3/14 (to just show m/dd)
What is DATEVALUE()
next click here
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.
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
How to know that 2/05/2010 = 40300 and 7/26/2010 = 40385? How to convert them into serial (like 40300)?
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
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?
Try setting the Major unit to 7.0
That sets them a week apart.
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks