[TSG] Excel's Secret Function, What Are They Hiding?

Excel Education for the REAL WORLD!

I hope you've had a good week so far!

Today, I want to bring your attention to a hidden gem in Excel – the DateDif function. And when I say "hidden gem", I literally mean hidden!

Have a look for yourself. Start typing "DateDif" into the Formula Bar and see if Excel shows any indication it's a real function.

The reason why DateDif is somewhat hidden in Excel is because it was introduced in Excel 2000 as a compatibility function for Lotus 1-2-3 users, and it was never officially documented by Microsoft. However, it has remained in Excel ever since, and it can still be used in newer versions of Excel.

Even though this function doesn't show up in the function tooltips, you can still type it out and use the DateDif function like any other native Excel function (if you remember the proper inputs).

The DateDif function is used to calculate the difference between two dates in various time units such as days, months, and years. The function takes three inputs:

• Start_Date: The first date in the calculation.
• End_Date: The second date in the calculation.
• Unit: The time unit you want to use for the calculation. The available units are: "d" for days, "m" for months, and "y" for years.

If there is a potential that your End Date value could be before your Start Date value, then you'll want to wrap an IFERROR function around it to handle the potential #NUM! error that could occur.

Here are some practical examples of using the DateDif function:

Example 1: Calculate the in-year cost of a new hire

Suppose you have an employee's hire date of 4/1/2023 in cell A1 and their annual payroll cost in cell B1. You want to calculate how much payroll expense you'll incur this year for this employee. You can use the following formula:

=DATEDIF(A1, "12/31/2023", "d") / 365 * B1

Example 2: Calculate the age of a person in years

Suppose you have a person's birthdate in cell A1, and you want to calculate their age in years. You can use the following formula:

=DATEDIF(A1, TODAY(), "y")

In this formula, the TODAY function is used as the End_Date, which represents the current date.

As you can see, the DateDif function can be quite handy when you need to perform calculations involving dates in Excel. So, go ahead and give it a try in your next spreadsheet!

I've written A LOT about Excel over the years. Here are a few articles that might teach you something new today!

• Pull Historical Stock Price Data in Excel
• Adding A Blank Option in a Data Validation Drop Down List in Excel
• How To Change Excel's Group Outline Direction Settings
• 5 Ways to Zoom In/Out of your Spreadsheet Window

Over the years I've solved issues throughout my career by building my own Excel Add-in solutions.

With the launch of my website, I was able to take these tools I had developed for myself over the years and use them to fund operating TheSpreadsheetGuru website.

I tend to never give out discounts except during a new product launch, so this is definitely something that will not be commonplace going forward. As a special welcome gift to you, I'd like to gift you with a 30% off discount code that you can apply to any purchase in my product store (excludes T-shirts and other physical merchandise)

Each of my Excel add-ins or spreadsheet templates comes with a 30-day money-back guarantee so please don't feel hesitant in using this gift to make a purchase. Most of my add-in products also have a 2-week trial period that you can utilize to make sure the tool is right for you before making your purchase.

This offer is only valid for the first 7 days after you've subscribed to this newsletter. After that time, you will be locked from the coupon code.

You can check out even more of my product offerings by visiting my online store.

Today's random product recommendation is a kitchen utensil that I have loved so much I recently bought another one. Today, I'll be telling you about an absolutely amazing ice cream scooper.

So how can an ice cream scoop be amazing? Well, it just works how you'd expect it to work.

• The scoop is very solid and cuts right through ice cream like butter, allowing you to create perfect spheres
• It has a very comfortable grip
• It's dishwasher safe
• Comes in a variety of colors
• And most importantly, the ice cream NEVER sticks to the scoop!

I'm an absolute sucker for ice cream (especially Mint Chocolate Chip) and after going through a ton of different scoops over the years, this one beats them all by a long shot.

If this product interests you or you have some shopping to do on Amazon, make sure to use my "Check It Out" button link above. If you buy anything within 24 hours after clicking this link, a 1-2% portion of your sale will go directly to TheSpreadsheetGuru (at no cost to you).

This is a fantastic way for you to support the cost of running this newsletter without having to spend any extra money. I appreciate your willingness to help support this newsletter!

Hopefully, this newsletter brought you some value today. I absolutely love sharing the tips I've come across or developed over the years and also receiving feedback from everyone.

If you have any thoughts on the topics covered in this email, feel free to respond back.

What's in store for tomorrow? We'll be discussing one Excel feature that instantly makes you an Excel guru (and virtually no one knows it exists!).

Chris
Founder, TheSpreadsheetGuru.com

You are more than welcome to invite any one of your friends or colleagues who might also benefit from this newsletter. Simply send them the below link and they can get signed up for free as you did.

Newsletter Link: TheSpreadsheetGuru.com/newsletters