CODEWITHSUNDEEP
excelexcel-formula
php123
php123

Reputation: 155

Increment column based on date - Excel

I am trying to create an excel formula that will increment as the year changes. The following is what i am trying to do:
http://i58.tinypic.com/nbc1fc.jpg

I have 2 columns - date and id. What i would like to do is increment the ID column by +1 when the year changes in the date column. So, dates from the same year will get the same id. (i am not concerned by the month/day values, all i need to do is assign the same id to dates from the same year).

I have tried a variety of formulas in excel but my excel skills are not great and i have been unable to find a solution. The best i could come up with is: IF(YEAR(A1=1928),420) but that would be quite monotonous to do for every year in the excel file.

i did try to do a bit of research and the only similar question that i could find on here was this --> Increment number when Date changes

Any help would be much appreciated

Upvotes: 1

Views: 1441

Answers (1)

pnuts
pnuts

Reputation: 59485

Please try, in say B2 assuming your data starts in A1: 

=IF(YEAR(A2)=YEAR(A1),B1,B1+1)

and you have chose a value for B1. Copy down to suit.

Upvotes: 1

Related Questions