DAY 11: Excel - Date Functions (TODAY, DATEDIF, NETWORKDAYS, EOMONTH)

Exercise 1: Calculate Employee Age Using TODAY and DATEDIF

Goal: Learn to calculate age from birthdate using TODAY() and DATEDIF functions.

Setup:

• Ensure your dataset has a "Birthdate" column (format: date, e.g., 1/15/1985)
• Create a new column header "Age" in the next available column (e.g., column E)

Step-by-Step Instructions:

1. Click on the first cell under "Age" (e.g., E2 if your data starts in row 2)
2. Type the formula: =DATEDIF(C2,TODAY(),"Y")
   • C2 is the birthdate cell (adjust if your birthdate column is different)
   • TODAY() returns today's date
   • "Y" means calculate complete years
3. Press Enter to execute the formula
4. Copy the formula down: Double-click the fill handle (small square at bottom-right of cell) or drag it down to copy to all employee rows
5. Verify the results: Check that ages make sense (e.g., someone born in 1985 should be around 38-40 years old in 2024/2025)

💡 Tips:

• DATEDIF is hidden: Excel doesn't show DATEDIF in formula suggestions, but it works perfectly - just type it manually
• Date format matters: Make sure your Birthdate column is formatted as Date, not text (use Ctrl+1 to check format)
• TODAY() updates daily: This function automatically updates when you open the file, so ages will always be current
• Unit codes: "Y" = years, "M" = months, "D" = days - you can use different units as needed
• Keyboard shortcut: Ctrl+; inserts today's date as a static value (won't update)
• Error check: If you see #NUM! error, check that the birthdate is before today's date

✅ Expected Result: A column showing each employee's current age in years (e.g., 35, 42, 28, etc.)

Exercise 2: Calculate Years of Service from Hire Date

Goal: Learn to calculate tenure (length of employment) using DATEDIF.

Step-by-Step Instructions:

1. Create a new column header "Years of Service" (e.g., column F)
2. Click on cell F2 (first data row)
3. Type the formula: =DATEDIF(D2,TODAY(),"Y")
   • D2 is the hire date cell (adjust to match your column)
   • This calculates complete years from hire date to today
4. Press Enter and copy down to all employees (double-click fill handle)
5. Verify: Someone hired in 2018 should show approximately 6-7 years of service

💡 Tips:

• Same function, different purpose: DATEDIF works for any date difference - age, tenure, project duration, etc.
• Whole years only: Using "Y" gives complete years (5.8 years shows as 5)
• Quick verification: Sort by Years of Service (descending) to see your longest-tenured employees first

✅ Expected Result: A column showing tenure in complete years (e.g., 5, 12, 2, etc.)

Exercise 3: Create "Years.Months" Format for Precise Tenure

Goal: Display tenure in a more precise format like "5.7 years" or "5 years 7 months".

Step-by-Step Instructions:

1. Create a new column header "Tenure (Detailed)" (e.g., column G)
2. Method 1 - Decimal format (5.7 years):

   =DATEDIF(D2,TODAY(),"Y") & "." & DATEDIF(D2,TODAY(),"YM") & " years"

   • DATEDIF(D2,TODAY(),"Y") = complete years
   • & = concatenate (join text)
   • DATEDIF(D2,TODAY(),"YM") = remaining months after complete years
   • & " years" = adds the word "years" at the end
3. Press Enter and copy down
4. Method 2 - Full text format (5 years 7 months):

   =DATEDIF(D2,TODAY(),"Y") & " years " & DATEDIF(D2,TODAY(),"YM") & " months"

   This creates more readable text like "5 years 7 months"
5. Method 3 - With IF for singular/plural (advanced):

   =DATEDIF(D2,TODAY(),"Y") & IF(DATEDIF(D2,TODAY(),"Y")=1," year "," years ") & DATEDIF(D2,TODAY(),"YM") & IF(DATEDIF(D2,TODAY(),"YM")=1," month"," months")

   This handles "1 year" vs "2 years" correctly

💡 Tips:

• "YM" unit: Returns months between dates, ignoring years - perfect for the "remainder" months
• Text concatenation: Use & to join text, numbers, and formula results
• Formatting flexibility: Choose the format that best suits your report (decimal for calculations, text for readability)
• Alternative approach: You could calculate total months and divide by 12: =DATEDIF(D2,TODAY(),"M")/12 for decimal years
• Professional tip: For HR reports, "5 years 7 months" is more professional than "5.7 years"

✅ Expected Result: Text like "5.7 years" or "5 years 7 months" showing precise tenure for each employee

Exercise 4: Identify Employees with 10+ Years Tenure

Goal: Use IF function with date calculations to flag long-tenured employees.

Step-by-Step Instructions:

1. Create a new column header "10+ Years?" (e.g., column H)
2. Type the formula:

   =IF(DATEDIF(D2,TODAY(),"Y")>=10,"Yes","No")

   • Checks if years of service is 10 or more
   • Returns "Yes" if true, "No" if false
3. Press Enter and copy down to all rows
4. Apply conditional formatting (optional but recommended):
   • Select the entire column of Yes/No values
   • Home tab → Conditional Formatting → Highlight Cell Rules → Text that Contains
   • Type "Yes" and choose green fill
