24 Hour Clock Calculations in Excel

Recently I’ve been doing some work with Excel to carry out some time calculations based on the results of an automated job. The job is sometimes triggered one night and finishes in the early hours of the next morning. After loading the figures into Excel I discovered that time calculations aren’t always as simple as subtraction when times cross midnight. A quick test later and I had a workable solution that I thought might be useful to share.

The screen shot below shows some simple time calculations subtracting the second time (T2) from the first time (T1). As you can see the first and second rows of times calculate correctly. The third row however shows up as a series of hashes.

Hovering over the cell containing the hashes produces a pop-up that reveals the result may be a negative result - which in this case would indeed be true.

Selecting the absolute value would yield the same result as the first calculation (3:45), but in reality the time elapsed between these two times is 20:15.

The solution is just to use a conditional test to check if T2 is bigger than T1. If it is then a normal time calculation will suffice. If it isn’t then the time difference would be equal to midnight minus T1, plus T2. For cells A4 (T1) and B4 (T2), this can be expressed as the following Excel formula.

``````=IF(B4>A4,B4-A4,24-A4+B4)
``````

This results of this formula are shown for each pair of timings in column C in the screen shot below.

This would have to be replicated for each time calculation. If you have many calculations and perhaps might want to include additional calculations it may make things easier to use a VBA function to carry out this calculation. The function below does exactly that.

``````Function Elapsed(tmStart, tmEnd)
If tmStart <= tmEnd Then
Elapsed = tmEnd - tmStart
Else
Elapsed = 24 - tmStart + tmEnd
End If
End Function
``````
Author: Stephen Millard
Tags: | excel | vba |

• Excel - Failed Tabbed Data Paste

02 May 2019

In my day job I work with a lot of raw data, and I make judicious use of Microsoft Excel to help me in the builds and transforms I need to accomplish. As many people are no doubt aware, Excel can be quite useful with the use of tab separated text as it treats each tab as a way to separate data out into cells and each new line as a new row in a spreadsheet. At least that’s what I thought. Recently however, I fell foul of finding tab separated data on my Windows clipboard not pasting into Excel in this way. But I did track down why this occurred.

• Excel: Count Lines in a Cell

30 Nov 2017

Recently I was using an Excel spreadsheet to prepare some data and I had a constraint to work to in the number of lines of data the target system could display. Unfortunately rather than rows of data in Excel this was to match against rows of data in a single Excel cell. So the question is, how can you count the number of rows in an Excel cell?

• Word - Refresh All Fields

19 Jul 2017

In my work, I write a lot of documents. A surprising amount in fact, probably equalled only by the number of spreadsheets. The majority of these (Microsoft Word) documents are produced for clients and I like to ensure as, best I can, that they are not only accurate but well presented. However, in order to simplify document production I make judicious use of templating and Word’s fields functionality. I use not only standard fields but also custom ones as well to update content in multiple places simultaneously on refresh.

• Batch Generating Worksheet Hyperlinks in Excel

29 Apr 2017

In my work I often find myself building spreadsheets to carry out calculations, manage sophisticated processes and capture information. These spreadsheets can often extend to covering many worksheets and as a result it can be awkwardly slow to navigate to particular worksheets just by paging back and forth with the navigation buttons.

In this post I’m going to illustrate a couple of useful techniques to help you navigate around.