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)
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
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
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
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
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)
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)
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:
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.
Here is another best rated Excel Charts and Graph Course from ExcelSirJi. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.
This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.
So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.
We are offering Excel VBA Course for Beginners to Experts at discounted prices. The courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion
This course is going to help you to excel your skills in Excel VBA with our real time case studies.
Lets get connected and start learning now. Click here to Enroll.
Hope you are enjoying learning Excel with us, if you want any support related to this article, please do comment else you can ask questions in Excel Community
Hope you are enjoying learning Excel with us, if you want any support related to this article, please do comment else you can ask questions in Excel Community
SUBSTITUTE function is used to substitute the existing old text to new text.
Merge Cells in Excel Merge cells is to combine multiple cells into one cell which can further be used for giving title to the report or header to the column. It helps to create clean…
MID function is used for extracting the mid characters from the available string. The output of the function returns the extracted characters in new cell.
SUMIF function is used to get the “total sum” for number of times the criteria across range is met. SUMIF Function has two required arguments.
TRIM function is used to remove the additional spaces (i.e. spaces before/after/between the words) except for single space between words.
SUMPRODUCT function performs multiplication of numbers within arrays and then sum the values SUMPRODUCT function has array1, 2.. arguments.
One Comment