SOLVED

Help with IF function

Copper Contributor

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:

Fastgrass_0-1634694791374.png

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!

 

Gillie

 

 

 

 

8 Replies

@Fastgrass 

Try:

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

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

@Fastgrass 

Sorry. I forgot to translate that part.

 

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

 

 

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

@Fastgrass 

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
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Fastgrass 

Sorry. I forgot to translate that part.

 

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

 

 

View solution in original post