TODAY and NOW functions in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are an office assistant managing employee attendance records.

📋 Request: Your manager wants a daily report showing each employee's check-in time and how many days they have been present this month.

📊 Data: You have a table with employee names, their check-in dates, and check-in times for the current month.

🎯 Deliverable: Create a report that shows today's date, current time, each employee's latest check-in time today, and the total days each employee has checked in this month.

Sample Data

Employee	Check-in Date	Check-in Time
Alice	2024-06-01	08:15 AM
Bob	2024-06-01	08:45 AM
Alice	2024-06-02	08:10 AM
Bob	2024-06-02	08:50 AM
Alice	2024-06-03	08:20 AM
Bob	2024-06-03	08:40 AM
Alice	2024-06-04	08:05 AM
Bob	2024-06-04	08:55 AM
Alice	2024-06-05	08:00 AM
Bob	2024-06-05	08:35 AM

1

Step 1: Insert the current date in cell E1 using the TODAY function.

=TODAY()

Expected Result

2024-06-05 (or the current date when you open the sheet)

2

Step 2: Insert the current date and time in cell E2 using the NOW function.

=NOW()

Expected Result

2024-06-05 09:30 AM (or the current date and time when you open the sheet)

3

Step 3: Create a list of unique employees in column G starting from G2 (Alice, Bob).

Manually type or use UNIQUE function if available: =UNIQUE(A2:A11)

Expected Result

G2: Alice, G3: Bob

4

Step 4: Find the latest check-in time for each employee today in column H starting from H2.

=MAXIFS(C$2:C$11, A$2:A$11, G2, B$2:B$11, E$1)

Expected Result

For Alice (H2): 08:00 AM, For Bob (H3): 08:35 AM

5

Step 5: Count how many days each employee has checked in this month in column I starting from I2.

=SUMPRODUCT((A$2:A$11=G2)*(MONTH(B$2:B$11)=MONTH(E$1))*(YEAR(B$2:B$11)=YEAR(E$1)))

Expected Result

For Alice (I2): 5, For Bob (I3): 5

Final Result

Date Today: 2024-06-05
Time Now: 09:30 AM

Employee | Latest Check-in Today | Days Present This Month
-------------------------------------------------------
Alice    | 08:00 AM              | 5
Bob      | 08:35 AM              | 5

✓TODAY function shows the current date without time.

✓NOW function shows the current date and time.

✓Alice and Bob both checked in every day this month up to today.

✓Latest check-in times today help track punctuality.

Bonus Challenge

Create a conditional formatting rule to highlight employees who checked in after 8:30 AM today.

Show Hint

Use a formula-based rule on the latest check-in time column with formula =H2>TIME(8,30,0) and apply a red fill.

Practice

1. What does the Excel function TODAY() return when entered in a cell?

easy

A. The current date without the time

B. The current time without the date

C. The current date and time

D. The date and time of the last file save

Solution

1. Step 1: Understand the purpose of TODAY()

   The function TODAY() returns only the current date, not including time.

2. Step 2: Compare with other options

   NOW() returns date and time, so TODAY() is only date.

3. Final Answer:

   The current date without the time -> Option A

4. Quick Check:

   TODAY() = current date only [OK]

Hint: TODAY() shows date only, no time included [OK]

Common Mistakes:

• Confusing TODAY() with NOW()
• Expecting time to appear with TODAY()
• Thinking TODAY() updates only when file is opened

2. Which of the following is the correct way to write the formula that returns the current date and time in Excel?

easy

A. =NOW()

B. =TODAY()

C. =DATE()

D. =TIME()

Solution

1. Step 1: Identify the function for current date and time

   NOW() returns both current date and time.

2. Step 2: Check other options

   TODAY() returns only date; DATE() and TIME() require arguments.

3. Final Answer:

   =NOW() -> Option A

4. Quick Check:

   NOW() = current date and time [OK]

Hint: Use NOW() for date and time, TODAY() for date only [OK]

Common Mistakes:

• Using TODAY() to get time
• Writing DATE() or TIME() without arguments
• Missing equal sign in formula

3. If cell A1 contains the formula =NOW() and the current date and time is June 15, 2024, 3:30 PM, what will be displayed in A1 (assuming default date-time format)?

medium

A. 6/15/2024

B. 3:30 PM

C. 6/15/2024 3:30 PM

D. 15-Jun-2024

Solution

1. Step 1: Understand what NOW() returns

   NOW() returns current date and time as a serial number formatted by default to show both.

2. Step 2: Interpret default formatting

   Default format shows date and time together, e.g., "6/15/2024 3:30 PM".

3. Final Answer:

   6/15/2024 3:30 PM -> Option C

4. Quick Check:

   NOW() shows date and time [OK]

Hint: NOW() shows date and time together by default [OK]

Common Mistakes:

• Expecting only date or only time from NOW()
• Confusing default date format with custom formats
• Ignoring time portion in NOW() output

4. You entered =TODAY() in cell B2, but it shows a date from last week instead of today's date. What is the most likely reason?

medium

A. Excel does not support TODAY() function

B. TODAY() function requires arguments to update

C. The cell is formatted as text

D. The computer's system date is set incorrectly

Solution

1. Step 1: Check function behavior

   TODAY() updates automatically to current date based on system clock.

2. Step 2: Analyze possible causes

   If date is old, system date is likely incorrect; TODAY() needs no arguments and Excel supports it.

3. Final Answer:

   The computer's system date is set incorrectly -> Option D

4. Quick Check:

   TODAY() depends on system date [OK]

Hint: Check your computer's date if TODAY() shows wrong date [OK]

Common Mistakes:

• Thinking TODAY() needs arguments
• Assuming Excel doesn't support TODAY()
• Not checking cell formatting or system date

5. You want to create a spreadsheet that shows the current date and time in cell A1 and automatically updates every time the sheet recalculates. Which formula should you use, and why?

hard

A. =TODAY() because it updates date and time automatically

B. =NOW() because it updates both date and time automatically

C. =TODAY() + NOW() to combine date and time

D. =NOW() but it only updates when you open the file

Solution

1. Step 1: Identify function that updates date and time

   NOW() returns current date and time and updates on recalculation.

2. Step 2: Evaluate other options

   TODAY() returns only date; adding TODAY() + NOW() is incorrect; NOW() updates on recalculation, not just file open.

3. Final Answer:

   =NOW() because it updates both date and time automatically -> Option B

4. Quick Check:

   NOW() updates date and time on recalculation [OK]

Hint: Use NOW() for auto-updating date and time [OK]

Common Mistakes:

• Using TODAY() expecting time to update
• Adding TODAY() and NOW() incorrectly
• Thinking NOW() updates only on file open