Level 4 · Month keys and anniversaries with TEXT and EDATE

Timothy’s email goes out on each customer’s anniversary, and his report groups customers by the month they joined. Both are date problems, and both get easier once you know what a date really is in a spreadsheet.

A date is a number

Excel stores a date as the number of days since the start of 1900. Day 1 is January 1, 1900, and every day after adds one. D2 shows 2024-02-14, but the value in the cell is 45336. The yyyy-mm-dd you see is a format laid on top of that number. Change the cell’s format to General and you see the number itself.

Because dates are numbers, arithmetic works on them: =D2+1 is the next day, and =D3-D2 is the number of days between two dates. Times are fractions of a day, so noon is 0.5. Google Sheets uses the same numbers.

To pull one part out of a date, use the part functions:

Formula	Result for D2 (2024-02-14)
=YEAR(D2)	2024
=MONTH(D2)	2
=DAY(D2)	14
=WEEKDAY(D2,2)	3 (Monday = 1, so Wednesday)

A month key with TEXT

To group customers by month you want one value per month that sorts in time order. Gluing the parts is the tempting way:

=YEAR(D2)&"-"&MONTH(D2)

It returns 2024-2. Next to 2024-11 that is a problem: sorted as text, 2024-11 comes before 2024-2, because the character 1 comes before 2. Use TEXT with a format instead:

E2   =TEXT(D2,"yyyy-mm")

It returns 2024-02, with the leading zero, so keys always sort in calendar order. Remember that TEXT returns text: E2 is the characters 2024-02, not a date. That is fine for grouping and labels, but you can’t add days to it.

Anniversaries with EDATE

EDATE(start, months) returns the date a number of whole months later (or earlier, with a negative number):

F2   =EDATE(D2,$I$1)

I1 holds 12, so F2 shows 2025-02-14. The $ keeps every row on I1 when you fill down.

Why not =D2+365? Because years are not all 365 days. February 2024 had 29 days, so for the signups from February 14 to 28, adding 365 lands one day early: D2+365 is 2025-02-13. EDATE counts months, not days.

Look at rows 20 and 21: Abigail Gomez and Alexander Cooper signed up on 2024-02-29. There is no February 29 in 2025, so EDATE returns the last day of February, 2025-02-28. That is the rule your email tool should follow too.

Its sibling EOMONTH(start, months) returns the last day of a month: =EOMONTH(D2,0) is 2024-02-29, the end of the signup month, and =EOMONTH(D2,-1)+1 is the first day of it.

Common mistakes

• MONTH alone as a key. MONTH(D2) is 2 for February of every year. Across several years, always include the year.
• Keys without a leading zero. 2024-2 and 2024-11 sort in the wrong order. TEXT with "yyyy-mm" pads it.
• +365 for “one year later”. Off by one around every leap day. Use EDATE.
• Treating the key as a date. TEXT returns text. To keep a real date for the month, use =EOMONTH(D2,-1)+1 and format it as yyyy-mm.