Excel Date Function

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)

Outbound References

https://support.office.microsoft.com/en-gb/article/DATE-function-9b619152-52d4-48f0-b1ab-a32dee59b6e4?CorrelationId=2fb5edef-b55a-4aa8-b5c3-deb71eb98471&ui=en-US&rs=en-GB&ad=GB

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *