≡ Menu

Date Formulas in Excel 2013

In this tutorial I will teach you about the DATE function in Excel 2013.

Whenever you have to do manipulation of date, excel use Date function. Excel does not store the date as the date itself. It does not keep the year, month and day for a date. Nor does it store weekday or weekend information in a cell. Rather it stores dates as serial numbers. This storage of serial numbers is the main confusion creator.

Due to this it is not recommended to supply dates directly in calculation. Instead DATE function can be used to get the serial number representing the date.

In Excel 2013, there is a separate section of date functions in the formula tab

The syntax of Excel Date function is

Date(Year,month,day)

Here are the details of the arguments used in the Date function.

Year

This shows the year mentioned in the date function. This is set up according to your computer’s date system. Excel 2013 uses 1900 system by default which means that if the year argument is between 1900 and 9999, it will use standard year like Date(2016, 12,06) gives December 6, 2016.

Similarly if we put the argument which is from 0 to 1900 let us say 116, the date function will return 2016 (1900 +116).

Giving a negative argument in the year section will give #NUM! Error.

In order to avoid any confusion, it is suggested to include 4 digit year.

Month

Month argument takes integer showing the month of the year (1 to 12).

If month is greater than 12, it will add the months For example. =Date(2016,16,01) returns April 01, 2017 adding 16 months.

Similarly, if the value of the month is less than 1, it will subtract from 12 to go back in date. For example. =Date(2016,-5,02) returns July 02, 2015.

Day

It also consist of integer from 1 to 31 and calculate similarly to the month argument.

This supply of values in date function seems odd but in advance level working, it turns out to be handy.

Some example of date function manipulation is given below:

The simple function returns the serial number of the date formula.

=DATE(2016,-5,2) returns 42187 in simple formatting.

We can also add or subtract the dates from the date function.

Adding days to a date:

=DATE(2016, 12, 02) + 15 returns December 17, 2016.

Subtracting days from a date:

=DATE(2016, 12, 02) – 15 returns November 17, 2016

To subtract a date from today’s date:

=TODAY()-DATE(2016,12,2)

This calculation uses Today function that returns todays date and then it subtracts from the specified date to calculate the number of days between the current date and some specific date.

In Excel 2013, the DATE function is the main function with lots of additional functions available to be used inside the date function in order to calculate different things. These functions includes Day, year, month, today etc.

{ 0 comments… add one }

Leave a Comment