Skip to content
Klargrid

The DATE function

Building a date from its three parts. It is also the safe way to write a date inside a formula, whatever the regional settings.

Syntax

=DATE(year, month, day)

In a French Excel: =DATE(année; mois; jour).

Arguments

  • year: four digits preferably.
  • month: 1 to 12; 13 rolls over to January of the next year.
  • day: 1 to 31; 0 gives the last day of the previous month.

An example

=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

Rebuilds the date from a text "15/03/2024" that the import left as text.

Pitfalls

  • A two-digit year: 24 becomes 1924, not 2024.
  • Date criteria in SUMIFS are written ">="&DATE(2024,3,1): safer than a text "03/01/2024".

Going further

  • Fix dates: fixes a whole column of text dates, swapped day and month included.
  • EOMONTH.

Frequently asked questions

How do I get the last day of a month?
=DATE(2024,3,0) returns February 29, 2024; or EOMONTH.