Restart Numbering - 08-26 to 09-01 format

[Smokeyham]

Hello,

I have a spreadsheet where I keep track of information for a bi-weekly payroll system. Most years there are 26 pay periods in the year.

I want to have the pay period number reset the suffix to "01" when it reaches 26. I also would like it to switch the prefix to the next year. Here is an example of what I am try to do:

08-25
08-26
09-01
09-02

 

[Jonmo1]

If A1 = 08-25

in B1 and filled down

=TEXT(LEFT(A1,2)+(RIGHT(A1,2)="26"),"00")&"-"&IF(RIGHT(A1,2)="26","01",TEXT(RIGHT(A1,2)+1,"00"))

 

[Smokeyham]

If A1 = 08-25

in B1 and filled down

=TEXT(LEFT(A1,2)+(RIGHT(A1,2)="26"),"00")&"-"&IF(RIGHT(A1,2)="26","01",TEXT(RIGHT(A1,2)+1,"00"))

Thanks so much!

I might be missing something here, but what I did to make this work when copying downwards in Column B was to change the reference in cell B2 to reference cell B1 (as opposed to A2) and then copied downwards. It worked great then.