Soutracting time in Google Sheets may seem simple, but formatting can sometimes complicate things and leave you with an incorrect answer. In this article, we’ll help you get the time difference in the easiest way possible.
Subtracting Time in Google Sheets to Get Duration
1. Prepare the time values you want to subtract
2. Subtract the values in the adjacent cell
Do this by using =B2-A2 in cell C2 and hit Enter.
Notice how the answer shows 1:00:00 AM instead of displaying a time.
3. Drag/Copy the formula to other cells
It’s important to check if there are no errors after subtracting the time values. The 8th row indicates 12:35 instead of a difference of 35 minutes. We’ll fix this issue using formatting.
4. Highlight the results to change the formatting
5. Go to Format -> Number and select Duration
6. Now, you finally have the correct time difference
Subtracting Duration from a Given Time in Google Sheets
Let’s say you want to subtract a certain number of hours or minutes from a given time, the steps below will show you how to do it.
1. Prepare your initial time values in the first column
You may notice that Google will treat your values by default as time values and add AM or PM at the end.
2. Enter the time values you want to subtract in the second column
Here, we’ll use an HH:MM:SS format to indicate how many hours, minutes, and seconds we’ll subtract from our values. If you want to subtract 1 hour, 20 minutes, and 32 seconds from our time value, type 1:20:32.
3. Change the formatting to Duration by going to Format -> Number and selecting Duration
This will ensure that our values are treated as durations and not day values. It will also allow you to subtract more than 24 hours without disrupting the differences.
4. Subtract the two values in the third column using the formula:
B2-A2
5. Drag the formula to other cells
We see that formatting numbers is essential for subtracting time in Google Sheets. Now, you can use this to meet your needs for event planning or analyzing time data. Crawlan.com can teach you how to add time values.
Use our email reminder software to set up custom reminders from your spreadsheet in just a few clicks.
If you enjoyed this article, you might also like our article on subtraction in Google Sheets or our article on subtracting dates in Google Sheets.
If you want to learn how to link emails in Google Sheets, we suggest checking out our detailed guide.
