Today’s topic is Date and Time functions
Excel has a great deal of functions that deal with date and time which are extremely useful. This post will show you four of my favorites. Before reading on, be sure to download this example spreadsheet showing how these functions are used.
EDATE – This function allows you to add or subtract months from a given date. This doesn’t sound super useful at first, but take into account the fact that:
- The unit Excel uses for simple addition and subtraction applied to dates is days, not months.
- Not every month is the same number of days
- Not every year is the same number of days (leap years)
NOW and TODAY – I lumped these two functions together because they are basically the same with one slight difference. Both these functions give the current date. This date is based on your computer’s internal clock, so the result is only as accurate as your computer. The one difference between the two is that the NOW function also gives the current time. Also, these two are part of the fun group of functions that have no arguments. You just type it like this: NOW()
WEEKDAY – Do you know what day of the week you were born on? No? Excel does. Feed any date into the Weekday function and Excel will give you a number 1-7. The default for this function is that 1 corresponds to Sunday and 7 corresponds to Saturday. However, notice the optional “return type” argument in the function. Using this option will allow you to have the days of the week ordered differently. Below is the chart showing the various options.
NETWORKDAYS – This function will count the number of working days between two dates. It essentially counts all the days between two dates and then takes out all the Saturdays and Sundays. You also have the option to tell it to exclude certain holidays. I’ve used this function before to deal with stuff related to employee salaries or to just figure out exactly how many days I’m stuck in the office.
Final Note: EDATE and NETWORKDAYS are part of the Analysis Toolpak Add-In for PC users. See below for installation instructions. For Mac users, I don’t currently have access to an Apple machine, so I have no idea if these functions work for you or not. There is no Analysis Toolpak Add-In for Macs.
Installation Links
Excel 2003 Instructions
Loading ...