Excel DATE: build dates and catch input mistakes

You have a year in one column, a month in another and a day in a third. You need a date that can be sorted, filtered and used in a report. I use Excel’s DATE function to combine those parts. Three arguments and one result sounds comfortably simple. The interesting part starts when someone enters the thirty-first of February and Excel quietly produces a date in March.

I will walk through both construction and validation. Keeping those jobs separate matters: a formula can calculate successfully while assigning a record to the wrong reporting period. A spreadsheet without error messages can look very reassuring. Sometimes it has simply made an unexpected interpretation of the input.

All values in this article are synthetic. The expected results follow from Microsoft’s documented rules and small calendar examples that you can trace by hand. Start in an empty worksheet. Once you can explain the normal cases and the boundary cases, move the approach into a real report with explicit rules for accepting or rejecting dates.

DATE and its Polish name, DATA

The English function is DATE; the Polish equivalent is DATA. Both take the components in the order year, month, day. With English function names and comma separators, a basic formula is:

=DATE(2026,10,27)

For typical Polish regional settings, the corresponding formula is:

=DATA(2026;10;27)

Both describe 27 October 2026. The argument separator depends on regional configuration, so translating the function name may not be the only change needed when copying a formula. If Excel rejects the expression before calculating anything, check the function language and separator before rewriting the calculation.

Microsoft’s DATE documentation explains the components and how they are normalized. I use four-digit years throughout. A short year such as 26 invites additional interpretation rules. In a report, I would rather state the intended century explicitly than leave another person to infer it from context.

Combine three columns into one date

Put the headers year, month, day and date in A1:D1. Enter 2026 in A2, 10 in B2 and 27 in C2. Then enter the following formula in D2. Its references point to the numeric values, not to the header row.

=DATE(A2,B2,C2)

With a date number format applied, D2 should display 27 October 2026. Another row can contain a different year or month. Copying the formula down changes its relative references to A3, B3 and C3. There is no need to type each full date manually or join the components with slashes.

Before copying a calculation through thousands of rows, I inspect a few recognizable cases. I want a date near the start of a month, another near its end and another in a different year. This helps expose swapped day and month columns. Day 7 and month 8 are both valid numbers, so reversing them may produce no obvious warning.

I also keep the original components during review. If the result looks suspicious, I want to see the values that produced it. Deleting the source columns immediately makes that investigation harder. Decide later whether the final presentation needs those columns; keep them available while you establish whether the transformation is reliable.

A date’s value and its appearance

A date has an underlying value and a display format. The same cell can show a month name, a short numeric date or a serial number. Changing the format gives another view of the value. A string that merely resembles a date, however, still needs to be interpreted correctly before it can support dependable calculations.

If DATE produces a number on screen, apply a date format through Format Cells. If the cell shows an expression beginning with an equals sign, check whether it was formatted as text before entry. If it shows hash marks, widen the column and inspect the value again. Diagnose the symptom before replacing the formula with a manually typed result.

Microsoft documents two date systems: 1900 and 1904. The same calendar day has serial numbers that differ by 1,462 between those systems. When handling older workbooks, I check the workbook’s setting rather than assuming that a raw date number will mean the same thing everywhere.

There is also a historical exception: Excel treats 1900 as a leap year. Microsoft explains the compatibility behavior. For a contemporary operational report, it is usually a boundary to know about rather than the main problem to solve. For historical data, it prevents a completely literal interpretation of every serial as an ordinary elapsed-day count.

Months above twelve and negative months

Consider =DATE(2016,15,4). Month 13 becomes January of the following year, 14 becomes February and 15 becomes March. The day component remains four. The result is therefore 4 March 2017. Separating the month calculation from the day calculation makes this example much easier to inspect.

Now consider =DATE(2016,-3,4). Month 1 is January 2016, month 0 is December 2015, -1 is November, -2 is October and -3 is September. The result is 4 September 2015. The zero position matters when counting backwards; skipping it shifts your mental answer by a month.

This behavior can help when constructing a schedule. You can describe the start of the next month even when the current month is December. The same behavior can conceal an import problem, though. A source month of 15 might be a typo, and the function will convert it into a plausible date instead of identifying the intended month.

I therefore distinguish a deliberate component shift from an input record that is supposed to describe an existing date. In a planning formula, an overflowing month can be intentional. In a source column that promises months from 1 to 12, it needs investigation. The purpose of the report determines the acceptance rule.

Day zero and days beyond the month

Day zero means the last day of the preceding month. Thus =DATE(2026,3,0) gives 28 February 2026. With =DATE(2024,3,0), it gives 29 February because 2024 is a leap year. You can use that property without maintaining a separate list of February lengths.

Overflow works in the other direction as well. =DATE(2026,2,31) reaches the end of February, which has 28 days, and carries three days forward. The expected date is 3 March. This is a useful construction rule, but it provides no evidence that the original entry was a legitimate birthday, document date or event date.

