I'm working on a schedule in google sheets that counts available cells to cleanly display availability. Each cell represents a 10-minute interval. I have a cell where a time can be input, and I have a function that gets that time, rounds it down, and returns the start position for the function. I'm really trying to get this function to fit in one cell so that I can have a one cell function for each person on the schedule. Is there a function where I could use a cell position as a starting point and count to the right, stopping when it reaches a full cell?
My current function looks like this:
=REGEXREPLACE(ADDRESS(1,MATCH(FLOOR($B$1,"010"),D$1:CX$1,0)+3,4),"[1]",TEXT(ROW(),"0"))
It returns a cell, which is exactly what I want. Now I just need to find a way to start counting from this cell value and stop when it reaches a full cell. Empty cells represent availability, while cells with a value in it represent being busy.
TLDR, I need a function that counts blank cells to the right and stops when it reaches a full cell.
This formula will count the number of blank cells starting in the column that matches the time entered in $B$1 (rounded down to 10-minutes) and moving to the right.
=XMATCH("*",OFFSET(D$1:CX$1,ROW()-1,XMATCH(FLOOR($B$1,"010"),D$1:CX$1)-1),2)-1