Excel Hint: How to Use Microsoft Excel 2019 To Calculate the Difference Between Two Dates: Datedif Function

 

The Microsoft Website has a really good post on how to use Microsoft Excel to calculate the difference between two dates. The graphic below comes from that page on their website. (The full image is at the end of this post).

 

How to calculate years between two dates in Excel: If you follow the examples you will see where we calculate the number of years, months,days, hours, minute, seconds, then you can easily derive a formula to calculate years in Excel, as shown below.

The following examples can help you check if you got the formula right – Example 1.

 

Calculating complete years between two dates (DATEDIF function)

style=”max-width: 100%; height: auto;”

To find out the number of complete calendar years between two dates, use the old good DATEDIF with the “Y” unit:
=DATEDIF(A2,B2,”y”)

Here is the full image of the graphic on the Microsoft Website. Follow the link to go to that page.

 

 

If you would like to learn more about this Excel function or literally anything at all in Microsoft Excel, Access, Office and Power BI, visit our free Microsoft Excel Help Forum, VBAExpress.com.

 
 
 

 

Excel Hint: How To Apply A Theme In Microsoft Excel 2011 For The Apple Mac

 

Say you are new to Microsoft Excel for the Mac, or just an average Excel user, and you want to make your workbook more attractive to the user, your boss in this case. That surly must take a lot of effort, right? Wrong, it is super easy to do, and the impact is instantly measurable.

 
 

Excel 2011 for the Apple Mac: Applying Themes

 
 

One approach you can take to make your Excel presentations visually stunning is to apply a Theme in Microsoft Excel 2011 for the Apple Mac. If you do you instantly you see that Excel does not need to look so bland; you can make your workbooks pop of of the page with properly formatting the file, via Excel Themes, in a manner of seconds or minutes.

 
 

On the Home Ribbon look for the Themes button and click it

 
 
 

Apply An Existing Theme In Mac Excel 2011

 

 
 

To apply an existing theme to your Mac Excel 2011 workbook, do the following:

1. Open the Excel 2011 workbook.
2. Click on the Home on the Ribbon
3. Click on the Themes button on the Home Ribbon.
4. Play with a few of the existing Themes until you find one that works for you, and you are done.

 
 
 

This is a really good graphic on what you can expect to see when you click the Themes Button on the Home Ribbon:

 
 
 

Here is a GREAT video on applying a Theme to an Mac Excel 2011 workbook:

 
 
 

Applying a Theme to your Mac Excel 2011 workbook is super easy!:

 
 
 

Here is what Microsoft says on the subject:

 

 
 
 

Like we said, this is easy to do.

 
 

 

Excel Hints: Choosing and Selecting the Source Data for an Excel Workbook

 

So you have an Excel workbook loaded with some sales data for your company and you want to create a chart so that you can see the trend. Sounds easy? It is that easy, just two keys on the keyboard is all that it takes.

 

Method One – Select a cell in the data table, then press “Alt & F1” keys to create a chart instantly

 

 

Method Two:

1. On the Insert tab, select the chart type you want.
2. On the Chart Design tab, select Select Data.
3. Click in the Chart data range box, and then select the data in your worksheet.

 

 
 

Did you know?Did You Know? If a value changes in the chart’s data source, that change is automatically updated in the chart!

 
 

 

Microsoft says, “Tip: If you don’t want to include specific rows or columns of data in a chart, you can simply hide them on the worksheet, or you can apply chart filters to show the data points you want after you create the chart.”

 
 

Microsoft explains:, how to select data for a chart.

 
 

It really is that easy. And it is just as easy to change they way they look and function. Well that ends this Excel Hint. Here is a link to our page with free online Excel and Access training videos.

 

 
 

 

Using The Microsoft Excel DATEDIF Function

 

Do you want to know the number of seconds, minutes, hours, days, weeks, month or years between two dates? If so, there are several ways to calculate it. One of the easiest is just to use the DateFif function in Excel. But many people have never heard of the DateDif function. We use it, so here it is.

 

The DATEDIF Excel function is a mystery to many people because it doesn’t have the popup help that the other functions have: Microsoft describes it like this “Calculates the number of days, months, or years between two dates. Warning: Excel provides the DATEDIF function in order to support older workbooks from Lotus 1-2-3. The DATEDIF function may calculate incorrect results under certain scenarios.” The “MD” argument may result in a negative number, a zero, or an inaccurate result. If you are trying to calculate the remaining days after the last completed month.

 

 

What is the use of Datedif function in Excel?
The Microsoft Excel DATEDIF function returns the difference between two date values, based on the interval specified. The DATEDIF function is a built-in function in Excel that is categorized as a Date/Time Function. It can be used as a worksheet function (WS) in Excel.

 

So it’s a leftover from a long time ago. And it’s still useful. Because Excel stores dates as serial numbers, actually the number of days since January 1, 1900, you can subtract one date from another and get the number of days between them. Besides Days, DATEDIF will convert the result into Years, Months, the difference in the Day part of the date, the difference in the Month part of the date and the difference in the Year part of the date.

 

 

So what to do? If it’s a quick, easy model and you’re not using the ‘MD’ argument, use DATEDIF. For longer term models where you might not know all the dates that are going to be used, use native Excel formulas like this:

 

 

• Start Date 1/1/17
• End Date 3/1/18
• Results
• DATEDIF Formula Formula Detail
• Y 1 1 =YEAR($B$2)-YEAR($B$1)
• M 14 14 =((YEAR($B$2)-YEAR($B$1))*12)+(MONTH($B$2)-MONTH($B$1))
• D 424 424 =$B$2-$B$1
• MD 0 0 =MOD(DAY($B$2)-DAY($B$1),DAY(EOMONTH($B$1,0)))
• YM 2 2 =MOD(MONTH($B$2)-MONTH($B$1),12)
• YD 59 59 =MOD($B$2-DATE(YEAR($B$2),MONTH($B$1),DAY($B$1)),365)

 
 

How do you calculate the time between two fields? If you have not used Datedif will you now?