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?