stuck in a little project ---> give unique id to papers

describing the situation:

Untitled.jpg

Which column contains the dates, and which column will you use for these numbers? Does your data start in row 2 ?

Pete

The dates are in the first column - A
the numbers are (you can look at the photo that i posted):
1. one is the number of month (i do it already) - i
2. second is the number of paper (that i want automatically) - j
3. thirs is a combine of id (number of month + number of paper. for example, 1-2 (second paper in jan). - k

thank you!

Put this in E2:

=IF(A2="","",COUNTIF(E$1:E1,"*-"&MONTH(A2))+1&"-"&MONTH(A2))

then copy down.

Hope this helps.

Pete

hey, works great!!!!

another little problem -> if a paper is at the first day of the month (01/05/2013) or (01/12/2013) -> he need to be at the previous month, so at his id he need to get the next number in the previous month.

for example: if the last paper in march is at the number 132-3, and i put a paper with the date of 01/03/2013, he need to get 133-3. thanks!!!

ok arlu1201, i do it.

i write the problem that not solved yet:

another little problem -> if a paper is at the first day of the month (01/05/2013) or (01/12/2013) -> he need to be at the previous month, so at his id he need to get the next number in the previous month.

for example: if the last paper in march is at the number 132-3, and i put a paper with the date of 01/03/2013, he need to get 133-3. thanks!!!

I see that you have added the links to your first post. You can amend the formula like this:

=IF(A2="","",COUNTIF(E$1:E1,"*-"&MONTH(A2-1))+1&"-"&MONTH(A2-1))

Hope this helps.

Pete

hey,

works great.

can you explain me what the formula do?

thanks.

another little question for finishing:

every paper have a year. can i separate the id with the year? write id like this: 820-3-13, 820-3-14, 820-3-12... etc...
becuse the id goes up with the month of separate year.

You should describe all the requirements at the beginning, rather than drip-feeding them. Try this version to include the year:

=IF(A2="","",COUNTIF(E$1:E1,"*-"&TEXT(A2-1,"mm-yy"))+1&"-"&TEXT(A2-1,"mm-yy"))

It works by counting the cells above where the formula is which have any number plus "-mm-yy", and then adding one onto the number followed by "-mm-yy"

Hope this helps.

Pete

HEY PETE.

you are awesome. sorry for bothering you. after i gave this for the project, i find that i need one small change.

every date need id with the moth after.
for example - 02/12/2011 need id for (jan 12).
for example - 15/06/2013 need id for (july 13).

01/06/2011 - is still id for the 5 month. he is with the group for example: 27/04/2011 - that goes to may (5).

get me? thank you a lot! you help me very much!!!

This contradicts what you said earlier, where a date of the 1st of a month should be taken as the previous month.

Instead of A2-1 (twice) in the formula, which means take the previous day, you should change it to A2+30 (as an approximate adjustment to move to the next month, but 28th Feb would move it into April - how long is a month??).

I hope this is going to be worth a "star" !!

Pete

hey,
i have some bugs.

31/01/2012 - goes to 1-03-12 - (id) - instead of 1-02-2012
01/06/2013 - goes to 2-07-13 - (id) - instead of (next number)-05-2013...

So you are saying that the first of June should still be counted as May, and other days in June should be counted as July? Doesn't make sense.

Pete

yes. like checks to the bank.

25/03/2013 are for april
31/03/2013 are for april
01/04/2013 are for april
13/04/2013 are for may

01/06/2013 are for june.
like 15/05/2013 are for june.
like 15/06/2013 are for july
like 01/07/2013 are for july.
get me?

No, I don't get you, but if you are saying that you want a cut-off date each month so that anything after 14th of a month should be treated as the next month then use A2+14 instead of A2+30.

Pete

hey pete,

look at the picture: Untitled.jpg

look at the date 31/1/2012 -> the id need to be with the month feb - (1-02-12). in the picture he give the paper id with march 1-03-13.

in the file, it happens only with this date... and this stuck all the papers in feb that get id for (3).

thanks!

Did you see my last post? Add on 14 instead of 30 (it occurs twice in the formula).

Pete

yes i saw it, but thats not good. 07/12/2011 get 1-12-11 instead of 1-01-12. and so on.
03/02/2011 get 1-2-11 instead of 1-3-11.

maybe we need new formula? with some if to the day 01 in every month? or something else with the exist formula?

thanks

Start a new file, put 1st Jan 2013 in A1, then in A2 put this formula:

=A1+1

and copy this down to A366, to give you a list of dates.

Then against each date put the month number that you want that date to be considered in.

Attach the Excel file (NOT a picture of it), and then I might be able to see the logic behind what you want to achieve.

Pete

Okay, try this formula in E2:

=IF(A2="","",COUNTIF(E$1:E1,"*-"&TEXT(MOD(MONTH(A2)+(DAY(A2)<>1)-1,12)+1,"00")&TEXT(A2,"-yy"))+1&"-"&TEXT(MOD(MONTH(A2)+(DAY(A2)<>1)-1,12)+1,"00")&TEXT(A2,"-yy"))

Hope this helps.

Pete