Increment value in a cell but resets value after a max value

[jameswhite4684]

I have date that always increments everymonth by 1 and max number 12, as there are 12 months in a year. Sometimes this number will be max of 6. So, I am trying to figure a formula out that would, on the first of each month increase that number by 1, and on the 13th month the number will go back to 1.

If possible, I would need it to increase another cell by 1 letter. Say value A > B after 12.

A1 Contains Letter Values, B1 Contains Number Values

Example:

March - Cell A1 = N, B1 = 11 Then April comes
April - Cell A1 = N, B1 = 12 Then May comes
May - Cell A1 = O, B1 = 1

I hope this example helps. Some times I need the max number to rotate is 3 months or 6 months.

 

[danzon]

does this give you what you are after

Cell A1 = N
Cell b1 = 11

in A2 use

=IF(IF(B2<=12,CHAR(CODE(A1)+1),A1)="[","A",IF(B2<=12,CHAR(CODE(A1)+1),A1))

in B2 use

=IF(B1<12,B1+1,1)

fill both down

 

[danzon]

oops.. jus re-read your request.

in A2 use this instead

=IF(IF(AND(B2<=12,B2-B1=1),A1,CHAR(CODE(A1)+1))="[","A",IF(AND(B2<=12,B2-B1=1),A1,CHAR(CODE(A1)+1)))

B2 is still same as indicated above

 

[jameswhite4684]

danzon, Thank you for your help, after rereading my post, I didnt explain very well, sorry.

Anyhow, I will revise it.

A1 = Letter, B1= Number (1 - 12), C1= Date
----------------------------------------------------------------
B, 11, 1-Feb-15 is in the current cells. For next month, I would update C1 date to 1-Mar-15 manually as I have to do this abouth the 20th of each month. So when I enter new month in C1, Cell value in A1 will remain the same (B), but Cell value in B1 = 12. Then when 1-Apr-15 comes around and I enter that date manually, A1 cell value becomes "C", B1 value becomes "1".

I would not be doing a fill down. as it may require a slightly different formula for each item. Instead of up to 12 values only up to 6 values and the the A1 letter would increment.

 

[jameswhite4684]

VBA coding will be okay also, and if I could have the date to update on the 20th of every month to the 1st of the next month, that would be fantastic,

 

[danzon]

my formulas are producing exactly that result...

if you don't want it to always be increments of 12 then replace the places in the formula where it references 12... maybe use a cell reference in the same row instead where you can manually enter 3 or 6 etc

so that this formula
=IF(B1<12,B1+1,1)

might be replaced with this formula instead =IF(B1 < F1,B1+1,1)

where in F1 you would manually type 6

same would have to be done to the other formula

[/IMG]

 

[jameswhite4684]

Yes, I see it works, but I need it to be maintain in the same cell, not filled down. I have attached a image, look at it as if the values are always located in cells A1, B1, and C1.

Thanks again.

 

[jameswhite4684]

As you see, Changing the date to the 1st of next month would increment the segment, and once the segment reaches after 12 the edition increments. The date plays a important role in the formula. So it seems your first two formulas will work if I can get the date to work in that also.