In this free Excel help article we’ll look at some of the Microsoft Excel’s Date functions. The format of the date used in our article is mm/dd/yyy but this of course can be changed in Microsoft Excel.
TODAY – This is a function that does not take in any arguments. Used in a cell as =TODAY() displays the date of the current date, for example 8/9/2011. But here’s a word of caution. You open your Microsoft Excel spreadsheet tomorrow and you’ll see tomorrow’s date in the cell and not today’s date.
NOW – Like the TODAY function this one too does not take any arguments. It displays both the date and time. Used in a cell as =NOW(), this displays something like 8/9/2011 13:15 Like the TODAY function this one too changes dynamically as time and day progresses.
DAY – This function takes in a date in quotes such as “02/05/2011” or a date in a cell on the MS Excel worksheet and returns the day of that date, in our example 5. Our date is in the MM/DD/YYYY format.
MONTH – This function takes in a date in quotes such as “02/05/2011” or a date in a cell on the Microsoft workbook and returns the month of that date, in our example 2. Our date is in the MM/DD/YYYY format.
YEAR – This function takes in a date in quotes such as “02/05/2011” or a date in a cell on the Excell spreadsheet and returns the year of that date, in our example 2011. Our date is in the MM/DD/YYYY format.
DATE – Date is a very useful function in Excel and using which a number of activities related to dates can be performed. We’ll discuss them here.
The basic Excel function returns a date if you supply year, month and day as parameters as in the example =DATE(2011, 5, 9) will return the date 5/9/2011 in the cell.
DATE can also handle some tricky situations. For instance, if the date was “2/30/2011” DAY, MONTH and YEAR functions will generate errors but the DATE function will not. For example DATE (2010,1,0) will give a date such as 12/31/2009. Another example would be DATE(2010,1,32) which would produce a result as 2/1/2010.
EDATE – With EDATE you can perform mathematical operations too. For instance you want to find a date five months from today’s current date. So instead of trying to figure out the number of days for the 5 months from the current date and even arriving at the wrong answer all you need is to use EDATE. With EDATE you can arrive at this answer easily. The format of the function is EDATE (Start-date, months). Both Start-date and months can be cells or input data as in EDATE(C12, C13) or EDATE(“9/5/2011”,5)
You can also do go backwards. Just use a negative number for the number of months and EDATE will give you the perfect result as in example EDATE(“9/5/2011”,-5).
What if we wanted to find a date 10 or 20 years from now? EDATE comes in handy here too. You don’t need to count the number of months. Instead you could do the calculations inside the EDATE function itself. Suppose you wished to know the date 20 years from a given date, all you need is to multiply 20 times 12 months in the function as EDATE(“9/9/2011”, 12*20) or if the date is in cell C11 and the number 12 is inside cell C12, EDATE(C11, C12*20). Note that using the DATE function too we can compute dates from a given date but that is tedious. EDATE is a new Excel Date function that solves the problem easily.
DAYS360 – This Excel function returns the number of days between two dates, a start-date and an end-date within a 12 month period i.e. within a year. An example would be =DAYS360(“2/2/2011″,”9/9/2011”) and the cell would contain 217. You could also replace the dates with cells containing those dates as in =DAYS360(C11,C12). A third parameter after the end-date is optional. However if you wish to mention “False” it would consider U.S. dates and “True” for European dates. This function is particularly useful in Payroll processes, Excel consultants, small business, student record keeping, etc.