Mukesh Dipto
In this article, we will explain how to subtract hours from the time in excel. If we want to subtract any amount of hours from a particular time we can easily determine that by using different features of excel. Microsoft Excel does not provide any special function to subtract hours from time. So, we will apply multiple functions or formulas to subtract hours from the time in excel.
Download Practice Workbook
You can download the practice workbook from here.
2 Easy Ways to Subtract Hours from Time in Excel
We will show you two easy ways to subtract hours from the time in excel. To clarify the concept of this article, we will show you two easy ways to subtract hours from the time in excel.
1. Subtract Less Than 24 Hours from Time in Excel
When we want to subtract a specific amount of hours from the time in excel we need to consider some facts. The first thing we need to consider is whether the amount of hours we want to subtract is more than 24 hours or not. In this first method, we will demonstrate how to subtract hours from time if the subtraction amount is less than 24 hours.
1.1 Apply Fundamental Method to Subtract Hours
First and foremost, we will apply the basic method to subtract less than 24 hours from the time in excel. From the dataset, we can see we have a schedule of starting time of six football matches. Suppose, all the matches have been rescheduled and will start 2 hours earlier. So, we need o to subtract 2 hours from the starting time of all the football matches.
Let’s see the step by step guide of how we can subtract less than 24 hours from time:
STEPS:
- Firstly, we will fix the time format like the dataset that we have given.
- To do this, go to the Home tab. Click on the dropdown from the number section in the ribbon.
- Then select “More Number Formats”.
- Secondly, a new dialogue box will appear. Select the option “Time” from the Category.
- Then from the Type section select the option “*1:30:00 PM” and press OK.
- The above actions will set the time format for the worksheet as “*1:30:00 PM”.
- Thirdly, select cell D5 and insert the following formula:
=C5-(2/24)- Then, press Enter.
- The above command subtracts 2 hours from the start time of cell C5 and returns output in cell D5.
- After that, select cell D5. Move the mouse cursor to the bottom right corner of the selected cell so that it turns into a plus (+) sign like the following image.
- Then, click on the plus(+) sign and drag the Fill Handle down to cell D10 to copy the formula of cell D5 in other cells. We can also double-click on the plus (+) sign to get the same result.
- Now, release the mouse click.
- Lastly, we can see the updated schedule for all matches after subtracting 2 hours from the start time.
1.2 Subtract Hours with Excel TIME Function
We will do the previous task again with the same dataset that we used earlier. But, this time we will use the TIME function to subtract 2 hours from the start time of all the matches in the following image. The TIME function allows us to build a time with discrete parts for each hour, minute, and second.
So, let’s the steps to use the TIME function to subtract time less than 24 hours:
STEPS:
- First, select cell D5. Insert the following formula in that cell:
=C5-TIME(2,0,0)
- Next, press Enter. This action subtracts 2 hours from the start time of cell C5 and returns output in cell D5.
- Then, select cell D5. Make the plus (+) sign visible like the following image by moving the mouse cursor to the bottom right corner of the selected cell.
- After that, to copy the formula of cell D5 in other cells click on the plus (+) sign and drag the Fill Handle down to cell D10. Another way to do this is to double-click on the plus (+) sign to get the same result.
- Now, release the mouse click.
- Finally, we can see the new schedule for all matches after subtracting 2 hours from the start time.
Read More: How to Subtract Minutes from Time in Excel (7 Methods)
Similar Readings
- How to Subtract and Display Negative Time in Excel (3 Methods)
- Excel Formula to Calculate Hours Worked Minus Lunch
- Calculate Hours Between Two Times in Excel (6 Methods)
- How to Subtract Military Time in Excel (3 Methods)
- How to Subtract 30 Minutes from a Time in Excel (8 Easy Ways)
2. Excel Subtract More Than 24 Hours from Time
The above two methods are only applicable for subtracting less than 24 hours. If we want to subtract more than 24 hours from a specific time we have to use another approach. To illustrate this method we will use the same dataset but this time we also have dates with time and will subtract 26 hours from time. So, if we subtract more than 26 hours from a time the date will change automatically.
So, let’s see the steps regarding this method:
STEPS:
- In the beginning, set the time format like the dataset. Here our time format is a Custom format.
- Go to the Home Click on the dropdown from the Number section in the ribbon.
- Then select “More Number Formats”.
- Next, a new dialogue box will appear. From that box select the option Custom from the Category.
- Type the format “dd mmmm yyyy, hh:mm:ss AM/PM” in the box of the Type.
- Now, press OK.
- So, the above actions will fix the time format for the worksheet as “dd mmmm yyyy, hh:mm:ss AM/PM”.
- After that select, cell D5 and insert the following formula:
=C5-(26/24)
- Now press Enter. This action subtracts 26 hours. We can see that the previous date of starting time was “28 February 2022, 12:30:00 PM” and the present start time is “27 February 2022, 10:30:00 PM”.
- Next, to make plus (+) sign seeable like the following image select cell D5. Drive the mouse cursor to the bottom right corner of the selected.
- Then, drag the Fill Handle down to cell D10 by clicking on the plus (+) This action copies the formula of cell D5 in other cells or double-click on the plus (+) sign.
- Finally, we can see the start time for all the matches after subtracting 26 hours.
Read More: How to Subtract Date and Time in Excel (6 Easy Ways)
Conclusion
In conclusion, This article is a guide to subtract hours from the time in excel. To put your skills to the test, use the practice workbook that comes with this article. Please leave a comment below if you have any questions. Our team will try to reply as soon as possible. In the future, keep an eye out for more interesting Microsoft Excel solutions.
Related Articles
- How to Calculate Average Handling Time in Excel (2 Easy Ways)
- How to Add Time in Excel Over 24 Hours (4 ways)
- Excel Formula To Calculate Time Worked
- How to Calculate Average Response Time in Excel (4 Methods)
- Subtract Date and Time in Excel (6 Easy Ways)
- Excel formula to calculate hours worked & overtime [with template]
- How to Calculate Elapsed Time in Excel (8 Ways)
- Excel Formula for Overtime over 40 Hours [with Free Template]
