1

I'm a software engineer but pretty unfamiliar with Sheets. I have a list of tasks and hour estimates, and I need to find out the expected end date of each one. Here's an example with an assumed starting date of Jan 4:

| Task Descriptions | Estimate | Arrival Date |
|            Task 1 |       8h |        Jan 5 |
|            Task 2 |       2h |        Jan 6 |
|            Task 3 |       1h |        Jan 6 |
|            Task 4 |    3d 6h |       Jan 10 |

For simplicity's sake, I can change the second column to just be hours instead of a string. I need to have only 8 hours per day. I know how I'd solve this in most programming languages, but I can't figure out how to do it in Sheets.

I'm sure this is something that has been dealt with in the past. Any ideas?

  • If you only have 8 hours per work-day does that mean that 3d 6h is really almost 9 work days? Or Does that mean 3 work days and 6 extra hours? If so Why would you write 8h instead of 1d? – Xzila Aug 12 '16 at 21:14
1

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<>"")

-2

In a programming language, you'd likely use the modulo function, variables, a loop statement and possibly an array.

In a spreadsheet, you just put those things into helper columns or cells. You could get fancy and write a script, which is more like what you're familiar with - but that does make it harder for non-programmers to understand.

See an example here of how to do it with helper columns: https://docs.google.com/spreadsheets/d/18xlkm9nd3BH0XlqlppmRqO4Ev7wz9bo8q2Pvi45pcmY/edit?usp=sharing

Note that I haven't considered weekends or holidays, since you didn't talk about how they should be handled. Post again if you want some hints about the Sheets fromulae which would help with those calculations.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.