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-09T03:21:06+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)))

    As I mentioned earlier, the space is indeed a regular space and I used just a keyboard. I have the same issue on two different computers. One is on O365 and the other Excel 2016.

    0 comments
  2. Anonymous
    2020-06-09T03:40:00+00:00

    Hello Jacob

    I am V. Arya, Independent Advisor, to work with you on this issue.

    There may be an invisible character before or after the entries since it is not lying in between. Try using following functions to see if this is the case

    =LEFT(A2,1)

    =RIGHT(A2,1)

    See whether you get blank i..e invisible character as the result for either of these. Then you can replace them through formula or Find and Replace.

    If this is not the case, I would like to have a look at your workbook. May I request you to post the file with dummy data (no production / real data) to Onedrive or any other cloud sharing site and post the link here? If you need help about how to do - https://support.office.com/en-us/article/share-...

    0 comments
  3. Anonymous
    2020-06-09T04:49:37+00:00

    Hi V. Arya

    There are no spaces as you mentioned. I have created a sample based on the actual data. Have a look. 

    https://1drv.ms/x/s!AiCCWtpWTL0p-RDR-QB75c-zHPJu

    Let me know if you can help.

    0 comments
  4. Anonymous
    2020-06-09T04:59:07+00:00

    I note #VALUE error for

    TIMEVALUE("27/05/2020 12:18 p.m.")

    This is because you can't use p.m. but pm

    Then I notice #VALUE for =VALUE(TEXT(F3,"ttt"))+(12/24) and =VALUE(TEXT(F4,"ttt"))+(12/24)

    The reason here is TEXT(F3,"ttt") will evaluate to ttt. Hence operating VALUE on this will give #VALUE.

    Anywhere else do you see #VALUE.

    0 comments