Google Sheets – Handle Negative Time Delta

As user3169 wrote, one can simply take the difference of time values (this works in Google Spreadsheets the same as in Excel). Specifically, the command

=ROUND((F2-E2)*24)

calculates the number of hours between the time values in the cells E2 and F2, rounded to the nearest whole hour. Similarly,

=ROUND((F2-E2)*24*60)

calculates the number of minutes between the time values in the cells E2 and F2, rounded to the nearest whole minute. Etc.

ABOUT DATES IN GOOGLE SHEETS

Dates are stored as serials expressed as the number of days since 12/30/1899 where 1 is equal to 12/31/1899.

Hours are stored in the same format but simply converted from days. So if 1 day is equal to the integer value 1. Then 12 hours is equal to 0.5 .

Both options below Assume that your Arrival Date is a date value & not a string that looks like a date. To Clarify if you type in 1/5/16 Google Sheets will automatically store this as a date-value. Such that if you select the cell containing it and look at the formula bar, you'll see "1/5/2016" This also works for the way you wrote the date Jan 5 is assumed to be 2016 unless you otherwise specify. If you click on the cell where you wrote "Jan 5" and look at the formula bar, it should say "1/5/2016" instead of "Jan 5" You can work with strings and try to find creative ways to parse them, but why reinvent the wheel?

Also I am assuming that 1d = 8h

(Option 1) RECOMMENDED

Yes -- replace & convert your Estimate data into numbers instead of strings. We are doing math and strings will only complicate things.

If users input data you should add validation that prevents non number entries. You can change the number format to include the word "days" after the number. So 8 hours = 0.3̅3̅3̅ .

Then it is simple addition, just add that the Estimate Column to your Arrival Date Column to Obtain your Expected End Date.

If your original data is in range A1:C5 & your Expected End Date column is Column D. Your formula may look like =$B2/8+$D2 & simply copy it down. Or you can use an array formula =FILTER(B2:B/8+C2:C,B2:B<>"",C2:C<>"")

(Option 2) NOT RECOMMENDED

If you wanted to keep the Estimate column a string. You will need to keep a consistent convention for how those strings are written. Your project will get increasingly difficult if you allow any kind of date-string.

EX if you had three different estimates:

• 1 day, 2 hr
• 2 hours 1 d
• 2h 1d

This will be difficult because you'll have to anticipate each type of formatting. And write a versatile enough formula to catch them all.

Once you get a consistent convention You simply extract the day denominations and express them as a natural number. Such that 1 day = 1. Then you extract the hour and divide it by 24 and add it to the value you obtained for days. This should give you a positive decimal value -- We'll call this value estimate-in-days

You can then add that number directly to the Arrival date to get your Expected End Date.

There should be no need for a modulus unless you want to go backwards and subtract the Arrival Date from the Expected End Date and convert that value into a string expressed in two denominations: Days & Hours.

Assuming that Column D is your Expected End Date. Your formula may look like =IFERROR(SUMPRODUCT({1;DIVIDE(1,8)},TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(B2,"d",),"h",)," "))),LEFT(B2,SEARCH("h",B2)-1)/24)+C2

Or as an Array for the whole column =FILTER(IFERROR(SUMPRODUCT({1;DIVIDE(1,8)},TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(B2:B,"d",),"h",)," "))),IFERROR(LEFT(B2:B,SEARCH("h",B2:B)-1)/8,0))+C2:C,B2:B&C2:C<>"")