If you have two timings⏳ in your screen and you want to calculate the difference between them or in other words you want to calculate the number of hours that fall in between, here’s what you can do:
As you can see in the image above, there is a start time as well as end time. This type of a data can be found in booking scenarios (airplane, train, bus, etc.)
To find the hour gap between the start time and the end time, we will begin with a simple formula.
As every formula starts with an equal to sign, we will begin with the same. After the same, we will select the end time as it is always the last thing that you select to subtract the first thing from it.
Exactly, this is the formula that we are going to follow:
=End time – Start time
After selecting cell D2 i.e. the End time, we will input the minus sign to create the difference and then we will select cell B2 which is the Star time.
By this, our formula becomes as follows:
After jotting down the formula, all we have to do is press enter and drag down the formula to the extent it is required.
Now as you can observe in the image above, there is a particular situation where the formula did not work out. This is because in this situation, the start time is 5:30pm and the end time is 01:00pm of the next day.
So, to deal with such situation, we will use the help of MOD Function.
The MOD Function is as follows:
The following formula requires number as well as divisor.
The number is the formula that we used before which is:
=End date – Start date
So, the number in the situation becomes D5-B5. The number is this formula becomes this is the formula that provides us the exact number of hours that fall between the start time and end time, but just because both the times do not end up on the same day, which is why we have to use the help of MOD Function🤷.
Now, comes the scenario of divisor. The divisor will be 1.
The overall MOD Function becomes as follows:
Now all we have to do is press enter, and the answer pops on the screen i.e. 19:10, this means that there is a 19 hours and 10 minutes of gap between 05:30 pm to 01:00 pm.
It’s easy, isn’t it🤷?