If you ever need to work out how many days, months or years are between two dates, Excel is an excellent tool to use. Excel has a couple of formulas especially used for working with dates, which we will look at now.
Excel stores dates as a number rather than a date, so it’s important dates are entered correctly if you want Excel to recognise them as dates. The number used by Excel for a specific date represents how many days have passed since January 1st, 1900, and the number is then formatted to display as a date that we can understand.
.png)
The easiest way to ensure a date is entered correctly into Excel is to first select the cells that will contain dates and format them using a date format. To do this, click on the dropdown list in the General group on the home tab and select one of the date formats. Type the date using a dash or forward slash to separate the day, month and year.
.png)
Adding or subtracting days of a particular date is very simple, and handled in the same way you would add or subtract numbers. Start with a date in a cell, and then in another cell, type =A1+5, where A1 is the cell that contains the date. This will add 5 days to the date in cell A1. Subtracting using the – sign also works.
.png)
To add or subtract months from a date, use the EDATE formula. =EDATE(A1,1) will add exactly 1 month onto the date in cell A1. You can also use a negative number to subtract months from a date. Another useful formula is =today(). Type this formula in any cell and it will display the current date.
.png)
If you need to calculate how many work days there are between two dates, use =NETWORKDAYS(A1,A2) where A1 contains the start date, and A2 contains the end date. Or if you need to calculate a future date using working days, use =WORKDAY(A1,20) where A1 contains the start date, and 20 is the number of working days you want to add to the start date.
.png)
To calculates somebody’s age down to the month, use this formula to calculate the years: =DATEDIF(A1,TODAY(),”Y”), where A1 contains the persons birth date, and use this formula to calculate the months: =DATEDIF(A1,TODAY(),”YM”). This formula uses the TODAY() formula to get todays date, and compares it with the birth date in cell A1 to calculate the result.
