Excel Countdown Timer with Time Extensions

The Goal:
To implement a count down timer that can add in time extensions to the end of the timer.

Insight to the the layout:

The Event is just a drop down of events that correlates to a specific time for that event. (For this, we'll just assume it's 50 minutes.)
Round 1 Start is the start time and is input by the user.
Round 1 End is the end time using an excel function. =IF(ISBLANK(C2),"",IFERROR(C2+VLOOKUP($A$2,Lookups!$A$2:$B$9,2), "Invalid"))
Remaining Time is the count down timer with an excel VBA to refresh it continuously =IFERROR(IF(NOW()-TODAY()-D2-TIME(0,F2,0)>0,"TIME IN ROUND",IFERROR(ABS((NOW()-TODAY())-D2)+IFERROR(TIME(0,F2,0),0),"")),"")
Time Extension is the extension in minutes.

What works:
Without time extensions, it does what it should and when the timer reaches 0:00:00 it displays the "TIME IN ROUND" message.

What doesn't work: When a time extension is added, it never reaches 0:00:00 and instead it just starts counting up as seen here. Instead it will show the "TIME IN ROUND" message after the Time Extension is multiplied by 2. This has never worked correctly for me even after messing with the time extension part of the formula.

Code Breakdown:
=IF(ISBLANK(C2),"",IFERROR(C2+VLOOKUP($A$2,Lookups!$A$2:$B$9,2), "Invalid"))
This will display blank until the Start time cell (C2) is filled. Once the start time is filled, the event is referenced to give the time duration (50 minutes).

=IFERROR(IF(NOW()-TODAY()-D2-TIME(0,F2,0)>0,"TIME IN ROUND",IFERROR(ABS((NOW()-TODAY())-D2)+IFERROR(TIME(0,F2,0),0),"")),"")
The first if statement, NOW()-TODAY()-D2-TIME(0,F2,0)>0 this is just checking if the countdown timer has reached the end time.
The second if statement ABS((NOW()-TODAY())-D2)+IFERROR(TIME(0,F2,0),0) is verifying if there's a time extension, add the time extension value to the end time.

Solution

The short answer:

The ABS() is the problem. Peculiar placement of parentheses obscures the problem.

Longer explanation:

ABS((NOW()-TODAY())-D2)
    ↑             ↑

the marked parentheses don’t do any good. You might as well just say

ABS(NOW()-TODAY()-D2)

and it would be clearer to say

ABS(NOW()-(TODAY()+D2))

where TODAY()+D2 is the actual (scheduled) end time of the event. For example, if D2 is displayed as 16:30:00, that is actually stored in the worksheet as January 0, 1900 16:30:00 (which is really December 31, 1899 16:30:00). And, if today is June 27, 2021, then TODAY() is June 27, 2021 00:00:00, and so TODAY()+D2 is June 27, 2021 16:30:00.

And so NOW()-(TODAY()+D2) is how far away the actual (scheduled) end time of the event is from the current time. After the (scheduled) end time of the event, this is positive. Before the (scheduled) end time of the event, this is negative. (At the (scheduled) end time of the event, it is zero, as you would expect for a countdown timer.)

You want to show a positive (but continually decreasing) time before the deadline (and then zero at the deadline, and your "TIME IN ROUND" message after the deadline). So taking the absolute value makes a certain amount of sense. But you’re taking the absolute value of the wrong thing. Consider: if D2 is 16:30:00 and F2 is 15, that means that the deadline is really 16:45. Now, let me take a slightly bigger piece of your formula, change the parentheses as I showed above, remove the IFERROR and add spaces for clarity:

ABS(NOW()-(TODAY()+D2))   +   TIME(0,F2,0)

Before 16:30, this works correctly. NOW()-(TODAY()+D2) is how far away the (scheduled) end time (16:30) is from the current time, as a negative value that is increasing toward zero (as time passes). So the absolute value is a positive value that is decreasing (toward zero).

But, at 16:30, NOW()-(TODAY()+D2) becomes zero, and then it becomes an increasing positive value. And, of course, ABS(x) equals x when x is positive (or zero). So let’s plug in some numbers:

`NOW()`	`NOW()-(TODAY()+D2)`	`ABS(NOW()-(TODAY()+D2))`	`ABS(NOW()-(TODAY()+D2)) + TIME(0,F2,0)` (the countdown value)
16:20	−0:10¹	0:10	0:25
16:25	−0:05¹	0:05	0:20
16:30	0:00	0:00	0:15
16:35	0:05	0:05	0:20
16:40	0:10	0:10	0:25
16:45	0:15	0:15	0:30

__________
¹ This would not be a legal free-standing value, since Excel doesn’t like negative times. But it’s OK in the middle of a formula.

Of course, after 16:45, the outermost IF overrides all this math stuff, and you get your "TIME IN ROUND" message.

Suggestions

Why would you ever want to look at TODAY()+D2 (or NOW()-(TODAY()+D2))? That’s the (originally) scheduled end time of the event. You care about the extended end time (deadline) of the event, which is

TODAY()+D2+TIME(0,F2,0)

OK, TIME(0,F2,0) can throw an error if F2 is negative or non-numeric. So, if there’s a risk that a user will type something like “ASAP” into F2, you need to guard against that with IFERROR().

NOW()-TODAY()-D2-TIME(0,F2,0)>0 is hard to read. It is equivalent to

NOW() > TODAY()+D2+TIME(0,F2,0)

which is (IMO) easily understood as “compare the current time to the extended end time of the event”. (Also, it has a problem: it uses TIME(0,F2,0) without IFERROR().)

The right half of your formula is way more complicated than it needs to be. We can simplify it to

=IF(NOW() > TODAY()+D2+IFERROR(TIME(0,F2,0),0),  "TIME IN ROUND",  (TODAY()+D2+IFERROR(TIME(0,F2,0),0)) - NOW())

But…

The above has a 29-character long repeated subexpression (TODAY()+D2+IFERROR(TIME(0,F2,0),0)). Repeated subexpressions impede readability, especially if they’re long. The extended end time (deadline) of the event is a meaningful value. You might want to display it in a column of its own. But, if not that, how about a helper column? Put

=TODAY()+D2+IFERROR(TIME(0,F2,0),0)

into cell Z2 (and frag/fill down to cover Column Z) and use

=IF(NOW() > Z2,  "TIME IN ROUND",  Z2 - NOW())

in E2. (You can then hide Column Z if you want.)