timevalue() # value error

[jetpack]

have sheet with now() in A1 and timevalue(9:00") in A2

need to perform an operation in other cells based on a comparison of the two cells.

the following formula returns the #value error.

=IF(RIGHT(A1,9)>A2,B4,C6)

any help greatly appreciated

 

[joeu2004]

RIGHT(A1,9) is probably not what you intended. And comparing NOW() > TIMEVALUE() is always TRUE; again, proably not what you intended.

Try:

=IF(HOUR(A1) > 9, B4, C6)
or
=iF(MOD(A1,1) > TIMEVALUE("9:00"), B4, C6)

Use the latter form if you want to compare current time with time that might include non-zero minutes.

PS.... Consider entering =MOD(NOW(),1) into A1 if you want A1 to contain only time of day. HOUR(A1) still works. But use A1 > TIMEVALUE("9:00") alternatively.

NOW() return date as well as time. Excel time is stored as a fraction of a day. The cell format only affects the appearance, not the actual value. So even if A1 appears to be 12:34:56, it is actually 43452.5242592593. The RIGHT function sees the latter, not what is displayed in the cell.

 

[joeu2004]

Errata....

=IF(HOUR(A1) > 9, B4, C6)
or
=iF(MOD(A1,1) > TIMEVALUE("9:00"), B4, C6)

Sorry, they are not the same. If the current time is 9:12:34, the second formula returns B4; the first formula returns C6.

BTW, I wonder if you want ">=" instead of ">".

Note that Excel NOW() returns time truncated to the 1/100 second. So even if the time appears to be 9:00:00, it rarely is exactly 9:00:00.000.

So most of the time that you see the time 9:00:00, MOD(A1,1) > TIMEVALUE("9:00") will be TRUE(!) because NOW() is 9:00:00.010 or greater. If that is acceptable, I think that MOD(A1,1) >= TIMEVALUE("9:00") is more consistent.

(In contrast, VBA Now returns time truncated to the second.)

 

[jetpack]

it be hittin' on all cylinders now.

thanx for ur help mate.

 

[jetpack]

appreciate the additional info, a great explanation.

 

[jetpack]

Errata....



Sorry, they are not the same. If the current time is 9:12:34, the second formula returns B4; the first formula returns C6.

BTW, I wonder if you want ">=" instead of ">".

Note that Excel NOW() returns time truncated to the 1/100 second. So even if the time appears to be 9:00:00, it rarely is exactly 9:00:00.000.

So most of the time that you see the time 9:00:00, MOD(A1,1) > TIMEVALUE("9:00") will be TRUE(!) because NOW() is 9:00:00.010 or greater. If that is acceptable, I think that MOD(A1,1) >= TIMEVALUE("9:00") is more consistent.

(In contrast, VBA Now returns time truncated to the second.)

supposing a bloke wants to evaluate a cell at exactly 9:01:00, or the closest possible time after that time.
what would that formula look like?

 

[joeu2004]

supposing a bloke wants to evaluate a cell at exactly 9:01:00, or the closest possible time after that time.

I don't understand. Can you provide a more-detailed concrete example? It might help if you also explain why.

I don't understand what you mean by "evaluate a cell". Excel "evaluates" (recalculates) a cell whenever it decides it is necessary.

I don't understand what you mean by "exactly ... or closest possible ... after". Do you mean: any time at or after 9:01 and before 9:02? Or at or after 9:01:00 and before 9:01:01?

 

[jetpack]

yea, that be a bit fuzzy around the edges.

after your explanation of how excel 'evaluates' time, i see the need for a more precise
timing method. my sheet contains rtd like sensors for industrial machinery that record temp, pressure, flow rate, etc. there is a need to troubleshoot anomalies, tracked by time of occurrence. therefore, certain cells require an exact time for a formula to copy the values in certain other cells. in the case of a problem arising, a second could be the difference between a 5 minute evaluation to determine when, where, and why of a situation and a half hour, depending on the complexity of the system.

so, the need is to have this formula to run at, eg, exactly shift startup or as close to that time as excel can determine. be that 1/100 second before the specified time or 1/100 second after.

i had noticed that excel is very loosey-goosey with "time", when you get down to seconds granularity, but i didn't know why, until you explained it.

does that help?

.

 

[joeu2004]

my sheet contains rtd [....] the need is to have this formula to run at, eg, exactly shift startup or as close to that time as excel can determine. be that 1/100 second before the specified time or 1/100 second after. [....] does that help?

Yes.

Not to nitpick, but in the interest of clarity.... Since NOW() is a "volatile" function, any formula that uses NOW() or that depends directly or indirectly on a formula that uses NOW() "runs" (is recalculated) whenever any cell in the workbook is modified (by RTD, for example), not at a particular time of day.

So I assume that we are still talking about a formula of the form =IF(A1 > A2,B4,C6), where A1 is current time, and A2 is the shift startup time, for example.

(Do you really mean =IF(A1 <> A2,B4,C6)?)

You say that "1/100 second before" or "1/100 second after" should be considered "equal". I hope that 50/100 before and after is just as good.

In that case, instead of =NOW() in A1, use =--TEXT(NOW(),"h:m:s").

That captures the current time (at recalculation time) rounded to the second.

And =IF(A1 > A2,B4,C6) should be sufficient, assuming that A2 is also rounded to the second, as it is in your original posting as a result of =TIMEVALUE("9:00:00").

Note: I use "h" instead of "[h]" so that 23:59:59.500 is rounded to 00:00:00, which is how midnight is written in Excel, not 24:00:00. But that might be something that you need to think about.

 

[jetpack]

thanks alot mate. that suits nicely.
appreciate your time to help and explain. cheers.