timevalue() # value error

jetpack

Board Regular
Joined
Nov 4, 2010
Messages
81
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
 
Last edited:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,583
Office Version
2010
Platform
Windows
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.
 
Last edited:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,583
Office Version
2010
Platform
Windows
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

Board Regular
Joined
Nov 4, 2010
Messages
81
appreciate the additional info, a great explanation.
 

jetpack

Board Regular
Joined
Nov 4, 2010
Messages
81
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,583
Office Version
2010
Platform
Windows
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

Board Regular
Joined
Nov 4, 2010
Messages
81
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,583
Office Version
2010
Platform
Windows
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

Board Regular
Joined
Nov 4, 2010
Messages
81
thanks alot mate. that suits nicely.
appreciate your time to help and explain. cheers.
 

Forum statistics

Threads
1,082,317
Messages
5,364,512
Members
400,804
Latest member
davileal

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top