Knowing how to use a few of the many available Excel date functions is a useful skill if you have to do any sort of time tracking or reporting. Aside from using proper number formatting, it is also important to know how to use a few of the core Excel date functions. Knowing how to use these date functions will enable you to analyze date/time data in increasingly interesting and more complicated ways. If you learn the three Excel date functions outlined below, you will be well on your way to becoming an Excel power user.
1. Get Started with Excel Date Functions TODAY!
The TODAY and NOW Excel date functions are particularly useful if you need a dynamic function to automatically calculate the date and/or time every time you open or refresh a spreadsheet. The only difference between these two functions is that TODAY will simply return today’s date, while NOW will return the current date and time (calculated using the time settings on your computer). To use either of these functions, simply enter
=NOW() into a cell on your spreadsheet. There is no need to put anything inside the brackets of the function.
2. Determine Working Days With NETWORKDAYS
The best Excel date function to use to calculate the number of working days between two dates is NETWORKDAYS. The syntax is
=NETWORKDAYS(Start-Date,End-Date,Holidays). You can enter the dates manually into the formula, but they have to be formatted using Excel’s special serial date formatting. It’s much easier to simply put your dates into two different cells and use cell references to pull them into the formula:
Note that the exclusion of holidays from your calculation is optional. Any dates listed in the holidays section of the formula will be excluded from the working days count.
3. Determine a MONTH/DAY/YEAR or HOUR/MINUTE/SECOND
If you’re presented with a date, and you only need to pull out the particular month of that date, the easiest way to do that is with the MONTH Excel date function. If you use
=MONTH(Date), Excel will return the numerical value of the month in the date you have indicated, as in the example below.
The DAY/YEAR/HOUR/MINUTE/SECOND functions all work the same way. These functions are useful if you need to break out the data by segments (day, year, etc.) and are particularly helpful in Pivot Table filters when the standard date filters aren’t feasible.
Put Some Training on Your Calendar
This only scratches the surface of what you can do with Excel date functions. There are many more applications that will enable you to perform increasingly complex data analysis with Excel. To get started, signup for our free email newsletter, where you’ll get Excel tips and tricks directly in your inbox. You’ll also get exclusive discounts for our training classes and webinars.