Forum Discussion

Gtur1976's avatar
Gtur1976
Copper Contributor
Feb 18, 2025
Solved

Excel Formula Help - Text & Numbers

I would like to have a formula that will automatically change this sequence but can't figure it out, would greatly appreciate the solution as I cant find it searching either.

 

The sequence is TP0285 / 02 / 25 the sequence to change is the 1st the number 285 to 286 and so on, the 02 would change as per the month, March is 03, April 04 and so on and the last digits would change annually.

Thanks in advance.

Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Feb 20, 2025
    ="TPO"&ROW(A285)&" /  "&TEXT(MONTH(A5),"00")&" /  "&RIGHT(YEAR(A5),2)

    You are welcome. Does this formula return the intended result?

  • PGSystemTester's avatar
    PGSystemTester
    Copper Contributor
    Feb 19, 2025

    You want each month to increment with the first three digits? The below formula would enable you to specify the starting date (month), the number of rows, etc. 

    =LET(irows,200,iStart,285,dateStart,DATE(2025,2,1),
zSeq,SEQUENCE(irows,1,0,1),"TP"&BASE(zSeq+iStart,10,4)&" / " &TEXT(DATE(YEAR(dateStart),MONTH(dateStart)+zSeq,1),"MM / YY"))

     

    • Gtur1976's avatar
      Gtur1976
      Copper Contributor
      Feb 20, 2025

      No the 2nd set is the month, the 1st set I want to change throughout the month when I issue a new number, the 2nd set would change when it rolls into March, 

       

      So it would be something like, 

       

      Feb:

      TP0285/02/25

      TP0286/02/25

      TP0287/02/25

      TP0288/02/25

      Mar:

      TP0289/03/25

      TP0290/03/25

      TP0291/03/25

      TP0292/03/25

      TP0293/03/25

      Apr:

      TP0294/04/25 and so on

       

      The times the 1st set of numbers change can vary depending on how many quotes I issue in a month. 

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Feb 18, 2025

    How about

    ="TPO"&ROW(A285)&" /  "&TEXT(IF(MOD(ROW(A2),12)=0,12,MOD(ROW(A2),12)),"00")&" /  "&QUOTIENT(ROW(A1),12)+25

     

    • Gtur1976's avatar
      Gtur1976
      Copper Contributor
      Feb 20, 2025

      Thanks that works, almost.

      Just checking if it could be changed to read the date from the 1st column please, tried changing it myself but couldn't figure it out, as you can see the month doesn't change when I put in an different date.

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Feb 20, 2025
        ="TPO"&ROW(A285)&" /  "&TEXT(MONTH(A5),"00")&" /  "&RIGHT(YEAR(A5),2)

        You are welcome. Does this formula return the intended result?

    • Gtur1976's avatar
      Gtur1976
      Copper Contributor
      Feb 20, 2025

      Thanks that’s nearly there, the 1set of numbers 0285 in this case is a quote number, I may issue numerous quotes in February so that could go to 0297, then when it rolls into March the 02 would change to 03 and continue from the previous set of number, 0298 and so on, the 1 set of numbers keeps going up throughout the month, the 2nd set only change with the month changes and the last set when the year changes. 

Resources