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.