Now, we will click on the Advanced option from the left pane. ⦿ Another window titled Excel Options will appear.

Click on the Options like the image below. ⦿ First, click on the File tab on the left of the Home tab. We can change the default date system to the 1904 date system to display the negative time after subtraction in the correct format. Method 1: Use the 1904 Date System to Subtract and Display Negative Time in Excel The image below shows the worksheet that we are going to work with. The worksheet has the Rank, name of the Cyclist, Finish Time, Time Difference between two successive cyclists, and the Corrected Format to show the negative time after subtraction. We have ranked the cyclist in ascending order from the winner of the contest to the last ranked cyclist. Let’s assume a scenario where we have an Excel file that contains information about the time 6 cyclists took to complete a cycling contest. In this tutorial, we used a simple example to demonstrate how we can add or subtract hours from data and time in Excel to highlight a particular set of data.3 Methods to Subtract and Display Negative Time in Excel If the data is not visible in the result, then we need to format the cells as dd-mm-yyyy hh:mm in the custom. Now to subtract 12 hours from the cell of A2, enter the formula as =A2+12/24 and click on enter to get our first result, as shown in the below image.Īfter getting the first result, we can get all the other results just by dragging from the first result until all the other results are filled, and our final output will look similar to the below screenshot. Let us consider the same data that we have used in the above example. Let us see the process to know how we can subtract hours from a date/time field in Excel. Here, we will first use the formula to get the first result, then use the auto-fill handle. If we want to add 12 hours to the cell of A2 and click on Enter to get our first result, as shown in the below image,Īfter getting the first result, we can get all the other results just by dragging from the first result until all the other results are filled, and our final output will look similar to the below screenshot. Let's go over a simple procedure for adding hours from a date/time field in Excel.Ĭonsider the data that contains a list of dates and times in a single cell, which is similar to the data shown in the below screenshot.

Here we will use the formula to get the first result, then use the auto-fill handle to complete the task. So let us see an article about how to add or subtract hours from any given date and time. This article will help us in solving this problem in a simple and efficient way. When we want to add or subtract a specific time from a list where they are the combination of the date and time manually, then it will be a very complex and time-consuming process.

Excel formula to subtract time