You need to add more information to questions, such as what format you're entering data in, whether you're entering just start and end times or whether there are multiple records etc.
It will help you to understand how Excel calculates dates and times. A date is a whole number between 1 (01 Jan 1900) and 2,958,465 (31 Dec 9999), but shown in a way that looks like a date. Similarly, a time is the decimal element of a number, so 8:00am = 8/24 = 1/3 = 0.3333, and 12 noon = 0.5. Change your number formats (ctrl + 1) to see this in action
So if you enter a pair of time values, you can simply subtract the start from the end, giving you a duration in time format. Similarly you can add a series of pairs and subtract the sum of starts from the sum of ends. Working with times like this is far preferable to trying to add whole numbers and then calculate the differences yourself - Excel has already taken account of the nuances that you need to consider. Bear in mind that if working past midnight you'll need to add an additional day to the end time in order to calculate correctly: for example 8pm to 8am = 12 hours = 0.5 as the underlying number = 1.333333 - 0.8333333 = (1 day 8 hours) - (0 days 20 hours)
Hi Bezzina, it looks like you want your analysis on a line by line level, which can be done using HOUR and MINUTE along with a nested IF. I've broken it into several parts to help reading, and because there's a lot of repetition so I'm looking to reduce the total number of calculations
I've placed your sample times in cells A2:B3
I then get the key outputs for simplicity in reading and creating the main formulas
I2:AG2 = 0, 1, 2 etc
Main formula in I2, copies across and down: