Build dates, count the days between them and work with today's date without surprises.
6 articles
To calculate age in Excel, use =DATEDIF(B2,TODAY(),"Y"), where B2 is the birth date. It counts complete years, so the age only goes up on the birthday. Years of service work the same way with the hire date. For a report that must not change, replace TODAY() with a fixed "as of" date cell.
NETWORKDAYS counts the working days (Monday to Friday) between two dates, including both dates: =NETWORKDAYS(start_date, end_date, [holidays]). WORKDAY does the reverse and returns the date a number of working days away: =WORKDAY(start_date, days, [holidays]). Both skip weekends, and both skip any dates you list as holidays.
The Excel DATE function builds a real date from three numbers: =DATE(year, month, day), always in that order. YEAR, MONTH and DAY do the opposite and pull one part out of a date, like =YEAR(B2). Because Excel stores dates as serial numbers, the results can be added, subtracted and compared.
To count the days between two dates in Excel, subtract the earlier date from the later one: =C2-B2. DAYS does the same with the end date first: =DAYS(C2,B2). For whole months or years, use DATEDIF with the start date first and a unit in quotes, like =DATEDIF(B2,C2,"Y").
EDATE moves a date by whole months: =EDATE(start_date, months) keeps the day where it can, so Mar 15 plus 12 months is Mar 15 next year. EOMONTH moves by months and then jumps to the month's last day: =EOMONTH(B2,0) is the end of B2's month. Negative months go backwards.
The Excel WEEKDAY function returns the day of the week as a number: =WEEKDAY(date) gives 1 for Sunday through 7 for Saturday. Add a return type of 2, as in =WEEKDAY(date,2), to make Monday 1 and Sunday 7. Then =WEEKDAY(date,2)>5 is TRUE only for Saturdays and Sundays.