How to work with Date & Time Formula Wizard

Start the add-in and select your operation

First, select a cell where you want to get the resulting formula. Then run the tool by clicking the Date & Time Formula Wizard icon on the Ablebits Tools tab, in the Date & Time group:

You will see the add-in window with four possible modes at the top: Add, Subtract, Difference, and Age. Choose the operation you would like to perform by clicking on it:

How to add or subtract date and time

Depending on your needs, click on Add or Subtract at the top of the Date & Time Formula Wizard pane:

1. Select or enter the start date—the day you want to subtract from or add to date and time units.
2. You can choose a date from the calendar by clicking the arrow:
   
3. Also, you can get a cell reference inserted into the field using the Select Range icon.
   Tip. You can even use cells with standard Excel NOW and TODAY functions to start with.
   Note. Keep the field blank to add the necessary time units to the existing formula. However, if you leave the field blank while your destination cell is empty, the add-in will take the earliest date used in Excel as the base for calculations: 1/0/1900.
4. To add or subtract years, months, weeks, or days, click in the corresponding field and use the Select Range icon that appears there. Also, you can enter the number manually. Feel free to use all fields at once or fill in just one of them.
   Tip. Use a cell with a date for any field, and the add-in will automatically fill in all units available in the specified value. This will work only if the CELL function returns one of the date formats for it.
5. Click Show time fields to expand time units as well:
   

   Click in the field of interest and use the Select Range icon to pick the cell with the necessary value in your Excel worksheet. Or enter the number manually. You can use all fields at once if needed.

6. The Formula result area helps you see the selected date or time calculation right away. It will also show #Value errors if any of the fields refer to data in non-numeric format, e.g. text.
   Tip. Right-click the preview field and choose to copy either all contents of the Formula result field or only the result.
7. Select the Use LAMBDAs checkbox to get custom functions that are as easy to reuse as native Excel ones.

Once you select the arguments, click the Insert Formula button. The add-in will paste the resulting formula into your Excel table and set the date format to the cell. You can then copy the formula down if it applies to other cells:

How to find the difference between two dates

Click on the Difference section at the top of the add-in pane:

1. Pick the dates to find the difference between. You can click in the field and then pick the cell with the date in your Excel table or type in a date manually.
2. Click the arrow to open a calendar and select a date from there.
3. Use the Select Range icon to point to a cell in your worksheet.
   Tip. Use the NOW or TODAY function in one of the fields and get a formula that will keep comparing a date to the current one.
4. Choose units to find the difference in. Open the Difference in drop-down list and decide on the way you'd like to see the result. You can find every possible option from a simple count of days, months, weeks, and years to unit combinations, like "Y + M + D". Also, you can choose to get your result in days plus time units (hours, minutes, seconds): "D + h + m" and "D + h + m + s".

   Each option in the list shows a preview of the resulting value:
   

5. If you want to omit years in your calculations and show the difference in months and days only, choose to Exclude years. To see the difference in days and skip the month count, select the Exclude months checkbox.

   Say, you are trying to compare "1/1/2015" and "3/1/2017". If you choose to show the difference in months, you will get "26 months". If you pick "Y+M" for the result, you will see "2 years 2 months". To get the difference in months without years, tick off Exclude years and you will get "2 months".

6. Use additional options to define the details of the calculated units:
   • Check the Add text labels option to see the description of the numbers in the result. For example, "5 weeks 3 days". When the label option is not selected, you will see numbers separated by your system delimiters, e.g. "5/3".
   • If the date difference result may have zero values, e.g. "1 week 0 days", you can hide them by selecting the checkbox Do not show zero units and get "1 week" instead.
   • The DATEDIF function in Excel will return an error if date 1 is greater than date 2. Our tool ignores the order of dates by default but you can use the option to show Negative result if Date 1>Date2.

     Here is an example: you are trying to see the number of days between two dates in Excel, 1/15/2018 and 12/29/2017. Select this option to get "-17 days" if 1/15/2018 is selected as Date 1. Without this option checked, you will get the plain difference of "17 days" no matter what date you select first.

   • You can select the More compact formula checkbox if you want your formula to be shorter. If you select this checkbox, your result will look like this: "1 month(s) 3 day(s)". If you clear it, the formula will distinguish between singular and plural, and you will get "1 month 3 days".
7. Select the Use LAMBDAs checkbox to get custom functions that are as easy to reuse as native Excel ones.

Click Insert Formula to see the result in Excel and use it for further calculations.

How to calculate age in Excel

Go to the fourth tab to quickly calculate ages in Excel:

1. Specify the Date of birth: select a cell that contains it or enter the date manually.
2. Besides, you can click the arrow and select your date from the dropdown calendar.
3. If you have a table of dates, use the Select Range icon to select a cell with the first date, or simply click the field and pick a date in your worksheet.
4. Use the Age at the date of section to choose the way to count the age:
   • Count it for Today's date.
     This option will calculate the current age for the selected date of birth. In other words, it will compare a person's birthday to the present day.
   • Calculate age on a Specific date.
     This option lets you get Excel formula to calculate age between two dates: the date of birth and any date you specify in the field. You can enter the second date in your system format, find it in the calendar clicking the arrow, or pick one in your Excel worksheet using the Select Range icon.
5. Choose the way to display the result. You can convert a person's age to days, weeks, months, years, or get it in years + months + days. Select the option that suits you best in the Age dropdown list.
   Tip. You will see the preview of the result next to each choice in the list:
   
6. There are three more options that change the way the calculated age looks in Excel:
   • Include date unit information for the numbers you get by checking Add text labels. Or unselect this checkbox to get plain numbers.
   • Exclude zero values from the result with the help of Do not show zero units.
   • Make your formula shorter by selecting More compact formula. In this case, your result will look like this: "1 month(s) 3 day(s)". If you clear this checkbox, the formula will distinguish between singular and plural, and you will get "1 month 3 days".
7. Select the Use LAMBDAs checkbox to get custom functions that are as easy to reuse as native Excel ones.

Click the Insert Formula button to get a formula that calculates exact age from the date of birth in Excel.