The title is a bit confusing, but essentially I am creating a calendar/tracker where a user inputs values into the "Work" column and the spreadsheet should count those values and blank cells afterward in order to output values into the "Start Day, End Day, Work Length, and Cycle Length" columns. I'm looking for formulas that can calculate each value in those 4 columns. I'd prefer to get this accomplished without macros/scripts.
Start Day corresponds with the first non-blank cell in the column.
End Day corresponds with the last non-blank cell after the Start Day.
Work Length should count from the Start Day to the End Day.
Cycle Length should count from the first non-blank cell in the column all the way until the last blank cell before the next non-blank cell (Start Day) in the next Month.
I'm able to count for some scenarios, but in instances where there are 2 groups of values in the same Month, the my formulas don't work. Specifically:
- In February, the Cycle Length is 23 days, but it should be 24 days.
- In March, the End Day is incorrect.
- This throws off the rest of the information in future months.
Here's a link to the spreadsheet with current formulas: https://docs.google.com/spreadsheets/d/1WceKBNbyrb2rSCiIfVVpdt9l1fKIpPkqqxPataNHgoY/edit?usp=sharing
ARRAY_CONSTRAIN(QUERY({OFFSET(R3,0,-3,31,1),OFFSET(R3,0,-2,31,1)},"SELECT * WHERE Col2 IS NOT NULL ORDER BY Col1 DESC",0),1,1)1/1/2022,2/1/2022, formatted as "Long Month name". 2) Cell A3 should be=SEQUENCE(DAY(EOMONTH(A1,0)),1,A1,1); formatted as Day number. The formula will populate the column for as many days there are in the month. The formula can be copied to H3, O3 and so on. The important issue here is to use date fields wherever possible - it makes life easier in the long run.