Excel Date and Time Basics Practice Questions with Solutions

Introduction

Dates and times are used in almost every Excel worksheet, from employee records and attendance sheets to sales reports and project schedules. Excel provides several functions for working with dates and times, including TODAY, NOW, DATE, DAY, MONTH, YEAR, TIME, HOUR, MINUTE, and SECOND. In this chapter, you will practice these functions through 10 different questions covering the basic date and time concepts you need before moving to more advanced date calculations. Excel Date and Time Basics practice questions with solutions to help you understand the concepts.

Question 1: Display Today’s Date

Problem Statement

Create a worksheet that automatically displays the current date.

Excel Data

Solution

In B2, enter:

=TODAY()

Expected Output

The cell will display the current date according to your computer’s date settings.

For example:

The result changes automatically when Excel recalculates on a different day.

Question 2: Display the Current Date and Time

Problem Statement

Display the current date along with the current time.

Excel Data

Solution

In B2, enter:

=NOW()

Expected Output

You may see a result similar to:

The exact time will depend on when the formula is calculated.

NOW() returns both the current date and current time.

Question 3: Create a Date Using DATE

Problem Statement

You have the year, month, and day stored separately. Create a proper Excel date from these three values.

Excel Data

Solution

In D2, enter:

=DATE(A2,B2,C2)

Copy the formula down.

Expected Output

DATE is useful when date components are stored in separate columns.

Question 4: Extract Day, Month and Year

Problem Statement

A list contains employee joining dates. Extract the day, month, and year into separate columns.

Excel Data

Solution

In C2, enter:

=DAY(B2)

In D2, enter:

=MONTH(B2)

In E2, enter:

=YEAR(B2)

Copy all three formulas down.

Expected Output

Question 5: Create a Time Using TIME

Problem Statement

Hours, minutes, and seconds are stored separately. Create a proper Excel time value.

Excel Data

Solution

In D2, enter:

=TIME(A2,B2,C2)

Copy down.

Expected Output

Question 6: Extract Hour, Minute and Second

Problem Statement

Extract the hour, minute, and second from each recorded time.

Excel Data

Solution

In B2, enter:

=HOUR(A2)

In C2, enter:

=MINUTE(A2)

In D2, enter:

=SECOND(A2)

Copy the formulas down.

Expected Output

Question 7: Extract the Month Name from a Date

Problem Statement

Display the full month name for each date.

Excel Data

Solution

In B2, enter:

=TEXT(A2,"mmmm")

Copy down.

Expected Output

To display the short month name, such as Jan, Feb, or Mar, use:

=TEXT(A2,"mmm")

Question 8: Extract the Day Name from a Date

Problem Statement

Find the day of the week for each date.

Excel Data

Solution

In B2, enter:

=TEXT(A2,"dddd")

Copy down.

Expected Output

To display an abbreviated day name such as Mon, Tue, or Wed, use:

=TEXT(A2,"ddd")

Question 9: Combine Date and Time

Problem Statement

An employee’s appointment date and appointment time are stored separately. Combine them into one date-time value.

Excel Data

Solution

In D2, enter:

=B2+C2

Copy down.

Format column D as:

dd/mm/yyyy hh:mm

Expected Output

Excel stores dates and times as numeric values, which is why they can be added together.

Question 10: Create a Date-Time Record from Separate Values

Problem Statement

You have separate columns for year, month, day, hour, and minute. Create a complete date and time.

Excel Data

Solution

In F2, enter:

=DATE(A2,B2,C2)+TIME(D2,E2,0)

Copy down.

Format column F as:

dd/mm/yyyy hh:mm

Expected Output

This combines DATE and TIME into a single date-time value.

Key Takeaways

• Excel stores dates as numbers and times as fractions of a day.
• TODAY() returns the current date.
• NOW() returns the current date and time.
• DATE() in excel creates a date from year, month, and day values.
• DAY() extracts the day from a date.
• MONTH() extracts the month number.
• YEAR() extracts the year.
• TIME() creates a time from hour, minute, and second values.
• HOUR() extracts the hour from a time.
• MINUTE() extracts the minute.
• SECOND() extracts the seconds.
• TEXT() can display dates as month names or day names.
• Dates and times can be combined using simple addition.
• Date and time formatting changes how a value is displayed; it does not necessarily change the underlying value.

FAQs

1. What does TODAY() do in Excel?

TODAY() returns the current date.

=TODAY()

It does not include the current time.

2. What is the difference between TODAY() and NOW()?

TODAY() returns only the current date:

=TODAY()

NOW() returns the current date and time:

=NOW()

3. How do I create a date from separate year, month and day values?

Use the DATE function:

=DATE(A2,B2,C2)

where the cells contain the year, month, and day.

4. How can I extract the month from an Excel date?

Use:

=MONTH(A2)

This returns the month number.

For the month name, use:

=TEXT(A2,"mmmm")

5. How can I find the day of the week from a date?

Use:

=TEXT(A2,"dddd")

This returns the full day name, such as Monday or Friday.

6. How can I create a time from separate hour, minute and second values?

Use:

=TIME(A2,B2,C2)

7. How can I extract the hour from a time?

Use:

=HOUR(A2)

Similarly, use MINUTE() and SECOND() for the minute and second.

8. Can Excel add a date and time together?

Yes. If the date is in B2 and the time is in C2:

=B2+C2

This creates a combined date-time value.

9. Why does Excel sometimes display a number instead of a date?

Excel stores dates as serial numbers. If the cell has General or Number formatting, you may see that underlying number instead of a formatted date.

Apply an appropriate date format to display it as a normal date.

10. Are Excel dates and times stored as text?

Normally, valid Excel dates and times are stored as numeric values. This is important because numeric dates and times can be used in calculations, sorting, filtering, and other date functions.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar