VideoPhoto

04 September, 2021

How to DATE in Excel

This is about dating with the Excel DATE... function, and with its versatile formats and forms of uses. It's syntax is simple but the results of its application can be sometimes a bit confusing or unexpected.

Confusing - because the DATE function with its syntax DATE(year,month,day) doesn't object some strange parameters and accepts numbers such as:

  • negative, 0, and >12 integers for a month,
  • higher number of days than the number of days allowable for a given month, as well as 0 days,
  • negative numbers for a day,
  • number lower than 1900 for a year.

So, e.g. entering accidentally month number 14 would set the date to February of the following year. Entering month number 0 would set the date to December of the previous year. Entering year number 1888 would set the date to the year 3788.

Normally, i.e. in General format, DATE function returns a serial number starting from January 01 in the year 1900, represented by number 1, which was Sunday, and increasing by 1 for every next calendar day. September 05, 2021 is the 44444  special day in this sequence, and it's Sunday as well.

If you need to deal with dates before the year 1900 you can access relevant information here:

MrExcel

When using the DATE function it's important to remember that whenever you need to use WEEKDAY function in combination with DATE, weekday number depends on the "return-type". If you are used to consider e.g. Sunday as the first day of the week, the return-type 1 must be used. If you use Monday as the first day of the week then return-type 2 must be used with the WEEKDAY function.

Let's have a look at some examples of using the DATE and related functions / formulas, formats and outcomes shown in this table:

The yellow-coloured column shows the results of formulas entered and how they depend on formats selected for display in a worksheet.

It doesn't matter in what format a given full date is displayed (used), you can get (extract) from that date a day, month or year by using DAY, MONTH and YEAR functions.


 



 


 

No comments:

Post a Comment

All comments are held for moderation. I reserve the right to edit, censor, delete and - if necessary - block comments.