5. Count the total: In a cell below your data, type:

   =COUNTIF(H:H,"Yes")

   This counts how many employees have 10+ years tenure

💡 Tips:

• Combining functions: DATEDIF inside IF is a powerful combination for date-based decisions
• Change the threshold: Easily adjust "10" to any other number (5, 15, 20) for different milestones
• Alternative outputs: Instead of "Yes"/"No", you could use "Milestone" or leave blank: =IF(DATEDIF(D2,TODAY(),"Y")>=10,"Milestone","")
• Filter for analysis: Use AutoFilter (Ctrl+Shift+L) to quickly see only "Yes" employees
• Advanced variation: Use IFS for multiple milestones: =IFS(tenure>=20,"20yr", tenure>=10,"10yr", tenure>=5,"5yr", TRUE,"<5yr")

✅ Expected Result: A column with "Yes" or "No" for each employee, with "Yes" appearing for those with 10 or more years of service

Exercise 5: Calculate Average Employee Age by Department

Goal: Learn to combine date calculations with AVERAGEIF for grouped analysis.

Step-by-Step Instructions:

1. First, ensure you have the Age column calculated from Exercise 1 (e.g., column E)
2. Create a summary table off to the side or below your data:
   • Column 1 header: "Department"
   • Column 2 header: "Average Age"
   • List your departments: Sales, Marketing, IT, HR, Finance, etc. (one per row)
3. In the Average Age column (next to "Sales"), type:

   =AVERAGEIF($E$2:$E$100,$J2,$E$2:$E$100)

   Wait, that's wrong! Let me correct:

   =AVERAGEIF($F$2:$F$100,J2,$E$2:$E$100)

   • $F$2:$F$100 = Department column range (use $ for absolute reference)
   • J2 = The department name you're checking (e.g., "Sales")
   • $E$2:$E$100 = Age column range to average
   • Adjust ranges to match your data size
4. Press Enter and copy down to all department rows
5. Format the results: Select the average age cells → Right-click → Format Cells → Number → 1 decimal place
6. Add a grand average: Below your summary table:

   =AVERAGE(E2:E100)

   This shows the overall company average age

💡 Tips:

• Dollar signs matter: Use $F$2:$F$100 (absolute reference) so the range doesn't change when you copy down
• Range size: Adjust "100" to match your actual data size, or use dynamic ranges like $F:$F (entire column)
• Alternative approach: Use a Pivot Table (Day 7) - insert Pivot Table, Rows=Department, Values=Average of Age
• Visualization: Create a bar chart to show average age by department visually
• Advanced variation: Also calculate average tenure by department using the same technique
• Data validation: If departments aren't consistent (e.g., "IT" vs "Information Technology"), use Find & Replace first

✅ Expected Result: A summary table showing each department with its average employee age (e.g., Sales: 37.5 years, Marketing: 32.8 years, etc.)

🔄 Connection to SQL: This Excel AVERAGEIF is similar to SQL's SELECT Department, AVG(Age) FROM Employees GROUP BY Department from Day 8!

Bonus Exercise: Project Timeline Calculator with WORKDAY

Goal: Learn to calculate business days (excluding weekends) using WORKDAY and NETWORKDAYS functions.

Setup:

• Create a small project tracker with columns: ProjectName, StartDate, TargetDays (e.g., 90)
• Optional: Create a holiday list in a separate area (dates of company holidays)

Step-by-Step Instructions:

1. Calculate project end date (90 working days later):

   =WORKDAY(B2,C2)

   • B2 = Start date
   • C2 = Number of working days (e.g., 90)
   • WORKDAY automatically skips weekends
2. Include company holidays:

   =WORKDAY(B2,C2,$G$2:$G$10)

   • Third parameter = range of holiday dates
   • WORKDAY now skips weekends AND holidays
3. Calculate actual working days elapsed so far:

   =NETWORKDAYS(B2,TODAY(),$G$2:$G$10)

   • Returns number of working days from start date to today
   • Also excludes weekends and holidays
4. Determine if project is on schedule:

   =IF(NETWORKDAYS(B2,TODAY(),$G$2:$G$10)<=C2,"On Track","Review Needed")

   This checks if working days used is less than or equal to target
5. Calculate percentage complete:

   =NETWORKDAYS(B2,TODAY(),$G$2:$G$10)/C2

   Format as percentage (Ctrl+Shift+%)

💡 Tips:

• WORKDAY vs NETWORKDAYS: WORKDAY calculates a future date; NETWORKDAYS counts days between two dates
• International versions: Use WORKDAY.INTL to specify custom weekends (e.g., Friday-Saturday in some countries)
• Holiday list: Put holidays in a separate sheet for cleaner organization
• Common use cases: Project timelines, SLA tracking, delivery date calculations, working days until deadline
• Error handling: If you get #VALUE! error, check that dates are formatted correctly
• Real-world tip: Always account for holidays - projects that ignore holidays often miss deadlines

✅ Expected Result: A project end date that falls on a weekday (skipping weekends), working days elapsed count, and on-track status