If the source was meant to contain an actual February date, March is not automatically an acceptable correction. Someone has to establish which component was wrong. Silently accepting the normalized result could move an observation into another reporting month and change a period comparison without any visible formula error.

In a review worksheet, I prefer a separate status for those records. Keep the original components, the calculated date and the reason for review. Another person can then see how many cases require a decision. An unresolved date with a clear explanation is often more useful than a polished result based on a guess.

The first and last day of a reporting month

Suppose A2 contains a year and B2 a month number. =DATE(A2,B2,1) constructs the first day of the period. =DATE(A2,B2+1,1) constructs the first day of the next one. This makes the boundary explicit without relying on a manually maintained list of month endings.

You can describe the last day with =DATE(A2,B2+1,0). For year 2026 and month 10, the expected date is 31 October. However, distinguish the final calendar date from a filter that includes every event in the month. If timestamps contain hours, midnight at the start of that last day does not include its later events.

For such a filter, I prefer an inclusive start and an exclusive start of the next period. Write that rule in ordinary language before translating it into a formula or query. It gives records on the boundary a clear destination and avoids having to invent a final representable moment of the day.

When starting from an existing date, you can also use EOMONTH, documented by Microsoft. I choose the expression that best communicates the rule to the people maintaining the workbook. A compact calculation is helpful only if someone can still explain the reporting boundary later.

Adding a month needs a business rule

A month later might mean the same day number, the end of the next month or a particular number of elapsed days. Those meanings diverge near the end of January. Before choosing a function, I write a sentence describing the intended behavior. This is especially useful for recurring tasks and schedules.

Microsoft provides EDATE for shifting a date by a number of months. For month-end dates, start by considering EOMONTH. Increasing the month component inside DATE follows the normalization rules already described, so I do not assume that it satisfies every possible scheduling requirement.

A useful requirement includes one ordinary case and one boundary case. What should happen to a date on the fifteenth? What should happen to a date on the last day? If two readers disagree, settle that question before copying the formula across several years. The workbook cannot infer which convention the author intended.

Subtracting two dates

Subtracting dates without times gives a difference in calendar days. A deliberately small example is =DATE(2026,10,30)-DATE(2026,10,27), whose expected result is 3. One day passes from the twenty-seventh to the twenty-eighth, then another and another. No elaborate date function is needed for that question.

I format the result as a number. If the output cell inherits a date format, Excel may display the date associated with the number 3. The calculation can be correct while the display is confusing. Checking the value and its format separately saves a surprising amount of unnecessary formula editing.

A three-day difference does not automatically mean four included calendar dates or three working days. Counting both endpoints needs an explicit convention. Excluding weekends and holidays needs a calendar rule. Name the measure first: elapsed calendar days, dates included in a period or working days under a defined schedule.

Text that looks like a date

The string 03/04/2026 needs a convention before it has a dependable meaning. One person reads 3 April; another reads March 4. With data arriving from several countries, I do not want the report’s interpretation to depend on the habits of whoever opens the workbook. Explicit components or a controlled import are safer starting points.

DATEVALUE converts a textual date representation to a date serial, and interpretation depends on settings. Applying a date format to a text column is not a complete import procedure. Establish the source convention and inspect known records before trusting the whole column.

A CSV file adds another layer: the delimiter separating columns is distinct from the convention used inside a date field. Successful import does not establish that every date was interpreted correctly. Preserve a few recognizable source examples and compare them after conversion. This provides a much better check than deciding that the first visible row looks reasonable.

Validate a date before reporting it

My first check asks whether all three components are present and are integers in the expected ranges. I also define a sensible year interval for the report. A date in 2099 is valid in a calendar and may still be entirely wrong for a list of events from last week.

Next, compare the year, month and day extracted from the constructed date with the original components. A change caused by normalization deserves attention. Do this after checking types and completeness. Otherwise, a missing or unsuitable component may enter the calculation without the reviewer realizing that a prerequisite was never satisfied.

For an exercise, prepare four rows: 2026/10/27, 2026/2/31, 2024/2/29 and 2026/13/1. Before entering formulas, mark the expected decision for each: accept or investigate. Then inspect the normalized results. The exercise separates what Excel can calculate from what your source-data rules should permit.

Also check whether the report discloses the number of rejected records. Filtering out problematic dates can make a total stop matching its input file. I prefer an exception list and an agreed owner for corrections. That turns date construction into a repeatable process with visible limits.

Put the date into a useful workflow

Take a small file whose records you understand and define the acceptance rule before preparing a monthly report. Keep the original values until the exceptions have been explained. A reader should be able to trace one event from its input components to its reporting period without guessing what happened along the way.

For a separate worked introduction to reading rows and defining periods in a query, continue with SELECT in SQL. The tools differ, but the same habit applies: establish what one record means, make boundaries explicit and inspect a small expected result before trusting a much larger output.