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.

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.

[Read more…] about Excel Formula for Elapsed Time in Days, Hours and Minutes

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.

[Read more…] about Count How Many Times a Specific Character Appears – Excel Formula

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.

[Read more…] about Excel Formula to Extract Postcode from an Address

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.

[Read more…] about Display a Symbol Conditionally using the IF Function