SOLVED

# Help with IF function

Occasional Contributor

# Help with IF function

Hi,

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

Gillie

8 Replies

# Re: Help with IF function

Try:

="F"&TEXT(TODAY(),"JJMM")&"-"&TEXT(COUNTIFS(A\$1:A1,"F"&TEXT(TODAY(),"JJMM")&"*")+1,"0")

# Re: Help with IF function

Hi,

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

# Re: Help with IF function

Sorry. I forgot to translate that part.

="F"&TEXT(TODAY(),"YYMM")&"-"&TEXT(COUNTIFS(A\$1:A1,"F"&TEXT(TODAY(),"YYMM")&"*")+1,"0")

# Re: Help with IF function

Thats brilliant - thanks!

# Re: Help with IF function

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

# Re: Help with IF function

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

No empty rows or empty columns in tables!

# Re: Help with IF function

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.

# Re: Help with IF function

In this test one I haven't protected column A