Date Examples 1

Please purchase the course before starting the lesson.


We need to determine how long our employees have worked for us in Days, Months and Years

  • In D7 column work out the number days worked by the employee using D5 as the report date and C column as the employment start date. This should just be a arithmetic calculation
  • In E7 column calculate the number of years worked by dividing the days of service in column D by 365
  • In column G, use the DATEDIF formula to work out the number of months
  • In column H, use the DATEDIF formula to work out the number of years.


Have a look at the formula for the start of month. Figure out why it works.

Using the blue reference cell, create the following in the appropriate yellow cells:

  • End of Month (last day of the month)
  • Number of days in the month
  • Number of Net Working days in the month
  • The % of networking days to days in the month
  • The networking days assuming that Friday and Saturday are the weekend (only possible in Excel 2010 and above).


The date in column B contains a 1 in front of the date i.e. 1130605 should be 2013/06/05. You need to create a new column that turns this into a valid date. In E5 to I160 use formula to turn this into a proper Excel date.

To see the full course contents click here.

Back to: Advanced Excel Course > Date functions and issues