Course contents · Excel functions

Dates

Open the practice in the trainer →

To Excel a date is a number of days since 1 January 1900, displayed in a date format. That is why dates can be subtracted: =C2-A2 gives the days between them.

YEAR / MONTH / DAYpart of a date as a number
DATEDIF(start, end, "y")complete years ("y"), months ("m") or days ("d"); it is not in the Insert Function dialog, type it by hand
NETWORKDAYS(start, end, [holidays])working days (Mon–Fri), both dates included
DATE(year, month, day)builds a date from numbers — handy in criteria
TODAY()today's date, updates every day

Total for a period: =SUMIFS(B2:B9, A2:A9, ">="&DATE(2024,3,1), A2:A9, "<"&DATE(2024,4,1)) — the comparison operator in quotes is joined to the date with &.

If a date is left-aligned, Excel treats it as text: fix it with Data → Text to Columns.

Tasks in the trainer

  1. In D2:D9 extract the year from the order date.
  2. In E2:E9 — the month number.
  3. In J2 calculate the employee's complete years of service on the report date.
  4. In F2:F9 — how many working days passed from order to payment (both dates included).
  5. In I4 calculate the total of orders for March 2024 (build the dates with DATE).

Solve the tasks in the trainer →