Date Function in Excel


While using Microsoft Excel, you must have got the situation where you need to use the Date function. Date function is one of the common and most used function in Excel that helps in various calculations. This Function also helps to convert the serial numbers to Date format of your choice.

How to Write Date Formula in Excel?

Now while looking at above Formula, there are four parameters which are described as below:

1. Year: This argument is used to denote or get the year from the serial numbers. It is advisable to use the four digits while using arguments for “Year”.

For Example: If you enter below formula 

Syntax: =Date(2019,9,19) will give result to 09/19/2019

And If you write the above formula as below:

Syntax: =Date(19,9,19) will give result to 09/19/1919

Here we mentioned only two Digits in Year Argument. Since Microsoft Excel has it’s calendar starts from Year 1900 and sometimes gives the incorrect result. That’s why it converted 19 to 1919 (1900+19) 

Date Function

So it is to advised that correct syntax with four digits is used to get the accurate results.

2. Month: Month argument is used to denote or get the month from the serial numbers. It is a numeric number starts from 1 to 12. If any negative or more than 12 numbers are used in function, then month would be adjusted from year.

For Example: If you enter below formula 

Syntax: =Date(2019,9,19) will give result to 09/19/2019
Date Function

So if you write this formula as below:

Syntax: =Date(2019,13,19) will give result to 01/19/2020

Here we mentioned “13” in the month, that is more than 12. In this case Microsoft excel will add the month and adjust the year. So adding 12+1 (i.e. December + 1 month) will be January and year would be changed to next year i.e. from 2019 to 2020

Date Function

And if you write this formula like below:

Syntax: =Date(2019,-1,19) will give result to 11/19/2018

Here we mentioned “-1” in the month. In this case Microsoft excel will deduct one month from the year and will provide the output. So, adjusting (2019 – 1 month) will be November and year would be changed to previous year i.e. from 2019 to 2018

Date Function

3. Day: This argument is used to denote or get the Day from the serial numbers. It is a numeric number starts from 1 to 31. If any negative or more than 31 numbers are used in function, then days would be adjusted from month and year.

For Example, if you write this formula:

Syntax: =Date(2019,9,19) will give result to 09/19/2019
Date Function

So if you write this formula as:

Syntax: =Date(2019,09,32) will give result to 10/02/2019

Here we mentioned “32” in the month, that is 2 additional days of 30 days in September. In this case Microsoft excel will add the days to next month. So, adding 30+2 (i.e. 30th September + 2 days) will be 2nd October and output will be 10/02/2019 (i.e. 2nd October 2019)

Date Function

Now if you write this formula as

Syntax: =Date(2019,09,-15) will give result to 08/16/2019

Here we mentioned “-15” in the days. In this case Microsoft excel will deduct 15 days from the given month So, adjusting (September – 15 days) will be 16th August and output will be 08/16/2019 i.e. (16th August 2019)

Date Function

Here you may also link any of the above argument with any cells in excel to drive the formula on the basis of variables. So if your data is like below:

Date Function in Excel

Above Formula in Cell D2 contains the Date function which is linked to Cell A2,B2,C2. So now this is driven by variable values. Hence you may use this function with multiple data points to create date. Hope you liked this article. 

Please comment and share your valuable feedback or if you have any questions.

Leave a Reply

avatar
SUBTRACTION AND ADDITION IN DATE FUNCTION
EXCEL TRICKS
Calculations with Date in Excel

Dates function also be used to subtract the Year, Month and Days from the existing dates. Sometimes we need to subtract specific period from the date. In case you are working on large database, this become tedious job for calculating date difference Here we discussed the various scenarios where we …

Logo Inserting Symbols in Excel
EXCEL TRICKS
How to Insert Symbol in Excel Numbers?

Have you ever faced the challenge while using special character symbols in number formatting or any customized number formatting? It is an easy method to insert any symbol in numbers i.e Delta Symbol or Special Character Symbols in Excel Number Formatting.  You may easily do this by following below steps: …

Delta Symbol
EXCEL TRICKS
1
Usage of Delta Symbol in Excel – Complete Guide

The Greek Capital Alphabet letter Delta (▲) and  is commonly discussed topic in Excel. This is being used for various purposes i.e. changes in quantity, insert the symbol in excel etc. Though inserting Delta (▲) is very easy in any of the MS Office Applications i.e. Excel, Word, Power Point …

error

Like the Article? Please spread the word :)

Facebook
Facebook
YouTube
YouTube
Pinterest
Pinterest
LinkedIn