Knowledge base / Excel

DATE in Excel: components, rollover and month boundaries

Build a date from three numbers and recognise when automatic rollover helps a formula or conceals an import problem.

Kajo RudzińskiPublished Updated 5 min read

Three streams of blocks join in a machine producing a calendar card

You have year, month and day in separate columns and need a value you can sort, compare and calculate with. In Excel, I use DATE to assemble those components.

The main trap is simple: DATE can carry an overflowing day or month into another period. A plausible date result therefore does not prove that the input was valid. The small synthetic examples below follow Microsoft’s documented rules.

DATE syntax and its Polish equivalent

The DATE documentation specifies three arguments:

=DATE(year,month,day)

The Polish equivalent is:

=DATA(rok;miesiąc;dzień)

English examples here use commas between arguments, while Polish examples use semicolons. Separator settings can vary. Function names depend on the formula language used in your environment.

The function is available in Microsoft 365 and versions including Excel 2016, 2019, 2021 and 2024. I use a four-digit year. In the 1900 date system, year arguments from 0 through 1899 are interpreted by adding 1900, so 24 is not a reliable shortcut for 2024.

Assemble a date from three columns

Assume A1 says Year, B1 says Month and C1 says Day. Put the numbers 2024, 6 and 1 into the second row. In D2, enter:

=DATE(A2,B2,C2)

The result is 1 June 2024. Apply a date format to display it as intended. A numeric display does not automatically indicate an error: the function returns a date serial number.

I prefer this explicit constructor inside a formula to ambiguous text such as 01/06/2024, which different regional settings can interpret differently. Three named numeric components make the intended month and day clear.

The columns still need validation. If an export swaps the month and day, DATE can produce a perfectly plausible result. A report needs confirmation of what those columns mean rather than merely an absence of spreadsheet errors.

Overflowing days and months

These synthetic examples show results in year-month-day notation:

Formula Expected date Explanation
=DATE(2024,13,1) 2025-01-01 Month 13 carries into the following year
=DATE(2023,2,29) 2023-03-01 The extra February day carries into March
=DATE(2024,2,29) 2024-02-29 2024 is a leap year
=DATE(2024,3,0) 2024-02-29 Day zero is the day before March begins

This is documented normalisation rather than automatic rejection of an invalid input date. The former English page incorrectly said that 29 February 2023 would cause a function error. The supplied arguments actually produce 1 March.

Normalisation is useful for period boundaries. However, if an import claims a birth date of 29 February 2023, silently changing it to March would conceal the input problem. The same behaviour can help build calendars while requiring extra care during data validation.

Check whether components were carried into another period

Assume three known integers and a four-digit year within the supported range of modern dates. D2 contains =DATE(A2,B2,C2). Compare the resulting components with the input:

=AND(YEAR(D2)=A2,MONTH(D2)=B2,DAY(D2)=C2)

AND returns TRUE when all comparisons hold. For 2024, 6 and 1, the components match. For 2023, 2 and 29, normalisation changes the month and day, so the result is FALSE.

This checks component rollover within the stated scope; it is not a complete import-validation system. Blank cells, errors, fractional components and date limits need separate input rules. I also avoid treating Excel’s historical date system as a general-purpose calendar for every historical calculation.

Golden blocks move across a bridge from a filled calendar board into the next board

Build the beginning and end of a month

For a year in A2 and a month in B2, construct the first day explicitly:

=DATE(A2,B2,1)

The final day of the same month is the day before the next month begins:

=DATE(A2,B2+1,0)

With A2 equal to 2024 and B2 equal to 2, the result is 29 February 2024. Move to March, then request its day zero. There is no need to hard-code February’s length.

When filtering events containing times, it is often useful to use the beginning of the month and an exclusive boundary at the beginning of the next month. A date representing the month’s final day does not automatically include every hour of that day. Choose boundaries according to whether the source contains dates or timestamps.

Calendar days and the current date

If D2 contains a valid date, =D2+7 adds seven calendar days. =E2-D2 gives the numeric difference between two date values. It does not automatically count working days. Time components, when present, can produce a fractional difference.

TODAY() returns the current date. The correct Polish name is DZIŚ(). The former Polish text used the invalid name DZISIAJ(). =DATE(YEAR(TODAY()),1,1) constructs the beginning of the current year, so the result depends on when the workbook recalculates.

For a historical report, I prefer a separate reference-date cell. That prevents reopening a workbook next month from silently changing the analytical period. The current date is useful when a moving reference point is intentional.

Display formats and date systems

Excel supports the 1900 and 1904 date systems. Microsoft’s date-system documentation gives a difference of 1462 days between corresponding serial values. When moving raw serial numbers between workbooks, account for their settings.

I therefore avoid interpreting a serial number without knowing its date system. In ordinary reports, I use explicit constructors and inspect known dates after import. Formatting a pasted number as a date does not establish which system it originally came from.

A format controls appearance rather than the meaning of the data. Built-in date formats are a useful starting point. For custom format codes, check the language and preview instead of assuming that codes copied from another language version produce the same label.

Next step: choose dates with conditions

Once a column contains valid dates, you can compare events. MINIFS and MAXIFS select extreme dates for a city or period. DATE can supply unambiguous year boundaries for those conditions.

For practice, enter February in 2023 and then February in 2024. Predict the result of =DATE(A2,B2+1,0) and the component check for day 29. Explain why an end-of-month formula deliberately uses normalisation while import validation should detect its unintended effects. I want both uses clear before copying a formula down an entire column.

Key takeaways

Does DATE reject 29 February in a non-leap year?

No. DATE(2023,2,29) carries the extra day into March and returns 1 March 2023. The constructor does not replace input validation.

How can DATE find the final day of a month?

For a year in A2 and a month in B2, use DATE(A2,B2+1,0). Day zero of the next month is the previous month’s last day.

Why do I see a number instead of a date?

DATE returns a serial number. Apply a date format and remember that raw serial interpretation depends on the workbook’s date system.

AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.

Kajo Rudziński

Newsletter KajoData

Let’s keep in touch.

I write about data analysis, learning and changing careers. My newsletter and the sign-up form are in Polish. If that works for you, I’d be glad to see you there.