Dec
5

Video: Excel date formulas made easy

Learn how to get the most of Ultimate Suite whenever you have calculations that involve dates.

Excel date formulas made easy: video transcript

This video will show you how to get Excel date formulas of any complexity without actually writing them. Ultimate Suite provides a simple tool that will quickly do all the technical work for you.

Add and Subtract dates in Excel

First let's see how you can easily add or subtract any date units. For example, here I want to find out when the orders need to be shipped to come in time, and I need to subtract shipping months, weeks, and days from the delivery date.

Let's go to Ablebits Tools tab and click on the Date&Time icon. You'll see 4 groups of tasks at the top that let you add or subtract dates, find the difference, and even calculate exact age using a birth date.

When you click Subtract, you'll see two simple arguments to specify:

  1. The first one is the date to start with, which is our delivery date. You can simply select one from the calendar, type it in using your system format, or refer to a cell in your table so that you get a formula you can apply to similar records.
  2. We go on to the second argument: the values to subtract. You can see that they are divided by units that can be used together or on their own.

    How to subtract dates in excel without writing formulas
    If we had a date we could point to, the add-in would take all available units. In our case it's best to point to the cells with shipping time: so I enter column D for months, E for weeks, and a cell in column F for days. The add-in won't let you make a mistake: if you use a text value, you'll see the error in the formula preview and quickly correct it.

Now it looks good, so I click Insert formula and simply copy it down to get the results for all my dates.

Excel DATEDIF formula in under a minute

What if you want to see the exact difference between two dates in excel? Run the add-in and select Difference. Let's find out how much time remains until the shipping date.

  1. To get a formula that keeps the result current, we can use the =TODAY() function Instead of a simple date. And I put in my shipping date as date 2 for comparison.
  2. I can also specify how I want to see the difference: I can pick months, weeks, days, or a combination of the units I need and you can already see the result preview.
  3. Get Excel datedif formulas in a click

  4. There are a few options that let you get a formula for a very precise result:
    • Add text labels to describe the result, or get plain numbers.
    • You can hide extra zeros so you don't get anything like "1 week and 0 days" in the results.
    • If you write such formula manually in Excel, you need to be careful with the order of the dates. Here you can use this option to either ignore the order, or show negative result, which is helpful in our example because we can see any overdue orders whenever today is bigger than the shipping date.
    • If your dates differ in years, but you only want to see the number of months or days between them, these two options let you ignore the bigger unit in your calculations.

We're all set, so I'll go ahead and insert the formula. See how long is? The best part is that I can use it over and over.
Quickly calculate the number of days between two dates in Excel

How to calculate age in excel

This add-in is also a simple age calculator in Excel. If you click Age:

  1. You can pick or enter a birthday
  2. Say whether you want to calculate the age today or on a certain date
  3. You can get a count in years, months, weeks, or even days from the date of birth, or see the result as a full year/month/ and day set
    How to calculate age from date of birth in Excel

  4. Feel free to add a text description for the resulting value, or unselect the checkbox to get just the numbers
  5. One more handy option is the possibility to hide zero values. That is, If the age is "7 years, 0 months and 5 days", you can skip the second number in your result
  6. When you click Insert Formula, you'll get the date of birth converted to age, and you can re-use this formula in Excel.

This add-in comes with other helpful tools in Ultimate Suite, you can download and try its evaluation version on ablebits.com. And if you have any questions, please contact us, we're always ready to help.

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
60+ professional tools for Excel 2016-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
Awesome!!!
Sheila Blanchard