Formula to create a sequence of numbers every month

[peterderrington]

Hi,
Sure this is really simple, I have a Spreadsheet and i would like one of the columns to take the value of a cell showing a date and generate a slowly increasing number which would reset every time the month changed.
So there might be 5 rows with dates in covering January and then 6 with February dates in etc etc and it would display a number sequence of 1 to 5 and then 1 to 6 and so on.
How easy is that to achieve?
Thanks,
Peter

[Glenn Kennedy]

I am not quite able t visualise this... but it will be easy. Show us what you want...

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet.

[peterderrington]

My computer seems completely against the idea of attaching either a spreadsheet or an image of it but essentially i have the righthand column and want excel to create the numbering in the left hand one.

No. Generated Referral Date
1 02/01/2020
2 02/01/2020
3 15/01/2020
4 22/01/2020
5 26/01/2020
1 01/02/2020
2 10/02/2020
3 11/02/2020
4 23/02/2020
5 24/02/2020
6 28/02/2020
1 03/03/2020
2 05/03/2020
3 16/03/2020
1 15/04/2020
2 16/04/2020
3 25/04/2020
4 26/04/2020
1 06/05/2020
2 07/05/2020

[Glenn Kennedy]

That is NOT what I had even sort-of visualised!!

=IF(B2="","",IFERROR(IF(MONTH(B2)<>MONTH(B1),1,A1+1),1))

[peterderrington]

That is fantastic - Thank you.

Might need a slight adjustment - there will end up being several years worth of data on here, what will happen when we come to the next year, will it still reset?

The data will all be in order so its not like there will be dates from one year mixed in with another.

[Glenn Kennedy]

Nope. But this will:

=IF(B2="","",IFERROR(IF(TEXT(B2,"mmyyyy")<>TEXT(B1,"mmyyyy"),1,A1+1),1))

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.