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.

enter image description here

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

  • 1
    In February, this year is 2022 and is not a leap year. This throws off your calculation for that part. Here's a temporary (maybe good enough for to be permanent) solution for the March thing: 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)
    – David Tan
    Aug 26, 2022 at 8:58
  • Hi, welcome to WebApps. I have been looking at this question and have a couple of suggestions thatassist in the long run. 1)Cell A1, H1,O1. The content should a date: 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.
    – Tedinoz
    Aug 31, 2022 at 0:37
  • I had the formulas for "Start", "End", "Work" and "Cycle" figured out until I struck March where you have two events in the same month. I'll try the suggestion by @DavidTan and see where/how that helps.
    – Tedinoz
    Aug 31, 2022 at 0:40
  • "In February, the Cycle Length is 23 days, but it should be 24 days." I disagree: Feb Cycle = 23. Feb8-Feb28 (inclusive) = 21 days plus March1-March2 (inclusive) = 2 days. Total = 23 days.
    – Tedinoz
    Sep 1, 2022 at 1:52
  • 1) You say "In March, the End Day is incorrect." However, you don't say what value you think it should be. Would you please advise what date you think March End date should be. 2) The March Cycle Length field is blank, would you please advise what value you believe the March Cycle length should be. 3) Calculated values for April are blank but April1 is the last day of an engagement begun in March. Would you please advise the various values that apply for April.
    – Tedinoz
    Sep 1, 2022 at 2:08


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy