Lab Manual: Practical 8 – Mastering Time Formulas

1. Objective

The primary objective of this practical is to equip students with the knowledge and skills to effectively use Excel’s date and time functions. Students will learn to:

·         Insert current dates and times automatically using TODAY() and NOW().

·         Extract specific components (year, month, day) from a date using YEAR(), MONTH(), and DAY().

·         Calculate the difference between two dates in various units (years, months, days) using DATEDIF().

·         Understand Excel’s serial number system for dates and times.

·         Apply appropriate number formatting for date and time values for clarity.

2. Introduction

Dates and times are fundamental components of many datasets, from financial records to project schedules and student demographics. Excel provides a powerful set of functions to work with these values, allowing for calculations like age, tenure, duration, and extracting specific date parts for reporting. Understanding how Excel stores and manipulates dates (as serial numbers) is key to mastering these functions. This practical will guide you through these essential time formulas, building on your formatting knowledge from Practical 1.

3. Pre-requisites

·         Basic understanding of Microsoft Excel interface.

·         Familiarity with basic formula entry and number formatting.

·         Ability to open, save, and navigate Excel worksheets.

4. Software Required

·         Microsoft Excel

5. Procedure: Practical Steps with Examples

We will start with a fresh worksheet to practice these formulas.

Step 5.1: Opening a New Workbook and Setting Up the Worksheet

1.     Open Excel: Launch Microsoft Excel and select Blank workbook.

2.     Rename Sheet: Double-click on the Sheet1 tab and rename it to Time Formulas Practice.

3.     Add Labels: In cells A1 through A10, type the following labels:

o    A1: Current Date

o    A2: Current Date & Time

o    A4: My Birth Date

o    A5: Today's Date

o    A6: Years Lived

o    A7: Months Lived

o    A8: Days Lived

o    A10: Date to Deconstruct

o    A11: Extracted Year

o    A12: Extracted Month

o    A13: Extracted Day

Step 5.2: Using TODAY() and NOW()

These functions return the current date or current date and time. They are volatile, meaning they update every time the workbook is opened or a calculation is performed.

1.     Insert TODAY(): In cell B1 (next to Current Date), type =TODAY() and press Enter.

o    Observation: The cell will display the current date. Ensure it is formatted as a date (Home tab > Number group > Short Date).

2.     Insert NOW(): In cell B2 (next to Current Date & Time), type =NOW() and press Enter.

o    Observation: The cell will display the current date and time. Ensure it is formatted as Date or Time (Home tab > Number group). You may need to select More Number Formats... to choose a format that includes both date and time.

Step 5.3: Using DATEDIF() to Calculate Duration

The DATEDIF() function calculates the number of days, months, or years between two dates. It’s often used for calculating age or tenure. The syntax is DATEDIF(start_date, end_date, unit).

1.     Enter Sample Dates:

o    In cell B4 (next to My Birth Date), enter a birth date (e.g., 15/08/2000).

o    In cell B5 (next to Today's Date), type =TODAY() and press Enter.

2.     Calculate Years Lived ("Y" unit): In cell B6 (next to Years Lived), type =DATEDIF(B4,B5,"Y") and press Enter.

o    Explanation: B4 is the start date (birth date), B5 is the end date (today), and "Y" specifies that we want the difference in complete years.

3.     Calculate Months Lived ("M" unit): In cell B7 (next to Months Lived), type =DATEDIF(B4,B5,"M") and press Enter.

o    Explanation: "M" specifies the difference in complete months.

4.     Calculate Days Lived ("D" unit): In cell B8 (next to Days Lived), type =DATEDIF(B4,B5,"D") and press Enter.

o    Explanation: "D" specifies the difference in complete days.

5.     Bonus: Calculate Years and Remaining Months ("YM" unit):

o    In cell A9, type Remaining Months.

o    In cell B9, type =DATEDIF(B4,B5,"YM") and press Enter.

o    Explanation: "YM" calculates the number of months remaining after subtracting whole years. This is useful for displaying age as "X years and Y months".

Step 5.4: Using YEAR(), MONTH(), DAY()

These functions extract specific numerical components from a date.

1.     Enter a Date to Deconstruct: In cell B10 (next to Date to Deconstruct), enter a date (e.g., 26/10/2025).

2.     Extract Year: In cell B11 (next to Extracted Year), type =YEAR(B10) and press Enter.

o    Observation: The cell will display 2025.

3.     Extract Month: In cell B12 (next to Extracted Month), type =MONTH(B10) and press Enter.

o    Observation: The cell will display 10 (for October).

4.     Extract Day: In cell B13 (next to Extracted Day), type =DAY(B10) and press Enter.

o    Observation: The cell will display 26.

Step 5.5: Understanding Excel’s Date Serial Number System

1.     Format B1 as General: Select cell B1 (containing TODAY()). Go to the Home tab, in the Number group, and change the format from Date to General.

o    Observation: You will see a large number (e.g., 45914). This is the serial number representation of the date. Excel counts days starting from January 1, 1900.

2.     Format B2 as General: Select cell B2 (containing NOW()). Change its format to General.

o    Observation: You will see a decimal number (e.g., 45914.5). The integer part represents the date, and the decimal part represents the time as a fraction of a 24-hour day (e.g., 0.5 is 12:00 PM).

3.     Key Takeaway: All date and time calculations in Excel rely on this serial number system. Formatting just changes how these numbers are displayed.

6. Exercises / Tasks

Apply the concepts learned to complete the following tasks:

1.     Calculate Student Age:

o    On a new sheet named Student Ages, create a simple table with Student Name and Date of Birth. Populate with 3-5 students and their birth dates.

o    Add a column Current Age. Use DATEDIF() and TODAY() to calculate the age of each student in whole years.

o    Add another column Age. Display the age as "X years, Y months" (Hint: you’ll need DATEDIF for years ("Y") and another DATEDIF for remaining months ("YM"), then use concatenation with &).

2.     Project Timeline:

o    On a new sheet named Project Dates, create a table with Project Name, Start Date, and End Date. Populate with 3 projects.

o    Add a column Project Duration. Calculate the total number of days for each project using DATEDIF().

o    Add a column Remaining Days. If the End Date is in the future, calculate how many days are left until completion using TODAY(). If the End Date is in the past, display "Completed". (Hint: This will require an IF statement combined with TODAY() and DATEDIF()).

3.     Event Countdown:

o    In a cell, enter a future event date (e.g., Your Exam Date).

o    In an adjacent cell, display "Days until Exam: X" where X is the number of full days remaining until that date. Use TODAY() and the date functions.

4.     Extracting from Past Dates:

o    Create a list of 5 historical dates (e.g., Independence Day, Republic Day of different years).

o    For each date, use YEAR(), MONTH(), and DAY() functions to extract and display its year, month, and day in separate columns.

7. Assessment / Deliverables

·         Submit the completed Excel workbook (.xlsx file) with the Time Formulas Practice sheet and the solution sheets for Student Ages and Project Dates.

·         Ensure all functions are correctly implemented and yield accurate results.

·         Verify that date and time values are formatted appropriately for readability.

·         Be prepared to explain the purpose and application of each function used, especially the DATEDIF() function with its different units.