Lec 03: Calendars & Date Calculations
- #excel-data-analysis
- #dates
- #calendar
- #sequence
- #date-functions
- #if-function
- #datedif
Lec 03: Calendars & Date Calculations
Part of: Excel for Data Analysis Key concepts: Date Serial Number, SEQUENCE, DATE, WEEKDAY, EOMONTH, TODAY, DATEDIF, Data Validation, Conditional Formatting
Source and scopeSource workbook:
Calendar and dates.xlsx. It reuses theemployeestable and the sorting and filtering sheets from Lec_01-Sorting, Filters & Database Functions, so those are not repeated here. What is new: theCalendarsheet (a self-building monthly calendar), theIsrael citieslist, and four newemployeescolumns: K (birthday message), L (promotion date), M (time until promotion) and N (city). The nestedIF/ANDlogic is taught properly in Lec_02-IF Statements & Logical Functions; date functions continue in Lec_04-Date Functions, Named Ranges & Data Validation and Lec_05-Time Functions & Protecting Cells.
1. Dates are just numbers
Excel stores a Date Serial Number: the count of days since an origin date (30 December 1899 in practice). Formatting is only a costume. That is why you can add 1 to a date to get tomorrow, subtract two dates to get a gap in days, and compare dates with > and <.
The workbook shows this directly. After =DATE(2024,3,1) the cell holds 45352; after =EOMONTH(...) it holds 45382. Both are the same kind of number, formatted as dates.
Why it mattersIf a "date" column is left-aligned or sorts alphabetically, it is text, not a real date, and none of the functions below will work on it. Real dates right-align and sort chronologically.
2. The date functions used in this workbook
| Function | What it returns | Example from the workbook |
|---|---|---|
| SEQUENCE | A spilled list of numbers | =SEQUENCE(12,,1,1) gives 1 to 12 |
| DATE | A real date from year, month, day | =DATE(L2,K2,1) |
| WEEKDAY | The day of the week as a number | =WEEKDAY(M2,1) |
| EOMONTH | The last day of a month, shifted by n months | =EOMONTH(M2,0) |
| TODAY | Today's date (recalculates every time the file opens) | =TODAY() |
MONTH, YEAR |
Pull one part out of a date | MONTH(F2) |
| DATEDIF | Whole years, months or days between two dates | DATEDIF(TODAY(),L2,"y") |
WEEKDAY and its return type
WEEKDAY(date, return_type) numbers the days. The second argument chooses the scheme:
| Return type | Numbering | Used in |
|---|---|---|
1 (default) |
Sunday = 1 ... Saturday = 7 | Calendar sheet |
2 |
Monday = 1 ... Sunday = 7 | Promotion rule in column L |
Same function, different scheme, so always state the second argument. In the calendar the class labels row 5 as 1 to 7 and row 6 as Sunday to Saturday, matching type 1.
EOMONTH
EOMONTH(start_date, months) returns the last day of the month that is months away from start_date. With 0 it is the end of the same month, with 2 it is the end of the month two months later. It is the clean way to get "end of month" without knowing whether the month has 28, 29, 30 or 31 days.
DATEDIF (hidden function)
DATEDIF(start, end, unit) does not appear in the function wizard but works. Units used here: "y" complete years, "md" days left over after removing complete months and years. The start date must be earlier than the end date, otherwise #NUM!. That is exactly why the workbook guards it with IF(L2>TODAY(), ... ).
Common mistakes
- Swapping start and end in
DATEDIF(error#NUM!).- Using
"md"alone and expecting total days. It gives only the leftover days within the month. For total days, subtract the dates.- Forgetting that
TODAY()is volatile: results change every day, and a saved file shows values from the last time it was calculated.
3. The self-building calendar (Calendar sheet)
Goal: pick a month and a year from drop-downs and get a calendar grid for that month.
3.1 The lists and drop-downs
| Cell | Formula | Result |
|---|---|---|
B2 |
=SEQUENCE(12,,1,1) |
Spills months 1 to 12 down B2:B13 |
C2 |
=SEQUENCE(31,1,2020,1) |
Spills years 2020 to 2050 down C2:C32 |
SEQUENCE(rows, columns, start, step): the class used 12 rows starting at 1 and 31 rows starting at 2020, both stepping by 1. In the first formula the columns argument is left empty, which means 1.
Why SEQUENCEIt replaces typing 1 to 12 by hand or dragging a fill handle. Change one argument and the whole list updates. It is a dynamic array: one formula in the top cell fills the cells below (the spill range).
The two drop-downs are Data Validation lists (type list):
| Cell | Allowed values | Source |
|---|---|---|
K2 (Month) |
1 to 12 | $B$2:$B$13 |
L2 (Year) |
2020 to 2050 | $C$2:$C$32 |
The class chose March 2024 (K2 = 3, L2 = 2024).
3.2 Three helper cells
| Cell | Label | Formula | Value (March 2024) |
|---|---|---|---|
M2 |
1st of chosen date | =DATE(L2,K2,1) |
1 March 2024 (45352) |
N2 |
day of week of 1st | =WEEKDAY(M2,1) |
6 (Friday) |
O2 |
end of month | =EOMONTH(M2,0) |
31 March 2024 (45382) |
Everything in the grid depends on just two facts: which weekday the 1st falls on (N2) and how many days the month has (O2).
3.3 The grid: filling days into the right columns
Header rows: K5:Q5 hold 1 to 7 (weekday numbers) and K6:Q6 hold Sunday to Saturday. The day cells are K7:Q12, six rows of seven columns (six weeks is the most any month can span).
Three formulas build it.
Top-left cell (K7, Sunday column of the first week):
=IF(N2=K5,1,"")
If the 1st is a Sunday (N2 equals the Sunday number in K5), start with 1, otherwise leave blank.
Other cells in the first week (L7 to Q7), filled across:
=IF(K7="",IF(L5=$N$2,1,""),K7+1)
Read it as: if the cell to my left is blank, then either start counting here (return 1 if my column's weekday number equals the 1st's weekday, $N$2) or stay blank. If the cell to my left already has a number, add 1 to it.
First cell of later weeks (K8, K9 ...), filled down:
=IF(Q7="",IF(K6=$N$2,1,""),Q7+1)
Take the last day of the previous week (end of the row above, column Q) and add 1. The Q7="" test is a safety net for a month that has not started yet.
The same left-neighbour-plus-1 formula as L7 is then copied across all remaining cells (L8:Q12, L9:Q9, and so on).
March 2024 resultThe 1st is a Friday (
N2= 6), soK7toO7stay blank and the first week starts inP7.
Sunday Monday Tuesday Wednesday Thursday Friday Saturday 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 This table is the correct March 2024 calendar; the row of 31 is what the grid shows once the overflow is hidden (next section).
3.4 Hiding the overflow days with conditional formatting
The formulas keep counting after the month ends. In the saved workbook the last row reads 31, 32, 33 ... 37. The class hides these with Conditional Formatting on K7:Q12:
=K7>DAY($O$2)
The format applied is a white font, so any day number greater than the last day of the month (DAY($O$2) = 31 for March) vanishes into the background. $O$2 is locked with Absolute Reference anchors because the rule is evaluated against every cell in the range, while K7 stays relative so it shifts per cell.
Hidden is not deletedThe overflow numbers are still in the cells, only coloured white. Copying the grid elsewhere will bring them back. A cleaner version would wrap the formula in an extra
IF(...>DAY($O$2),"",...).
Reconstructed from the workbookThe workbook contains no written explanation of the calendar. The step-by-step reading above comes from the formulas, the drop-down rules and the conditional-format rule found in the file. I also noticed that in
K8the inner testK6=$N$2points at a day-name label rather than a weekday number, but it is never reached becauseQ7is never blank, so the result is unaffected.
4. New employee columns (K to N)
The employees sheet keeps the ten columns from Lec_01-Sorting, Filters & Database Functions (ID, names, branch, department, birth date, hire date, seniority, position, salary) and adds four.
| Column | Header | What it holds |
|---|---|---|
| K | Happy Bday | A message for employees born this month |
| L | Promotion date | Date the employee gets promoted |
| M | time until promotion | Text such as "0 years and 9 days" or "Already promoted" |
| N | City | The employee's city (values typed in, drawn from Israel cities) |
4.1 Column K: birthday message
The written task (cell K204): if employee was born this month, write happy bday.
=IF(MONTH(F2)=MONTH(TODAY()),"Happy Birthday man","")
MONTH(F2)is the birth month;MONTH(TODAY())is the current month.- Equal means a birthday month, so the message appears; otherwise
""leaves the cell empty. - Birth year is ignored on purpose: only the month matters, and that is why we compare months and not whole dates.
It tests the month, not the dayEveryone born in the current month gets the message for the whole month, including people whose birthday has already passed. Compare
DAYtoo if you need an exact day.
In the saved file the message appears for December births (for example employee in row 39, born 23 December 1984), which shows the file was last calculated in December 2024.
4.2 Column L: the promotion rule
The written task (cells L204 and L205), cleaned up:
Promotion rule
- If the employee started on a Monday and in March, they are promoted on 1 July, two years after the hire year.
- Everyone else is promoted on the end of the month that falls two months after their hire month, in 2024.
The formula in L2:
=IF(AND(WEEKDAY(G2,2)=1,MONTH(G2)=3),DATE(YEAR(G2)+2,7,1),EOMONTH(DATE(2024,MONTH(G2)+2,1),0))
The class wrote its own breakdown beside it (cells L206 to L209). Here it is, expanded.
| Piece | What it does |
|---|---|
WEEKDAY(G2,2)=1 |
True when the hire date is a Monday (type 2 makes Monday = 1) |
MONTH(G2)=3 |
True when the hire month is March |
AND(..., ...) |
Both conditions must hold at once |
DATE(YEAR(G2)+2,7,1) |
1 July, two years after the hire year (the special-case result) |
DATE(2024,MONTH(G2)+2,1) |
The 1st of the month two months after the hire month, placed in 2024 |
EOMONTH(...,0) |
Moves that 1st to the last day of its month |
Putting it together: IF(test, value_if_true, value_if_false), where the test is the AND, the true result is the July date, and the false result is the end-of-month date.
Month overflow is a featureFor a hire month of 11 or 12,
MONTH(G2)+2becomes 13 or 14.DATEdoes not error: it rolls over into the next year, soDATE(2024,13,1)is 1 January 2025 andDATE(2024,14,1)is 1 February 2025.EOMONTHthen returns 31 January 2025 and 28 February 2025. The saved results confirm this (serials 45688 and 45716).
Worked rows, copied from the workbook (hire dates are real data; serial numbers converted to dates):
| Row | Employee ID | Hire date | Weekday | Promotion date (L) |
|---|---|---|---|---|
| 2 | 10001 | 18 Oct 2002 | Friday | 31 Dec 2024 |
| 3 | 10002 | 17 Oct 2008 | Friday | 31 Dec 2024 |
| 4 | 10003 | 18 Oct 2011 | Tuesday | 31 Dec 2024 |
| 5 | 10004 | 5 Jan 2005 | Wednesday | 31 Mar 2024 |
| 7 | 10006 | 27 May 2004 | Thursday | 31 Jul 2024 |
| 108 | 10107 | 21 Mar 1994 | Monday | 1 Jul 1996 |
| 168 | 10167 | 31 Mar 2008 | Monday | 1 Jul 2010 |
Rows 108 and 168 are the only two in the 200 employees that satisfy "Monday and March", and they show the special rule: the date is 1 July two years after hire (so 1996 and 2010, long in the past). All 200 stored promotion dates match this rule when recomputed.
Things to watch
2024is typed straight into the formula. The rule says "in 2024", so this is intended, but it is a hard-coded value: change the target year and you must edit every formula (a cell reference would be better).- The two branches have different logic: the first uses the hire year, the second ignores it and always uses 2024. That is why most promotion dates fall in 2024 regardless of when the person was hired.
WEEKDAY(G2,2)=1needs the 2. With the default type 1, Monday is 2 and the rule would silently pick Sundays.
4.3 Column M: time until promotion
=IF(L2>TODAY(),DATEDIF(TODAY(),L2,"y")&" years and "&DATEDIF(TODAY(),L2,"md")&" days","Already promoted")
How it works:
L2>TODAY()checks the promotion date is still in the future.- If so, build a text string with
&: complete years from today to the promotion date, the words "years and", leftover days, the word "days". - If not, show "Already promoted". This is also the guard that stops
DATEDIFgetting a start date later than its end date.
Values stored in the file include "0 years and 9 days" (promotion on 31 Dec 2024, so the file was last recalculated on 22 Dec 2024), "0 years and 6 days" (promotion on 28 Feb 2025) and "Already promoted".
Common mistakeThe output is text, not a number. You cannot sum it, sort it by time left, or filter "less than 30 days". If you need to do that, keep a numeric helper column (
=L2-TODAY()) and build the text only for display.
Reconstructed from the workbookThe class left no written note beside column M (the written tasks sit beside K and L). The explanation above is read from the formula and its stored results. Note also that the
yandmdparts sit side by side:"md"can be confusing when the gap crosses a month end, so treat the output as a readable estimate, not an exact count of days.
4.4 Column N and the Israel cities sheet
The Israel cities sheet is a single column headed Cities with 43 city names (Afula, Akko (Acre), Arad ... Yehud), carrying an AutoFilter. Column N of employees holds a city for the first 15 employees (N2 to N16), for example Rishon LeZion, Herzliya, Jerusalem, Akko (Acre), Ashdod.
Reconstructed from the workbookThe file has no formula or drop-down rule on column N, only the typed values, so the cities are plain data. The list on
Israel citiesis clearly the master list they were chosen from (a natural fit for a Data Validation drop-down, which is covered in Lec_04-Date Functions, Named Ranges & Data Validation). Only 15 of 200 rows are filled.
5. Other formatting in the employees sheet
These come over from the earlier sheet and are worth recognising:
| Range | Rule |
|---|---|
A2:J201 |
Highlight the whole row when department equals the cell E204 (=$E2=$E$204), where E204 reads "computing" |
H2:H201 |
Seniority greater than 24 is highlighted (cell H204 "sen over 25") |
J2:J201 |
Salary banding: under 5000, between 5000 and 10000, over 10000, each a different colour |
The department rule uses a mixed reference: column locked ($E), row free, so each row compares its own department to the one fixed cell $E$204.
6. Summary
- Dates are serial numbers, so arithmetic and comparison work on them; always use
DATE, not typed text. SEQUENCEbuilds number lists,DATEassembles a date,WEEKDAYfinds the weekday (mind the return type),EOMONTHfinds month end,TODAYgives the current date,DATEDIFmeasures gaps.- The calendar needs only two facts, the weekday of the 1st and the last day of the month. Each cell is either blank, the starting 1, or its left neighbour plus 1; conditional formatting hides days past month end.
- Promotion rule: Monday-and-March hires get 1 July two years after the hire year; everyone else gets the end of the month two months after the hire month, in 2024. Month numbers over 12 roll into the next year.
- Guard date calculations with
IF(for example, only runDATEDIFwhen the end date is in the future).