Home > Topics > Fundamentals of Excel > Working with Date and Time
← PreviousNext →

Working with Date and Time

In the world of commerce, dates and times are everywhere—from invoice deadlines to interest calculations and employee attendance. Excel handles dates and times as numeric serial numbers, which allows you to perform mathematical operations on them.

1. How Excel Stores Dates and Times

It is important to understand that Excel does not "see" a date like a human does.

  • Dates: Excel stores each date as a serial number. $1$ represents January 1, 1900. Every day after that adds 1.
  • Times: Times are stored as decimal fractions of a day. For example, $0.5$ represents 12:00 PM (half a day).

2. Essential Date Functions

These functions help you retrieve or build dates automatically.

FunctionSyntaxDescription
TODAY()=TODAY()Returns the current system date. It updates every time you open the file.
NOW()=NOW()Returns the current date AND exact time.
DATE=DATE(year, month, day)Combines three separate numbers into one valid Excel date.
DAY, MONTH, YEAR=MONTH(serial_number)Extracts the specific part (day, month, or year) from a date cell.
Practical Pro Tip

If you want to quickly insert today's date that does not change later, use the keyboard shortcut Ctrl + ; (Semicolon).

3. Date Arithmetic (Math with Dates)

Since dates are numbers, you can add or subtract them.

Loading comparison…

4. Time Management Functions

  • HOUR, MINUTE, SECOND: Similar to date parts, these extract units of time from a time cell.
  • TIME(h, m, s): Combines three numbers into a time format.

Exam Pattern Questions and Answers

Question 1: "Explain how Excel stores Date and Time internally. Why is this system useful?" (4 Marks)

Answer: Excel stores Dates as sequential serial numbers where the integer 1 represents January 1, 1900. For example, January 1, 2024, is stored as 45292. Times are stored as decimal fractions of a day (e.g., 6:00 AM is stored as 0.25).

Usefulness: This numeric system is incredibly useful because it allows for Date Arithmetic. Because dates are numbers, a business can easily calculate:

  1. The number of days between two dates (Delivery Date - Order Date).
  2. The exact age of an employee (Today's Date - Birth Date).
  3. Maturity dates for loans by simply adding a number of days to a starting date.

Question 2: "Difference between TODAY() and NOW() functions with examples." (4 Marks)

Answer:

  1. TODAY(): This function returns only the current system date. It does not include time.
    • Example: If used on Jan 1st, it shows 01-01-2026.
    • Use Case: Used in headers of reports, or to find the current age.
  2. NOW(): This function returns both the current date and the current system time.
    • Example: It might show 01-01-2026 14:30.
    • Use Case: Used for time-stamping entries, such as recording exactly when an order was processed or a shipment was received.

Both functions are volatile, meaning they automatically update every time the worksheet is recalculated or reopened.

Summary

  • Excel uses Serial Numbers for dates and Decimals for time.
  • TODAY() = Date only; NOW() = Date + Time.
  • =B2 - A2 gives the number of days between two dates.
  • Use YEAR, MONTH, DAY to break a date into its parts.
Exam Success Tip

In practical exams, ensure the cell is formatted as 'Short Date' or 'Long Date' after using date math. Sometimes Excel shows a number like 45000—this is the correct value, just in the wrong format!

Quiz Time! 🎯

Loading quiz…

← PreviousNext →