Share via

#VALUE! error when using TIMEVALUE or DATEVALUE function

Anonymous
2020-06-09T02:11:42+00:00

Can anyone tell me why the formula is B6 is giving the #VALUE! error.

The formula in B8 is =TIMEVALUE(A8). The cell format of A8 is TEXT

FYI this happens when I use DATEVALUE and VALUE function as well. I'm using O365 v1908.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2020-06-09T05:45:32+00:00

    The error is because that short date format is set to ddd d/M/yy.

    This doesn't work when you have ddd in front of the date format. You need to change this format to a regular date format say dd/mm/yy and the error will disappear.

    2 people found this answer helpful.
    0 comments

12 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-06-09T02:22:25+00:00

    The space between 12:18 and PM is a non-breaking space (ASCII character 160) not a regular space (ASCII character 032). These are commonly used on web sites so the contents of a table's cell doesn't text wrap. The HTML code is  .

    Open the Windows 10 utility charmap.exe, locate the non-breaking space character in the 5th row just right of centre. Click Select then click Copy.

    Go back to Excel and select column A. Open Find and Replace (Ctrl+H). Paste the non-breaking space character into the Find what: text box and type a normal space into the Replace with: text box. Click the Replace All button.

    0 comments
  2. Anonymous
    2020-06-09T02:53:34+00:00

    Thanks for the response. The thing is I used my keyboard to put that space, did not copy from a web page. Now the question is how do I get a regular space (ASCII character 032)?

    0 comments
  3. Anonymous
    2020-06-09T03:00:59+00:00

    Also I checked the ASCII value of the space between 12:18 and PM. It is 32 not 160.

    0 comments
  4. Anonymous
    2020-06-09T03:05:30+00:00

    ... The thing is I used my keyboard to put that space, did not copy from a web page. Now the question is how do I get a normal space?

    I really don't know how you typed something other than a space into a worksheet cell. It is ultimately possible; particularly so with a full size keyboard equipped with a Num Pad.In fact, the existence of a non-breaking space is only my best guess. It does explain the situation but it is not the only possible explanation..

    You can double-check the identity of each character by putting =UNICODE(MID($A$8, ROW(1:1), 1)) into an unused cell and dragging down. Each character/digit will be examined and their corresponding code returned to the cell. You'll get #VALUE! when you run out of characters/digits to examine.

    Maybe just use a formula to make the substitution.

    =TIMEVALUE(SUBSTITUTE(A8, UNICHAR(160), CHAR(32)))

    0 comments