How to Apply a Formula to Every Nth Column in Google Sheets?

Question

I'm working on a Google Sheets file which tracks employee work hours for an entire year. The sheet has columns for employee names, start and end times, and a blank column for total work hours.

My goal is to calculate the total work hours for each set of start and end times, without having to copy the same formula 365 times. Using a formula like =ARRAYFORMULA(C3:C-B3:B) only works for one column, and copying the formula for each of the remaining 364 columns is time-consuming.

I've also attempted an alternative solution of =ARRAYFORMULA(IF((MOD(COLUMN(D3:3), 4) = 0), C3:Z-B3:Z, "")) which calculates the difference between the previous two cells in every fourth column, but if any cell in the start or finish columns has a value added to it, the formula will break (as expected) with the error "Array result was not expanded because it would overwrite data in some cell". I understand that I cannot skip non-blank cells when generating content from a single cell, but there must be other workarounds and methods to solve this problem.

Here is a link to a sample Google Sheets file where you can see the problem. There are two sheets - the first one is the problem, and the second one has one of my attempted solutions: https://docs.google.com/spreadsheets/d/1fk6l5JW8CSs2tPiiETX5XhzAUf0vBa_vrM3QXCFph1I/edit?usp=sharing. Feel free to view, copy, or edit the sheet.

Is there an efficient way to solve this problem? While I strongly prefer using formulas over scripts, any solution that can help me achieve my goal is welcome, including even a keyboard shortcut or a magic feature in Google Sheets that would allow me to quickly copy a formula across multiple columns.

Thank you in advance for your help.

Answer 1

Here's one possible solution added to your sheet here:

1. you are just going to fill your manual data(Name, Start_time, end_time) in Sheet 1

2. the single formula in Cell A2 of Sheet 2 will populate the entire manual data + calculated fields

currently the formula loops through 44 sets of fields (one set=start,end,total,blank_column). You can change the 44 number to your choice within the sequence(44) part of the formula.

=reduce({"Name";indirect(address(3,column(),4,,"Sheet 1 - The Problem")&":"&address(60,column(),4))},sequence(44),lambda(r,c,
{r,{"Start";indirect(address(3,index(sequence(1,44,2,4),,c),4,,"Sheet 1 - The Problem")&":"&address(60,index(sequence(1,44,2,4),,c),4))},
{"Finish";indirect(address(3,index(sequence(1,44,3,4),,c),4,,"Sheet 1 - The Problem")&":"&address(60,index(sequence(1,44,3,4),,c),4))},

{"Total";map(indirect(address(3,index(sequence(1,44,3,4),,c),4,,"Sheet 1 - The Problem")&":"&address(60,index(sequence(1,44,3,4),,c),4)),indirect(address(3,index(sequence(1,44,2,4),,c),4,,"Sheet 1 - The Problem")&":"&address(60,index(sequence(1,44,2,4),,c),4)),lambda(x,y,if(len(x),x-y,IFERROR(1/0))))},

{"";indirect(address(3,index(sequence(1,44,5,4),,c),4,,"Sheet 1 - The Problem")&":"&address(60,index(sequence(1,44,5,4),,c),4))}}))