PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
BEFORE READING THIS ARTICLE , IT IS REQUESTED TO VISIT THE PART I WHICH DISCUSSES THE CONCEPT OF THE DATE AND TIME IN EXCEL FOR BETTER UNDERSTANDING.CLICK HERE TO VISIT.
Time is just another number for Excel. We can subtract it the same way as numbers but with a bit care.
Just like the addition, we can simply subtract the time arithmetically. For example
If a cell contains 12:00 and one contains 4:00 we can simply subtract the 12:00-4:00 and the result would come as 8:00
The formula used is D8-E8.
Let us try the different cases and learn how to tackle different situations.
TIME 1: 15:00 HRS
TIME 2: 8:00 HRS
DIFFERENCE= TIME 1- TIME 2= 7:00
As shown in the picture below.
This particular case is for certain situations where we are calculating the work hours for a night shift.
Mostly, the night shift starts on the day 1 and ends in the morning of day 2.
This is the situation where our simple subtraction fails.
Let us try an example where the night shift starts at 10 pm and ends at 7 am.
Let us try to find out the work hours.
We put the formula as SHIFT END TIME-SHIFT START TIME [C15-B15 FOR OUR EXAMPLE] but the result didn’t show and #### appeared.
It appears when the result is negative.
WHEN WE DOESN’T MENTION THE DATE WITH A TIME, THE DEFAULT DATE IS TAKEN WHICH IS JAN 1 , 1900. ThE TIME DOESN’T GO WITHOUT A DATE WHETHER YOU MENTION IT OR NOT.
Let us try to sort this problem out.
This particular case is for certain situations where we are calculating the work hours day or night for the different dates. Example 2 has been discussed specifically for the NIGHT SHIFTS only.
It appears when the result is negative.
THE DATE AND TIME CAN BE ADDED TOGETHER TO CREATE THE COMPLETE NUMBER INCORPORATING THE DATE AND TIME TOGETHER. IT CAN BE SIMPLY SUBTRACTED AND CONVERTED BACK TO THE FORMAT WE WANT.
Let us try to sort this problem out.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE