Apr 27 2018

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

 

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 time between two dates in Excel - Datedif

 

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.

 

How to calculate time between two dates in Excel - Datedif-graphic

 

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.

 
 
 

TAGS:
Mar 1 2018

Applying A Theme In Mac Excel 2011 Super Excel Hint

 

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

Microsoft Excel 2011 Mac 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

MS Excel for the Apple Mac has themes you can apply

 
 
 

Apply An Existing Theme In Mac Excel 2011

 

Excell themes

 
 

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:

Microsoft Excel 2011 makes Mac Themes easy

 
 
 

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!:

excel 2011 for the mac

 
 
 

Here is what Microsoft says on the subject:

 

microsoft-excel-2011-theme

 
 
 

Like we said, this is easy to do.

 
 

TAGS:
Feb 23 2018

Choosing and Selecting the Source Data for an Excel Chart

 

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

 

choosing-and-selecting-the-excel-chart-data-source-image

 

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.

 

choosing-data

 
 

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

 
 

excel-data-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.

 

 
 

TAGS:
Feb 16 2018

Using The Microsoft Excel DATEDIF Function

 

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.

 

microsoft-excel-datediff

 

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.

 

datediff-microsoft-excel

 

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:

 

datediff

 

  • 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?

 
 
 

TAGS:
Jan 19 2018

How To Pull Data From the Web Into A Microsoft Excel Workbook

 

Excel Hint: How To Pull Data From the Web Into A Microsoft Excel Workbook.

 

Being able to extract data from the web via a Microsoft Excel macro/VBA is pretty cool. It separates an intermediate Excel user from an Excel beginner.

 

This is often a gateway procedure that changes an Excel user forever; once you learn how to write this code, you see the possibilities.

 

Learning to write VBA to automate your work changes the game; puts you on the road to becoming an Excel guru!

 

Want an example, add code to your Excel file that will pull stock data every 5 minutes from the hours of 6am to 4pm, Monday thru Friday, and email you the workbook each day, without you doing a single thing. That is pretty f in cool!

 

microsoft-excel-vba-example

 

What are some of the reasons Microsoft Excel users use VBA to instantly extract financial data from the web?

  • Pull historical stock data
  • Download real estate listings
  • Extract contact records for sales leads
  • Pull data from member only sites
  • Download all sorts of statistical data

 

Excel Hint: LEARN HOW TO WRITE VBA/MACROS!!!

 

Do you want the code? Click here to read the full post with all of the details and the source code written by Jacob Hilderbrand, from Excel and Access, LLC and a Microsoft Excel MVP.

 
 

Do you want to have code like this but don’t know how to write it, don’t have the time to learn, no worries, Jacob will write the Excel VBA for you. This is just one of our paid Microsoft consulting solutions that we offer.

 
 
 

TAGS:
Page 1 of 29123451020...Last »

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives