Help with IF function

Occasional Contributor



I'm hoping someone can help with this one. I'm using the following formula to generate a self propagating job number: =IF($B2="","","F" & YEAR(TODAY()) & TEXT(MONTH(TODAY()),"00") & "-" & TEXT(ROW()-1,"0"))


It comes back as F202110-01. F is for fastgrass, 2021 is the year, 10 is the month and -01 is the number that changes to the next number ie -02 when text is entered into the next B column).

What I would like to be able to do is shorten the year to 2 digits so it would read F21. I've tried subtracting 2000 in the function but I can't get it to work - any ideas?

Also - is there a way that when the month changes the numbers start back at -01 instead of just carrying on?

For instance: it's the 20 Oct 2021 and I'm entering the next 2 jobs that have been quoted and it looks like this:


What I would like the next job ref above to be is F2110-44


At the moment if the next job was not quoted until 01 Nov 2021 the Job Ref would be F202111-44

What I would like it to change to would be F2111-01


All help gratefully received!







8 Replies




Thanks for the quick response!. I tried it and it comes out as FJJ10-1?
best response confirmed by allyreckerman (Microsoft)


Sorry. I forgot to translate that part.





Thats brilliant - thanks!
Being cheeky now - I only want the job number to propagate when a client is entered into column B


You table has empty rows? That is a bad setup.

No empty rows or empty columns in tables!


@Detlef Lewin 


Sorry - I'm not the best at explaining...

I've attached a copy of the spreadsheet - when staff open the spreadsheet column A is locked out (so no one can accidently change the formula). The next job number does not appear until you enter a clients name.


In this test one I haven't protected column A