How to work with Date & Time Formula Wizard

Working with time-related values requires a deep understanding of the way the DATE, DATEDIF, and TIME functions work in Excel. This add-in lets you quickly perform date and time calculations and get ready-to-use formulas without much effort. Below you can find a detailed description of how to work with the tool.

Video: How to calculate dates and age in Excel

Start the add-in and select your operation

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

Note. If you have a value in the selected cell, it will be replaced with the resulting formula. If you already have a formula there, the result will be added to it.

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:
Pick the tab depending on what you want to calculate.

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:
How to add and subtract dates in Excel.

  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 Expand arrow icon in the field:
    Select the date in the drop-down calendar.
  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. Please note that 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:
    Add and subtract time units in Excel.

    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 feel free to 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. You can copy the details of the result by right-clicking the preview field.

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:
Add weeks to date in Excel.

Tip. The formulas you build will be imported into the add-in for the next time you start it. Say, you create a formula and close the Wizard, but then realize that you need to fix that formula. If you select a cell with the formula and run the add-in, you will see that all the initial arguments remain in the corresponding fields. You do not need to enter them again, just change the wrong values keeping the right ones intact.

How to find the difference between two dates

Click on the Difference section at the top of the add-in pane:
How to find the difference between two dates in Excel.

  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 Expand arrow icon 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 functions 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:
    Select the units for calculating the difference between two dates in Excel.

  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, tick off the Exclude months box.

    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 check the More compact formula box if you want your formula to be shorter. If you tick the box, your result will look like this: "1 month(s) and 3 day(s)". If you leave the box unchecked, the formula will distinguish singular and plural, and you will get "1 month and 3 days".
Note. You will see a preview of the formula in the destination cell.

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:
Calculate age 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 Expand arrow icon and select your date from the drop-down 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 most current age from the date of birth. In other words, it will show how young the person is by comparing their 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 using the Expand arrow icon, 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 an exact number in years + months + days. Select the option that suits you best in the Age drop-down list.
    Tip. You will see the preview of the result next to each choice in the list:
    Select the way to calculate age in Excel.
  6. There are three more benefits 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 ticking More compact formula. In this case, your result will look like this: "1 month(s) and 3 day(s)". If you leave the box unchecked, the formula will distinguish singular and plural, and you will get "1 month and 3 days".

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

How to pick dates in pop-up calendar in Excel


AKASH Gavhane says:
July 6, 2018 at 4:44 am

Hi sir
My name AKASH kalyan Gavhane
From waluj M. I. D. C. Aurangababd
My problem axle
Date works in job time
Year, month. Day. 2018.7/6/2018
Formulas axle plz send


Hi Akash,
Thank you for contacting us.
I think in this blog post you will find the formulas you need. If not, try to look for the needed information here.


Column b is an entered date of birth,
Column c is the date they turn 3 years old
In column d I want to show a date they will need to transition to the next stage. If they are 3 before 8/31 of the current year they will transition on 8/31 of the current year, and if they do not turn 3 until after the 8/31 date it will be 8/31 of the next year that they will move on.

Anyone have a formula to populate the cell with 8/31/current year if 3 before date or 8/31/current year plus one if not 3 before date?


Hello, Trent,

Thank you for your question.

If I understand your task correctly, you need to use the IF function to build the formula. Please refer to the following blog post to check how to do that: IF function with dates


Difference is not returning Zero weeks if Date 1:Today < Date2 12/31/2019. It is returning 40 weeks.


Hello Lisa,
Thank you for the comment. That is right, the difference between Today and 12/31/2019 is 40 weeks. In the Difference option, the tool does not subtract one date from another but calculates the period of time between the two dates, that is why it is called the difference. Did I manage to dispel your doubts?
Please feel free to contact us with any other questions.
Thank you.

Coreen McIntosh says:
March 27, 2019 at 1:39 am

Can you choose a null date in date picker?


Hi Coreen,
Thank you for the comment. Date Picker uses the standard Excel's dates variety, where the "null" date is 1/1/1900. Or maybe you meant something else? If so, please describe your task in more detail, including the definition of "null date" as you see it.
Thank you.

Douglas Carlile says:
July 12, 2019 at 6:43 pm

I am trying to do some calculations using time. The help screen references Time Wizard tool, but it does not show on my toolbar. My version is 2014.4.1.355.

A quick reply would be most helpful.

Katerina Bespalaya says:
July 12, 2019 at 7:13 pm

Hello Douglas,

The Date & Time Formula Wizard is quite a new tool and available only in Ultimate Suite version 2018. If you want to try it out, you need to uninstall the current version of the product via Control Panel, download and install the latest version of Ultimate Suite from this page.

If you have any other questions or need further assistance, please reach us out at


I am trying to use the date & time wizard to show time elapsed by weeks. I would like to exclude months in this formula. The exclude months and years features are both grayed out. Any idea why?


Hello Marion,
Thank you for contacting us.

For us to be able to understand the problem better, please send us a small sample workbook with your source data and the result you expect to get to If you attach a screenshot of the add-in's pane with the options grayed out, it would help a lot. Thank you.


Hi, I have just installed the trial version of Ablebits and everytime I try to perform the date&time difference I getting the message to insert time, this I do not want, just to work with days.
When I input all details into the date&time box I get the correct result but it will not let me put the formula into the cell I require without putting some sort of time function.

Please help as this will save a lot of work over many spreadsheets, if it does not work then it is back to the old method.

I am trying to use the trial version on office 365.

Katerina Bespalaya says:
November 26, 2019 at 7:08 pm

Hi Ian,

I’ve just replied to you by email. Please check your Inbox. Thank you.


Is there any way to calculate a time difference (Total hrs and minutes 00:00) without a day? Ablebits' date & time wizard seems to only calculate it with days "d+m+h". All my cell entries are for the same date so I only need hh:mm. Thank you.


Hello Abe,

Thank you for contacting us. You are right, currently, there is no hh:mm combination to find the difference in. We'll consider adding it in the future versions of the add-in, but in the meantime just check the "Do not show zero units" option and the zero value of days will be omitted. Thank you.



I downloaded the trial and trying date & time formula wizard. There is problem in the difference.
I tried everything but it pops up error.

Please select the cell with the second DateTime or enter the DateTime manually in one of the following formats: "h:mm AM/PM" or "dd/mm/yyyy h:mm:ss AM/PM"

What is the problem?

Thank you.


Hello Wen,

Sorry to hear that you are having difficulties with our add-in.

For us to understand the problem better, could you please send us a sample workbook with your dates and a screenshot of the add-in window with the selected options to

We'll look into the issue and try to find its cause.


Great tool, i bought it for the calculation between two fields but too bad the colums is not sortable after the output (73 hour(s) 14 minute(s) ) and i actually only wanted the total of minutes... So a lot of manual work to make that happen with ablebit... Why are there ni minutes and just enter it as a number field?

(I use it to check time between opening ticket and closing)

Any tips?



Hello Ronny,

Thank you for sharing your feedback with us. We have just replied to you via email.


Ask a question (posted publicly)

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.

60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Sheila Blanchard