sequence number based on date

[Guest]

Hi folks,

I'm trying to set up an auto sequence number(col A) based on a date entry
(col B) as in the example below. The sequence number should reset to 0001
each time the date in Col B changes. Is this possible?

Col A Col B
072306-0001 07/23/06
072306-0002 07/23/06
072306-0003 07/23/06
072406-0001 07/24/06

Thanks,
Denise

 

[Guest]

Try something like this:

For a list of dates, beginning in B2

A2: =TEXT(B2,"mmddyy")&"-"&TEXT(COUNTIF($B$2:B2,B2),"0000")
Copy that formula down as far as you need.

Or....to avoid errors for blank cells.
A2: =IF(ISBLANK(B2),"",TEXT(B2,"mmddyy")&"-"&TEXT(COUNTIF($B$2:B2,B2),"0000"))


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Ron, this works great! Thanks much!

- Denise

Try something like this:

For a list of dates, beginning in B2

A2: =TEXT(B2,"mmddyy")&"-"&TEXT(COUNTIF($B$2:B2,B2),"0000")
Copy that formula down as far as you need.

Or....to avoid errors for blank cells.
A2: =IF(ISBLANK(B2),"",TEXT(B2,"mmddyy")&"-"&TEXT(COUNTIF($B$2:B2,B2),"0000"))


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP