How do I make one column return a desired value based on the date in another column?

Question

I'm looking for a formula that will automatically return a value based on the date entered in another column.

Here is a photo copy of the spreadsheet with desired results.

Essentially, I want the number 0,1,2,3 to be returned automatically based on the data entered in column F, which are dates.

Ex.

• In Column 'F', '1/1/2021' is entered. Desired value output in column 'G' is '0'.

• No data in column 'F' is entered. Desired value is '1' in column 'G'.

• In Column 'F', a date after '01/01/2020', before '1/1/2021' is entered. Desired value in column 'G' is '3'

• In Column 'F', a date before '01/01/2019' is entered. Desired value in column 'G' is '2'

I'm having trouble fitting all of the conditions required into a formula and I've had embarrassingly low levels of success with this. To be frank, I'm not even sure anymore what kind of function should be used for what I'm trying to accomplish. Any help that could be offered would be greatly appreciated.

Answer 1

I'm no Google Sheets expert, but maybe something like this:

=IFS(A1 = DATEVALUE("1/1/2020"), 0, A1 = "", 1, AND(A1 > DATEVALUE("01/01/2020"), A1 < DATEVALUE("01/01/2021")), 3, A1 < DATEVALUE("01/01/2019"), 2)

Replace A1 with input field.

Answer 2

You can try something like this

=ARRAYFORMULA(
  (F2:F=DATE(2021,1,1))*1+
  (F2:F="")*2+
  (F2:F<DATE(2019,1,1))*(ISDATE_STRICT(F2:F))*3+
  (F2:F>=DATE(2020,1,1))*(F2:F<DATE(2021,1,1))*4+
  -1
)