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