In case you have time values in Excel, what you really have in the back end in Excel are decimal numbers that represent that time value (which are formatted to be shown as time in the cells).Īnd since these are numbers, you can easily subtract these.īelow I have a data set where I have the start time and the end time, and I want to calculate the difference between these two times. While the day part of the date would be represented by the whole number, the time part would be represented by the decimal.įor example, 44197.5 would represent 12:00 PM and 44197.25 would represent 09:00 AM Example: Find the answer to 7-3 in a cell. This combines calculating the difference and the average all in a. Just like dates, even time values are stored as numbers in Excel. Type (without the quote marks) and then type the numbers separated by a hyphen -. You can remove the whole of column E from the calculation by using an array formula. The structure is: number1 - number2 A space may be placed between each element, but isn’t necessary. Remember that any numbers you want to subtract should have a minus sign ( - ). You can read more about subtracting dates in Excel here Subtract Time in Excel A subtraction formula in Excel consists of the equal sign, the values or cell references to be used in the formula, and the minus sign, represented by a dash (-). Enter the numbers you want to use in each cell from A1 through A10. So you won’t be able to subtract dates with such a format For example, if you use, Excel won’t recognize it as a date and consider it a text string. Note: This formula would only work when you using a date that Excel recognizes as a valid date format. You can easily adjust this and get the values in numbers by going to the Home tab, and select General in the number format drop-down. This sometimes happens when Excel tries to be helpful and pick up the format from the adjacent column. There is a possibility that Excel will give you the result in the date format instead of a number as shown above. In case you are only using VBA to perform this one calculation, and would prefer to just use a native Excel formula, you could use the following: Sum (If.

Subtracting in excel in one formula