What Is Wrong With This TIMEVALUE Formula ? Gives #VALUE! error

Please Login or Register  to view this content.

Hi,

Not able to duplicate a #VALUE with that formula.

This website talks about that error.

https://support.office.com/en-us/art...7-acfc7766dfff

Hope this is helpful.

Cheers

I could not get the first formula to work for me. Not really certain what I was doing wrong.

I was able to use the COUNTIF function as shown here in a test sheet environment:

Please Login or Register  to view this content.

This returns how many cells exceed the time range. From that, I can use a conditional formatting process for the remainder of the project.

If anyone cares to send me a sample file using the TimeValue function, that would be great. I'd be interested in learning what I was doing wrong. Formulas are something I need to get better with.

Thanks to all. Merry Christmas !

Hey Ben. Thanks for answering. I can't get the same result you get, not even using the example you sent me. I don't undestand why.

I apologize for the confusion with the example file I posted. This has been spinning my mind most of the afternoon.

If: A1 = 12:00......B1=12:00.....C1 = 12:00.....D1 = 12:01....... Using True / False , the response should be TRUE because of D1.


If: A1 = 12:00......B1=12:00.....C1 = 12:00.....D1 = 12:00....... Using the same logic, it should return FALSE because none of the entries exceed 12:00.

I've used the CTRL / SHIFT / ENTER and I have all cells formatted as TEXT. It always returns FALSE if the times are all less than 12:00 or if all or one exceed 12:00
Just to be certain I've tried formatting the cells A:D as time - same response.

I'm just not certain what I'm doing wrong here. I'm beginning to feel like .... well .... I can't say. I can't believe something this basic is kicking my backside.

A couple things...

1. Sorry I don't know where the top half of my post went when I edited it. But, yes, that COUNTIF that I suggested is a good solution.

2. You need to format the times as times. Not text. Excel doesn't see any difference between "banana" and "12:00" unless it is formatted as a time or wrapped in a TIMEVALUE function.

3. It was unclear if you wanted ONE, AT LEAST ONE, -or- ALL of the inputs to be greater than 12:00 in order to return true. I found how to get to get that post back; it sounds like you want the second example:

If you want to count the number of cells that are greater than 12:00, use this:

=COUNTIF(A1:E1,">"&TIMEVALUE("12:00"))

Then you can do something like:

=IF(COUNTIF(A1:E1,">"&TIMEVALUE("12:00"))>0,"AT LEAST ONE FAIL","OK")

If you need all four to be greater than 12:00 for it to fail:

=IF(COUNTIF(A1:E1,">"&TIMEVALUE("12:00"))=4,"ALL FOUR FAILED","OK")

4. protonLeah is suggesting using an array formula. I would not bother with this Excel abstraction until you are more familiar with functions in Excel. There's a tendency to suggest the shortest, fastest, most "clever," etc. solution to people who ask questions here, but if too often creates more confusion. It is a great solution and correct, no doubt, I just wouldn't stress over it not working for you.

5. Make sure you have am/pm right. 12:01 AM < 11:59 AM.

6. Workbook attached shows, first, the TIMEVALUE function being used to convert the text into values that Excel can work with. Below that is an example of entering times directly and formatting them as such.

JYTS:

Thank you for your explanation. It is much clearer to me now.

You mentioned

6. Workbook attached shows, first, the

... did you mean you attached a workbook on your last post ? If so, it is not there. Or were you referring to * protonLeah * attachment ? I ask only because I am
more visual than cerebral. It really helps if I can look at something and figure out how it works.

Again, thank you. I'll mark this solved.

Also ... THANK YOU to everyone who took time to become engaged with this thread. It means alot.

Merry Christmas !

Somehow my attachment got lost when I later deleted some. I recreated it in the attached.

Glad to help.

JYTS

Just trying to further understand. Your last attachment ... was it working for you prior to posting it ?

Here is a screen shot:
.

Yep, look in F4 and F10.

Thank you. I should have done a little more clicking on the cells. Thought those were just comments.

Thank you again !

Glad I could help, Logit!