How to add auto increment number with 2018-00XX format (Google Form to Google Spreadsheet

Question

I do have a problem when I'm going to submit my form, i dont have any idea to add this format "2018-00XX" automatically when someone submitted a form and the data will be transferred to google spreadsheet.

As you can see the picture, I gave you an example of how the control number should be entered automatically when the user submitted a form from google form.

Thanks in advance to anyone can answer my problem!

Answer 1

A2:

=ARRAYFORMULA("201800"&text(ROW(A$1:INDEX(B:B,COUNTA(B:B)-1)),"00"))

Answer 2

You can use formula like this in the 1st Column

=if(EQ(B2,),"", A1+1)

This will check if column 2 is empty or not if, it isn't then it'll set value of cell just above it plus one.

NOTE : For 1st cell you'll need to set value 20180001 manually and apply this formula to rest of the whole column one cells.

Answer 3

With 2018 hardcoded in,

=text(row(1:1), "\2\0\1\80000")

Using the date from column B,

=year(B2)&text(COUNTIFS(B$2:B2, ">="&date(year(B2), 1, 1), B$2:B2, "<"&date(year(B2)+1, 1, 1)), "0000")