Microsoft Excel has over 200 functions available, how many do you use? Date functions can make your life easier especially when you need dates that change or are using dates within calculations. Here are 7 date functions that I use on a regular basis:
- Now () this function is one that quite a lot of people use, it will insert the current date and time into a cell, and update it. This means that if you open the workbook that you have the Now () function in tomorrow it will have tomorrows date in the cell. I rarely use this in a cell and just leave it. Normally I have calculations that are based on it, such as a calculation with another date cell to find out how many days between 2 dates. If you are in a business where you take orders and ship out products, then this is a good start to finding out the average number of days that it takes between taking the order and shipping it.
- Networkdays (start date, end date, holidays) this function can make the above example really work. If you just subtracted Now () from a date you would get the number of days from one to the other but that would also include weekends. If your business does not work weekends, this reflects actual but not working days. By using the Networkdays function you can input the start date and the end date (which is where you can input the Now () function or the cell that it resides in) and any holidays between those dates. This will then tell you how many days that are Monday to Friday are between 2 dates.
- Today () is an alternative that I use instead of now. The only difference between the two is that the Now () function will return the current system (computer or network) date and time, whereas the Today () function will only return the current date. This does make a difference if you are calculating with dates and are showing decimals. It can also make your spreadsheet slightly neater to look at.
- There are 3 that are so similar I am going to group them together. Year (date) Month (date) and Days (date). They all work the same way, you either type the date or reference the cell within and they will return the Year, the month or the day as a number. I use Year (date) the most. It will give me the year as a 4 digit number. If I just want a general number of years between 2 dates, this is an easy way to do it, but if your dates are 31 December 2009 and 1 January 2010 this will give you 1 year (2010 – 2009). The way I tend to use them is with the IF statement. By using the month within the conditional statement ie Month (C4) >=Month (today () ) then I can have one calculation if true and another if false.
- The last is one that when I first started using a lot of functions I really didn’t like, but now I do use it. It is Date (year, month, day). You can’t use a whole date for each but extract, which is another use for the functions Year (date) Month (date) and Days (date). I find this a quick way to add or subtract from dates, so I could have one month in the future by using Date (Year (today () ), Month (today () ) +1,Day (today () ) ) looks complicated but if you need to do a complicated formula best to break it down and spend the time working on it in order to save time in the future.