1

I have a spreadsheet in Google Sheets in which the formulas in E2 and F2 need to be copied down to the last row that has data. Here are rows 1 and 2:

Name of Teacher Date Absent During        Start Time          End Time
Larry Smith     2016-04-07  Period 1      4-7-2016 08:40:00   4-7-2016 09:24:00

The formula in E2 is:

=C2+choose(value(regexextract(D2,"\d+")),time(8,40,0),time(9,24,0),time(10,13,0),time(10,57,0),time(11,41,0),time(13,14,0),time(13,58,0),time(14,42,0),time(15,31,0))

The formula in F2 is:

=C2+choose(value(regexextract(D2,"\d+")),time(9,24,0),time(10,8,0),time(10,57,0),time(11,41,0),time(12,25,0),time(13,58,0),time(14,42,0),time(15,31,0),time(16,15,0))

Note: Date of Absence is C2; During is D2.

Is there an arrayformula (other any other) that will see C2:C and D2, then fill the formulas in E2 and F2 down to the last row with a value in C and D?

Here is the link to the spreadsheet.

2
  • Looks like this is solved, based on a comment in the spreadsheet
    – user79865
    Apr 10, 2016 at 21:31
  • Yes, thank you. I'm sorry for my delay in making a note.
    – brian
    Apr 12, 2016 at 9:56

1 Answer 1

1

The way I usually tend to do my arrayformulas, and actually any formula that requires action based on a value in another cell, is i start them off with an ISTEXT statement, and then just a simple comma in place of the else portion:

={"Start Time";arrayformula(IF(ISTEXT(B2:B),text(C2:C+vlookup(D2:D,Administrative!$A$1:$C$10,2,false),"mm-dd-yyyy hh:mm:ss"),))}

Note: the reason I leave just a comma instead of "", is that "" will leave a blank string, which will prevent new entries from being added in those rows if your using google forms or any other integration to add in those entries

1
  • Thank you very much your alternative idea. I'm learning a great deal from people like you. Thanks!
    – brian
    Apr 12, 2016 at 9:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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