Oct 19 2021 07:06 PM
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
All help gratefully received!
Gillie
Oct 19 2021 07:19 PM
Try:
="F"&TEXT(TODAY(),"JJMM")&"-"&TEXT(COUNTIFS(A$1:A1,"F"&TEXT(TODAY(),"JJMM")&"*")+1,"0")
Oct 19 2021 07:22 PM
Oct 19 2021 07:25 PM - edited Oct 19 2021 07:30 PM
SolutionSorry. I forgot to translate that part.
="F"&TEXT(TODAY(),"YYMM")&"-"&TEXT(COUNTIFS(A$1:A1,"F"&TEXT(TODAY(),"YYMM")&"*")+1,"0")
Oct 19 2021 07:35 PM
Oct 19 2021 07:43 PM
Oct 19 2021 08:04 PM
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.
Oct 19 2021 07:25 PM - edited Oct 19 2021 07:30 PM
SolutionSorry. I forgot to translate that part.
="F"&TEXT(TODAY(),"YYMM")&"-"&TEXT(COUNTIFS(A$1:A1,"F"&TEXT(TODAY(),"YYMM")&"*")+1,"0")