GOOGLE SHEET FORMULA spreadsheet

Question

is there a way, if I want to highlight the cell if the date is current week, past week and next week ?

Answer 1

see WEEKNUM():

---

try:

past

=(WEEKNUM(A1, 2)<WEEKNUM(TODAY(), 2))*(A1<>"")

present

=(WEEKNUM(A1, 2)=WEEKNUM(TODAY(), 2))*(A1<>"")

future

=(WEEKNUM(A1, 2)>WEEKNUM(TODAY(), 2))*(A1<>"")

---

to look only one week into past & future you can do:

past

=(WEEKNUM(A1, 2)<WEEKNUM(TODAY(), 2))*(WEEKNUM(A1, 2)=WEEKNUM(TODAY(), 2)-1)*(A1<>"")

future

=(WEEKNUM(A1, 2)>WEEKNUM(TODAY(), 2))*(WEEKNUM(A1, 2)=WEEKNUM(TODAY(), 2)+1)*(A1<>"")

Answer 2

Since you didn't include your Minimal, Reproducible Example, I created one to demonstrate.

Select the date range, in this case A2:A1000 and Go to Format > and then Conditional formatting. Under format rules choose: Custom formula is, And paste this formulas for each rule.

Rules	Formulas
Prev week	=AND(A2>WORKDAY(TODAY()-14, 7)-7,A2<WORKDAY(TODAY()-14, 7))
This week	=AND(A2>WORKDAY(TODAY()-14, 7),A2<=WORKDAY(TODAY()-14, 7)+6)
Next week	=AND(A2>WORKDAY(TODAY()-14, 7)+7,A2<WORKDAY(TODAY()-14, 7)+14)