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:
Jan 16 2018

How To Assign A Number Format To A Cell In Microsoft Excel 2019

 

How To Assign A Number Format To A Cell In Microsoft Excel 2019

 

There is more than one way to skin a cat, or to assign a number format to a cell.

 

Do a Google Search for “assigning a number format to a cell in excel”, and Google Says:

  1. Select the cell or range of cells that you want to format.
  2. On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list.
  3. In the Format Cells dialog box, under Category, click Custom.

 

assigning-a-number-format-to-an-excel-cell-2019

 

Do you want to create a custom number format, Microsoft shows you how.

 
 

Said another way, to assign a number format to a cell, do the following:

  1. Select the cell or cells you want to assign format to.
  2. On the Home tab click the format button in the cells group.
  3. Select Format Cells.
  4. Select a number format in the category box.
  5. Enter or select options in the right pane.

 

formatting-microsoft-excel-2016-excel-hints-website

 

Do you have any questions on this subject, or any subject for that matter in any of the Microsoft applicaitons? If so, get those questions asnwered free, in a timely manner by visiting our Microsoft Help Forum, VBAExpress.Com.

 

TAGS:
Jan 5 2018

How Microsoft Power BI Dashboard Presentations Exceed Expectations

 

Excel Hint – How to Make Your Microsoft Excel Dashboard Presentations Exceed Expectations via Microsoft Power BI (Power Pivot)

 

With the advanced data manipulation and data analysis techniques used by Excel’s data scientists and data visualization experts there is a quick way to make visual and numerical sense of the super large data sets possible in Excel 2010 and on, data that is appropriately termed ‘BIG Data’. The tool that has both the speed and the power to make impressive Microsoft Excel dashboards is Microsoft Excel Power BI and Power Pivot. Microsoft Power BI Dashboards have allowed data scientists to tame the BIG data beast…

 

bring-your-data-to-life-micrpspft-power-bi

 

Imagine this scenario, you report to the Senior VP of Finance for an S&P 100 firm. Your boss has asked you to put together a financial presentation of KPI for the senior team of your organization. You have one week to accomplish this monumental task, and the failure or success of this presentation sits strictly on your shoulders. In addition to the limited amount of time to accomplish this task is the sheer amount of data you have to collect, sift through and organize. We are taking BIG data here; millions of records spread across Excel workbooks, Access tables and SQL Server databases. Currently you do not have a solution in place to accomplish your task. What you need is a powerful Microsoft relational database. So what do you do?

 

How do you make this even possible in one short week, even if you can gather the data? How can you turn these desperate data sources a powerful relational database? How can you make your presentation actually presentable, organized and visually appealing?

But don’t fret, you are a well seasoned data scientist and data visualization expert and you know that in order to grasp the required data and in order to make it truly sing, Microsoft Power BI, and more particularly, Power Pivot is the only way to go. Microsoft Power BI dashboards with their slicers are the most impressive way to present your findings to the senior team, and the drill-down capabilities make it a snap to use and it will exceed their expectations.

 

Do you want to know more? Is this Excel hint just that, a hint, and you want to read the details? Click here to read our post on Microsoft’s Power BI covers the following points:

  1. Why BI is the Basis for Presentations
  2. Reports and Your Reputation
  3. Appearing Intelligent with Advanced Analytics
  4. Demonstrating Data with Vibrant Visualizations
  5. Are You Ready to Boost Your Presentation Power?

 

TAGS:
Dec 6 2017

Microsoft Excel Keyboard Shortcut – How To Insert Column or Row

 

Microsoft Excel Keyboard Shortcuts – How To Insert a New Column or Row

 

Knowing how to insert a row or column via a keyboard shortcut should be one that you know. Inserting rows or columns is a staple in Microsoft Excel, just as is deleting them. Of course this is for those that do workbook development and not just using what has been built.

 

29 Excel Hacks IG-number-insert-rows-columns

 

The image above shows you how to insert a new row or column into an worksheet via a keyboard shortcut. It shows how to do this in both versions of Microsoft Excel, for the PC or the Mac. Give it a try.

 

You too can become a keyboard shortcut master; you don’t need to learn them all at once. Just learn one or two per month and before you know it, you will be flying through your Excel files, without touching the mouse.

 

In Microsoft Excel 2013: Modifying Columns, Rows, and Cells image via gcflearnfree.org

microsfot-excel-keyboard-shortcut-insert-row-or-colunm

 

Have we not covered a keyboard shortcut that you use on a regular basis? If not send us an email and we will make sure to cover it.

 

If you have questions on how to use any of the Microsoft applications, including VBA, visit our Microsoft Help Forum, VBAExpress.Com. If you want to ask questions on Excel VBA, you possibly have code that will not work, get help for free, Free Microsoft Excel Help Forum.

 

If you would like to get a free copy of the 29 Microsoft Excel Keyboard Shortcuts chart please send us an email and we will get that right out to you, it has been made available to you via SiegeMedia.com.

 

Page 1 of 28123451020...Last »

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives