Vinai Prakash is the founder and master trainer of ExcelChamp.Net.
Vinai has over 30 years of experience in using Excel, and is a Data Warehousing & Business Intelligence Professional.
Vinai has coached and trained over 30,000 professionals in using Excel, Power BI, Python, SQL, and regularly runs Excel Data Analysis, Excel Dashboard MasterClass, Excel for Beginners, Advanced Excel MasterClass online and all over the world.
Vinai's Videos on YouTube channel and his blog articles and newsletters are read by thousands of subscribers all over the world.
Vinai lives in Singapore with his wife and 2 kids.
Some of the attendees at my Excel Boot Camp trainings have been thrilled to find that there’s this nifty function, which can combine many text columns into a single column.
Let’s say you have a column with First Name, and a separate column for Last Name, and you want to combine them to form the full name. How would you do it?
Most beginners to Microsoft Excel are not aware how easy it is to use some simple functions.
I have recorded a detailed, step by step video, which shows how to use the Sum, Count, Average, Minimum and Maximum functions in Excel, to add, count numbers.
Do let me know if this helps you in using Microsoft Excel. The techniques applied here are same for Excel 2003, Excel 2007, Excel 2010 or Excel 2013, Excel 2016 & Office 365, so it does not matter which version you are using.
It has always been possible in Microsoft Excel to find the difference between 2 dates. And there are hordes of other date and time functions in Excel, some of which I wrote earlier about.
Today, let us look at a recently added function in Microsoft Excel, which can help you find the difference between any 2 dates, and this function works beautifully, even if your weekend does not fall on a Saturday/Sunday.
For example, recently I was in Cairo, Egypt to conduct Corporate Training, and found out that their weekends fall on Friday and Saturday.
The regular function that I used earlier – NetworkDays() – is good at finding the days between any 2 dates, but it assumes that the weekend is on Saturday/Sunday.
Fortunately, Microsoft has added an International Function in Excel, which helps to work this out and makes it fairly easy.
The function is NETWORKDAYS.INTL().
It takes 4 arguments – Start_date, End_date, Weekend & Holidays. The last 2 are optional, but that is where the function shines and differs from the Networkdays() function.
The weekend argument can take one of these several values, and when you type the formula, this helpful guide pops up.
As you can see, you can easily select any single day or two consecutive days as your weekends.
For Egypt and Dubai, their weekend of Friday, and Saturday has the value of 7.
Thus, I choose the following function:
=NETWORKDAYS.INTL(“1-Sep-2012”, “31-Sep-2012”,7)
and promptly got the result as 21, which is the number of working days in this period, excluding Fridays & Saturdays.
If you only use =NETWORKDAYS(“1-Sep-2012”, “31-Sep-2012”)
you will get the answer as 20 days, which is based on the Saturday/Sunday weekends.
So there you are. Have your cake and eat it too! You can check out these functions in the difference-between-dates Excel file, which you can use to practice and test out these functions.
Anything else you’d like covered here? Do post a comment and let me know.
There are many in-built Excel Functions that can help you in working with Dates and Time. It can assist you in showing today’s date, the current time, and represent dates as Months, Years, and even calculate difference in 2 dates.
1. Getting Today’s Date
In the cell where you want to display today’s date, key in the following formula: =TODAY()
Depending on your PC’s Regional Settings (Setable in Control Panel), you will get today’s date displayed in MM/DD/YYYY or DD/MM/YYYY format.
The problem will this approach is that every the Excel file is re-calculated, the date will change and display the latest day’s date.
If you wish to only display today’s date, but do not want it to change in the future, there’s a little known shortcut to do this. Go to the cell where you want to get today’s date, and simply press Ctrl + ;
2. Getting Today’s Date & Time
In you want the date and time, both to be displayed, more like a time stamp, use the following formula: =NOW()
It will display as 24/7/2012 12:33
If you only want the time, like a time stamp, and do not want the time to ever change, then simply press the following keys: Ctrl + Shift + ;
And if you want to display both the date and time stamp in a single cell, combine the 2 functions
Select a cell and press CTRL+; then press the SPACE BAR to add a space, and then CTRL+SHIFT+;
Up to Microsoft Excel 2003, there was a in-built Custom Chart Type called the 2-Axis Chart. This was a pretty useful chart type, in which I used to display very small numbers and very large numbers – all on the same chart. You can see in this chart below, the numbers on the Primary Y Axis on the left are from 0 to 16. The numbers on the Secondary Y Axis (on the right side of the chart), are from 20,000 to 180,000.
2 Axis Chart in Microsoft Excel
But this 2 Axis chart type magically disappeared from Excel 2007, Excel 2010 & even Excel 2013. So today, I will show you how to create a 2 Axis chart in any version of Excel– whether you are still using Microsoft Excel 2007 or Microsoft 2010, or the latest Excel 2016, 2019 or Office 365 (Cloud version of Microsoft Office).
What is a 2 Axis Chart?
The default Bar Chart or a Column chart of Excel has One X Axis (The Horizontal side), and One Y Axis (represented Vertically). This is generally useful most of the time.
However, once in a while, when you want to display 2 different sets of numbers in the same chart, and one set of numbers (Quantity) comprises of very small numbers, and the other set of numbers (Sales figures) are very large – in Thousands.
If you plot a normal Column chart, the Sales figures are so high that the bars are quite long, and the scale is set in Thousands. So the Quantity figures are too small to be even seen in the Column Chart.
A 2 Axis chart is slightly different from a normal, standard Column Chart.
The 2 Axis Column Chart uses TWO Y Axis in the same chart.The one on the Left of the chart is used to display one scale (Small Numbers), and another Y Axis is added on the Right Side, which represents the (Large Numbers) in Thousands.
In this way, both small numbers and very large numbers are seen in the same chart – using both the Primary Axis and the Secondary Axis.
However, do note that the Columns of the Chart will overlap each other, and it will be difficult to see one set of values. To overcome this situation, we convert one of the bars into a Line Chart.
In this way, even if the scale is different, both axis can be seen clearly, and a useful 2 Axis chart is created easily in Microsoft Excel.
