Excel Advanced Drawing Skills 100 Lectures (23) - Countdown Counting in Excel

As shown in the figure below, it is part of the vehicle transportation form of a courier company.Where column F is the planned arrival time, I hope to get the following effect in column G:

If the scheduled arrival time is earlier than the system time, the remaining time is displayed.

If the planned arrival time is later than the system time, the words "Timeout xx hours xx minutes" will be displayed.

To solve this problem, let's sort out our ideas first:

1. To get the current time of the system, you need to use the NOW function.

2. Use NOW() to subtract the time in column F to calculate the time difference.

3. With the help of the TEXT function, the positive number after subtraction is displayed as time, and the negative number after subtraction is displayed as "timeout xx hours xx minutes".

It looks simple, let's try it.

Enter the following formula in cell G2, drop down.

=TEXT(F2-NOW(),"h:mm:ss;timeout h:mm:ss")

The second parameter of the TEXT function uses two format codes. The h:mm:ss before the semicolon corresponds to the format of the positive number application, and h:mm:ss is "hour:minute:second".

The "timeout h:mm:ss" after the semicolon corresponds to the format of the negative number application, that is, the word "timeout" is added before the "hours, minutes and seconds".

However, starting from cell G3, the formula returns the wrong value. What's the reason?

By default, Excel does not support negative time or negative date, so when the time in column F is earlier than the current system time, the two are subtracted to a negative number, common