CODEWITHSUNDEEP
excel-formula
user736893
user736893

Reputation:

Calculate time difference between NOW() and a calculated time

I have a cell that simply looks like 4:11 PM. Here is the formula that calculates it: 

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

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

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

=HOUR(NOW())-HOUR(Table1[@[Leave At]])&":"&TEXT(MINUTE(NOW())-MINUTE(Table1[@[Leave At]]),"00")

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.

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

How can I solve this?

Upvotes: 1

Views: 226

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

The issue is NOW() is a DATE/TIME so the numeric value is >42000. Wand when subtracting it from A time which is <0 you get a negative number, so NOW() needs to be changed to just its decimal part:

=L12-TIME(HOUR(NOW()),MINUTE(NOW()),0)

Or

=L12-(NOW()-INT(NOW()))

The second will return seconds with the output.

Upvotes: 1

Related Questions