[RESOLVED] Counting and finding years

I have a worksheet which contains 4 rows of data for each day of several months. Dates like 8/1/2022 are displayed in d-mmm-yy format like 1-Aug-22. I need to 1) determine how many months of data there are and 2) determine the address of the last row of the first month (or the address of the first row of the second month) so that I can cut the first month and paste it to a different sheet.

For 1) I know I can step through each row looking for a Day() value of 1 and then dividing by 4 (since there are 4 days 1s for each month) but I was hoping for something more elegant like a Worksheetfunction.countif with a wildcard search for the day 1s.

Quote:

---

Originally Posted by MartinLiss

I have a worksheet which contains 4 rows of data for each day of several months. Dates like 8/1/2022 are displayed in d-mmm-yy format like 1-Aug-22. I need to 1) determine how many months of data there are and 2) determine the address of the last row of the first month (or the address of the first row of the second month) so that I can cut the first month and paste it to a different sheet.

For 1) I know I can step through each row looking for a Day() value of 1 and then dividing by 4 (since there are 4 days 1s for each month) but I was hoping for something more elegant like a Worksheetfunction.countif with a wildcard search for the day 1s.

---

(1) why are you only sending the first month to another different sheet? - why are you not sending all of the months to other sheets?

you could simply run down the sheet outputting each line to the appropriate other sheet until there if nothing to report i.e. cell being referenced = ""

just thinking out loud

Quote:

---

Originally Posted by incidentals

(1) why are you only sending the first month to another different sheet?

---

It is a part of an archiving process. Over time, without this, the main worksheets could have a lot of months added to it and the archiving process limits the number of months on the main sheet by cutting and pasting one month to the archive sheet when a new month is added to the main sheet.

depending if you want a formula or vba
assuming all the dates are in a single column like column A
=INT((MAX(A:A)-MIN(A:A))/30)
of course just calculating for 30 day months only is a bit rough, if you can use vba then datediff would be more accurate

to get the row number of the last entry for the first month, by formula, no looping required
assumes the data (date) in column A starts on row 2, change to suit
=MATCH(DATE(YEAR(A2),MONTH(A2)+1,1)-1,A:A,1)
if you want to automate moving the rows then vba could be a better option

Quote:

---

Originally Posted by MartinLiss

It is a part of an archiving process. Over time, without this, the main worksheets could have a lot of months added to it and the archiving process limits the number of months on the main sheet by cutting and pasting one month to the archive sheet when a new month is added to the main sheet.

---

how many months does the main sheet hold - for sanity purposes?

you could trigger the archival process when you enter the month xmonths since first month mentioned...

The process would end up archiving the first month on you add the xth+1 and there after.

you only need compare actions in a date cell where -mmm- is first row date cell -mmm- and they are xmonths apart thus triggering the archival process.

that's a trigger of cells in range when table changes.

the last row is unimportant as the action happens as soon as you enter the first occurance of the xth month.

just thinking out loud

Thanks for your suggestions but I've decided to keep doing it inelegantly like this which both gives me the count of months and the ending address of the first month.

Code:

---

    For lngRow = 3 To lngLastRow
        If Month(.Cells(lngRow, "A")) <> intOldMonth And .Cells(lngRow, "A").Text <> "" Then
            intMonthCount = intMonthCount + 1
            If intMonthCount = 2 Then
                strEndAddrMo1 = .Cells(lngRow, "A").Offset(-1, 0).Address
            End If
            intOldMonth = Month(.Cells(lngRow, "A"))
        End If
    Next

---