The WEEKDAY Function in Excel – Two Examples

Excel has many wonderful date and time functions. These functions are some of the most popular in Excel.

One that drifts under the radar a little is the WEEKDAY function. This function will return a number that identifies the day of the week of a date.

This can be very useful. This blog post will look at two examples of what the WEEKDAY function can do for you. Continue reading

Excel Formula for Elapsed Time in Days, Hours and Minutes

Having to calculate date and time difference in Excel is a common task. Unfortunately, depending on your requirements it is also not always a simple one.

Take this example where column A contains a start date and time, and column B an end date and time. We wish to calculate the elapsed time in days, hours and minutes e.g. 11 days 4 hours 9 minutes.

Difference between two date and times

There are multiple ways of calculating date and time difference in Excel. In this scenario we will need to get a little clever.

As you may well know, date and time values are stored as numbers in Excel. For example, the 05/01/2017 10:10 is stored as 42740.42.

Therefore, if I write the formula as =B2-A2, then the result is returned as 2.993056.

To return a result that makes sense to us, we will tackle the date and time parts of the cell separately. Continue reading

Count How Many Times a Specific Character Appears – Excel Formula

In this blog post we will uncover an Excel formula to count how many times a specific character appears in a cell.

The formula used in this tutorial will work for any text character, and can also be used to count the occurrences of specific words in a cell.

In this example, we used the formula to count the occurrences of the asterisk in the cells of column A.
Count occurrences of a specific character
Continue reading

Excel Formula to Extract Postcode from an Address

I was asked recently in class how to extract a postcode from an address in the UK. The person asking needed a formula because the spreadsheet updates often and they wanted an automated solution.

The problem with extracting UK postcodes is that they are highly irregular. They will be at the end of the full address and can come in a different number of characters e.g. E1 6AX, RM3 8HN and LE41 8JX.

They are not as structured as a US zip code may be and harder to extract. Because of this the formula is intense, but I am going to break it down and explain it in detail.

Extract postcode from UK address with Excel formula

Continue reading

Display a Symbol Conditionally using the IF Function

This blog post looks at using the IF function to display a symbol conditionally in a cell. In the image below a thumbs up or thumbs down symbol is shown dependent upon whether the sale of products have improved since last month.

This tutorial will show you how to display any symbol though, so you could insert a smiley face, hour glass, aeroplane and much more.

Display thumbs up symbol using IF function Continue reading

Excel Formula to Find the Least Frequent Value

This tutorial explains an Excel formula to find the least frequent value in a list. This formula will work whether the value is a number, or text. In this example we want to return the name that occurs the least.

The spreadsheet below shows a list of names with the answer in cell D2. Ross is the name that occurs the least in that list.

Excel formula for least frequent value

This formula returns the least frequent value from the list in A2:A16. The formula is explained below so keep reading.

{=INDEX(A2:A16,MATCH(MIN(COUNTIF(A2:A16,A2:A16)),COUNTIF(A2:A16,A2:A16),0))}

Continue reading

Count How Many Fridays Between Two Dates

You may need to count the occurrences of a specific weekday between two dates in Excel. For example, how many Fridays between now and a specific date.

There is no real standalone function in Excel to do this, but it can be done. This could be a useful formula to find how many payments, or how many meetings until an end date.

How Many Fridays Until a Specific Date – Excel Formula

The formula below calculates the number of Fridays between the date in cell A2 and the date in cell B2. The formula is explained below.

=B2-A2-NETWORKDAYS.INTL(A2,B2,16)+1

Count the number of fridays

Continue reading

Excel Formula to Display the Sheet Name in a Cell

This blog post looks at using an Excel formula to display the sheet name in a cell. By finding the sheet name using an Excel formula, it ensures that if the sheet name is changed, the formula returns the new sheet name.

For the formula we will be using the CELL, MID and FIND functions. Let’s begin by looking at the CELL function.

The CELL function is a fantastic, and relatively unknown, function in Excel. Its purpose is to return information about a cell such as its column, cell colour, whether it is in a protected state or the filename. Continue reading

Euro 2016 Planner – Excel Template

Euro 2016 is here and I have a Euro 2016 planner to share. The spreadsheet has a separate sheet for the group stage and the knockout stage. Enter the results into the boxes on the sheets and everything is calculated automatically.

The group league tables are ranked as the results are entered. As soon as a groups fixtures have been completed, the knockout schedule is created. The quarters, semi-final and final fixtures are then calculated as these results are entered.

The spreadsheet is unlocked so you are free to check out the formulas and how it all works. Download the Euro 2016 planner spreadsheet.

Euro 2016 Planner Template
Continue reading

Use VLOOKUP to Find the Last Match in a List

VLOOKUP is an awesome Excel function. We all know this. But it certainly has its limitations. One of these is that it can only return the first match from a list.

This is great when looking for a unique value. But what about when the value you are looking for appears multiple times in the list, and you want to return the last match.

Sure we could sort the list so that the last match would become the first, but this is not always an option.

This blog post looks at using the VLOOKUP function to return the last match in a list. The technique and formulas used can be adapted to find the 2nd or 3rd match if required.

VLOOKUP to find last match

Continue reading