Hi,

When i place an date in the C colum the number in the B colum is added
by 1.
What i realy want is that the number start over again with 1 when i put
in the next month.

Like 1 to 20 inputs for the month januari en when februari comes Nr.
starts over again with 1.

Here is an screenshot of the situation:

http://www.ssgi.nl/excel.gif
I am dutch so "ALS" means "IF" in the formula:
=ALS(C4="";"";ALS(B3="Nr.";1;B3+1))

Thanks in advance.


--
shailend
------------------------------------------------------------------------
shailend's Profile: http://www.excelforum.com/member.php...o&userid=30916
View this thread: http://www.excelforum.com/showthread...hreadid=505924

So you want the number in B to change to the number of the month in C,
correct? If so, just put:
=MONTH(C2), and it will give you 1 through 12. If you want it to count
1,2,3, and start over at 1 when a new month occurs, then try:
=IF(MONTH(B2)<MONTH(B1),A1+1)

************
Hope it helps!
Anne Troy
www.OfficeArticles.com

"shailend" wrote in
message ...

Hi,

When i place an date in the C colum the number in the B colum is added
by 1.
What i realy want is that the number start over again with 1 when i put
in the next month.

Like 1 to 20 inputs for the month januari en when februari comes Nr.
starts over again with 1.

Here is an screenshot of the situation:

http://www.ssgi.nl/excel.gif
I am dutch so "ALS" means "IF" in the formula:
=ALS(C4="";"";ALS(B3="Nr.";1;B3+1))

Thanks in advance.


--
shailend
------------------------------------------------------------------------
shailend's Profile:
http://www.excelforum.com/member.php...o&userid=30916
View this thread: http://www.excelforum.com/showthread...hreadid=505924

TRy this modification

=ALS(C4="";"";ALS(OF(B3="Nr.";DAG(C4)=1);1;B3+1))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=505924

hi thanks for the reply,

=ALS(C4="";"";ALS(OF(B3="Nr.";DAG(C4)=1);1;B3+1))

this one works great!
there is only a little thing.

when i put these two values

01-02-06
01-03-06

below each other the number stays 1

when i do:
02-04-06
02-04-06

the number wil change from 2 to 3 (or so)

it looks like when the firstnumber of the date is 01 the number stays
1
in holland we use dd-mm-yyyy.

here is a screen dump of the situation:
http://www.wens.speedxs.nl/excel2.gif


--
shailend
------------------------------------------------------------------------
shailend's Profile: http://www.excelforum.com/member.php...o&userid=30916
View this thread: http://www.excelforum.com/showthread...hreadid=505924

I assumed that you would only show each date once, to accommodate what
you have you can incorporate Anne's suggestion...

=ALS(C4="";"";ALS(B3="Nr.";1;ALS(MAAND(C4)=MAAND(C 3);B3)+1))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=505924

hi,

it works fantastic, many many thanks :) :) :)


--
shailend
------------------------------------------------------------------------
shailend's Profile: http://www.excelforum.com/member.php...o&userid=30916
View this thread: http://www.excelforum.com/showthread...hreadid=505924