Calculate time difference between NOW() and a calculated time
<p>I have a cell that simply looks like <code>4:11 PM</code>.  Here is the formula that calculates it: </p>

<pre><code>[Time In]+((TIME(8,0,0)-(K11/24))+([Lunch End]-[Lunch Start]))
</code></pre>

<p>I can go into that in more detail, but hopefully it's not necessary.</p>

<p>Now, what I want to do is display what is essentially a countdown, the difference between <code>NOW()</code> and <code>4:11 PM</code>.  Here is the closest I came:</p>

<p><code>=HOUR(NOW())-HOUR(Table1[@[Leave At]])&amp;":"&amp;TEXT(MINUTE(NOW())-MINUTE(Table1[@[Leave At]]),"00")</code></p>

<p>This was right until the minutes passed the comparison minutes, then it started counting back up (IE at 2:05 it said 2:06 left, but once it was 2:25 it said 2:14 left).  Sorry if this is not clear.</p>

<p>One of the problems is, if I try <code>TIMEVALUE(L12)</code> (L12 being 4:11 PM) I get <code>#VALUE!</code>.  I'm assuming this is why it's not working, and why simply doing <code>=L12-NOW()</code> didn't work.</p>

<p>How can I solve this?</p>