Calculation to subtract TimeValue from Now() Value

[Guest]

Hello,

I am trying to use a formula that would do the following but it is not
working.

e.g.
Function I a trying to use =IF(D4>C4,"",F4)

D4 -Contains this Function =NOW()
C4 -Contains =TIMEVALUE("3:50 PM")

Value in F4 is a number ( 4 Digits)

I am trying to have E4 return the value in F4 only if the time in D4 is less
than the time in C4. Once the sheet refreshes and the time value in D4 > C4,
then I want the cell E4 to be blank.

Also, if this works is there a way to have the Now() value update or refresh
at regular intervals.

Thank you.

Martin

 

[Ron Rosenfeld]

Hello,

I am trying to use a formula that would do the following but it is not
working.

e.g.
Function I a trying to use =IF(D4>C4,"",F4)

D4 -Contains this Function =NOW()
C4 -Contains =TIMEVALUE("3:50 PM")

Value in F4 is a number ( 4 Digits)

I am trying to have E4 return the value in F4 only if the time in D4 is less
than the time in C4. Once the sheet refreshes and the time value in D4 > C4,
then I want the cell E4 to be blank.

Also, if this works is there a way to have the Now() value update or refresh
at regular intervals.

Thank you.

Martin

1. NOW() refreshes whenever your worksheet recalculates. If this is not
sufficient, you will need to write a Visual Basic macro.

2. Given your formula, D4 will always be greater than C4. You may be
overlooking the fact that NOW() returns the DATE as well as the TIME.
Depending on your requirements, you should either add a date to C4, or subtract
the date from D4. Excel stores dates as serial numbers since 1/1/1900 (or
1904) and times as fractions of a day.

There are many methods to do what I think you want. One would be to change
your comparison formula to read:

=IF(D4>(TODAY()+ C4), "",F4)


--ron