Excel #Value error when using DATEVALUE function

Question

In cell A2 I have 7/21/2014 12:44:36 PM

When I use DATEVALUE(LEFT(A2;FIND(" ";A2)-1)) I get the error #VALUE.

When I use LEFT(A2;FIND(" ";A2)-1) I get 7/21/2014.

What do I need do that function DATEVALUE(LEFT(A2;FIND(" ";A2)-1)) to return just the date?

Solution 1

DATEVALUE() is designed to make a Date out of plain text. Your cell is currently a Date/Time, which is a numeric value. I recommend using one of the following solutions to get the date from the cell.

Using DATE()

---

This is the cleanest option and the method that I would recommend.

=DATE(YEAR(A2),MONTH(A2),DAY(A2))

YEAR() gets the Year value from the cell, MONTH() gets the Month value, and DAY() gets the Day value. The DATE() function takes a Year, Month, and Day value, so by passing them into DATE() we can get the Date value from A2.

Using INT()

---

If we look at the numeric value of your Date in A2, we see that it is 41841.5309722222. The whole portion of the number (41841.) is the date and the decimal portion (.5309722222) is the time. So if we take INT(A2) to convert this value to an integer, we will lose the decimal portion so that all that remains (41841) is the date. So this is our formula for using INT()

=INT(A2)

The same idea can be accomplished with ROUNDDOWN(A2,0) or =FLOOR.MATH(A2) or =FLOOR(A2,1).

Using DATEVALUE()

---

While the first solution is the cleanest, there is a way to do this with DATEVALUE() that involves converting the cell into Text first. The TEXT() function takes a value and a format string, so we format the cell value as Text as follows

=TEXT(A2,"yyyy-mm-dd")

And this gives us

2014-07-21

We then pass that result into DATEVALUE()

=DATEVALUE(TEXT(A2,"yyyy-mm-dd"))

You will need to format the result as a date.

Using LEFT() and DATEVALUE()

---

Based on this Stackoverflow question that I found, it appears the issue could be a result of inconsistent formatting, so you might try this solution

=DATEVALUE(LEFT(A2,FIND(" ",A2)-1))

I have included my results with this and the other methods in a screenshot below. You can see by my use of the TYPE() command below the value that I tested this on both a number and text.

Results

Formatting

---

I'm assuming you want just the date for calculation purposes, but if you just want to display the date, you can format the cell to only show the date and ignore the time element although the time element will still be present in the cell. I'm assuming you know about this, but since you didn't specify, it is a possible solution.

Solution 2

Please try:

 =INT(A2)

and format the result to suit.

In a comment I have just seen you mention "=INT(A2) return #VALUE!" so I would suggest selecting your date cells, DATA > Data Tools, - Text to Columns, Delimited, Delimiters Tab (only), and at Step 3 of 3 choose MDY for Date: or change your locale to say USA.

If neither work try =INT(TRIM(A2)) in case you have leading spaces (though not showing them).

If still not working, try applying =CLEAN.

If still nothing works then some further details of where your dates are coming from and how imported would be helpful, and of your locale and default date format.

Comments

[StephenH]

@pnuts, 1) His original function works on a string, I've tried it. The only way he would have gotten the #VALUE error is if he was trying to use DATEVALUE() on a numerical value, so I'm assuming it's numerical. Also, most of my solutions work with a string anyways (including my recommended one) so it doesn't matter. 2) Yes, I see the region specific issue, but he should be able to substitute a , for a ; without too much difficulty. I want people to understand my solution, not just copy-paste it.

[Sashka]

When use =DATE(YEAR(A2),MONTH(A2),DAY(A2)) return #VALUE!; =INT(A2) return #VALUE!; =TEXT(A2;"yyyy-mm-dd") return 7/21/2014 12:44:36 PM. Format cell A2 is General, when chage to Date format nothing happens and functions return same error value

[StephenH]

You missed the second part of the TEXT() solution. Did you try the full =DATEVALUE(TEXT(A2,"yyyy-mm-dd"))?

[StephenH]

I've tried all of my solutions on both text and numbers and they work fine. Did you forget to substitue ; in the INT() and DATE() solutions?

[StephenH]

@Sashka, try to format your questions and comments with the appropriate code blocks. Use the ` character in questions and comments or you can use 4 spaces in your questions if you're formatting multiple lines of code in a block by themselves. That will help us all out. Thanks! :-)

[StephenH]

@Sashka, sorry for the excessive comments, I will clean them up once we figure this out, but I wanted to let you know that I've added another solution that references a different StackOverflow post that might apply here. See if that helps.

[StephenH]

@Sashka, I've added results. Let me know if anything is clicking for you. You might try using the TYPE() function to help us get this all cleared up.