This Excel tutorial explains how to use Excel Date Function.
You may also want to read:
Excel first date of month and last date of month
Excel Date Function
Excel Date function is used to transform year, month, day into Date Serial, a numeric value that represents the Date.
Why do you need Excel Date Function?
Date is being treated specially in Excel. The format you see in Excel worksheet such as dd/mm/yyyy is only a displayed formatting, the underlying value of a Date is a numeric value. 1/1/1900 is considered as the first date in Excel, the numeric value of it is 1, and add one to each day onwards.
Everytime you do calculation on Date, Excel calculates using the numeric values. However if you actually type a Date in a condition or do a calculation, you need to enter the underlying numeric value instead of typing something like 1/1/2015.
For example, most people type a Date in A1 and A2 respectively to compare two Dates in Cell
=IF(A1>A2,”present”,”past”)
What really happening underneath is that Excel compares the two dates in numeric value, not comparing in “dd/mm/yyyy”. What if you need to actually type a Date in A2?
Should you type
=IF(A1>1/1/2015,"present","past") OR
=IF(A1>"1/1/2015","present","past") ??????
None of these will work, because you need the underlying numbers that represent the two dates, not a text, that is why Date Function is important, which transforms dates into numeric values.
Syntax of Excel Date Function
DATE( year, month, day )
| year | 4 digits |
| month | 1 to 12 |
| day | 1 to 31 |
If day is input as 0, the last date of last month is returned, which is an important trick to find the last date of month.
If you want to know more about the exceptional handling of Date function, please refer to the Outbound References section.
After applying this formula, the function returns a date in dd/mm/yyyy (dd/mm or mm/dd depending on your local setting). But as I explain previously, the underlying value is numeric, you can display the numeric value in Format Cells > General.
Example of Excel Date Function
| Formula | Result | Explanation |
| =DATE(2014,1,1) | 1/1/2014 | Display as 1/1/2014, but the underlying value is 42005 |
| =DATE(2015,1,1) | 1/1/2015 | Display as 1/1/2015, but the underlying value is 41640 |
| =DATE(2015,1,1)-DATE(2014,1,1) | 365 | 42005-41640=365 |
| =IF(A1>=DATE(2015,1,1),”future”,”past”) | Compare with Range A1 | |
| =IF(DATE(2014,1,1)>=DATE(2015,1,1),”future”,”past”) | past | 41640 (1/1/2014) < 42005 (1/1/2015) |
scream truly an awe-inspiring contentedness thanks so much for sharing i testament call this pregnant web site every bingle mean solar day