Date range formula (last week)

[nielf]

I'm trying to come up with a TRUE/FALSE formula to check if a date is within last week. I thought this would be quite simple but that's not the case (at least for me).

I have come up with a formula that checks if today's date is within the current week and year - and that works perfectly fine using WEEKNUM and YEAR. I thought I might be able to use WEEKNUM -1 or something similar but that's doesn't seem possible when you are at the start or end of the year.

My week starts on a Monday and the year must also be taking into consideration.

I hope you can help.

 

[jorismoerings]

Hi,

try this:

Book1
	A	B
14	5-6-2016	FALSE
15	6-6-2016	TRUE
16	7-6-2016	TRUE
17	8-6-2016	TRUE
18	9-6-2016	TRUE
19	10-6-2016	TRUE
20	11-6-2016	TRUE
21	12-6-2016	TRUE
22	13-6-2016	FALSE
23	14-6-2016	FALSE
24	15-6-2016	FALSE
25	16-6-2016	FALSE
26	17-6-2016	FALSE
Sheet1

Cell Formulas
Range	Formula
B14	=AND(YEAR(TODAY())=YEAR(A14),WEEKNUM(TODAY(),2)-WEEKNUM(A14,2)=1)

 

[nielf]

Hi Joris,

Thank you very much. Your solution works like a charm. Will the formula also work when the week number changes from week 52 in 2016 to week 1 in 2017?

 

[jorismoerings]

Hi,

No, the formula will evaluate into FALSE if the year-number of week 1 (eg 2017) will differ from the year-number of week 52 (eg 2016);
If you want the formula to go over from year to year change it to this:

Book1
	A	B
1	4-6-2016	FALSE
2	5-6-2016	FALSE
3	6-6-2016	TRUE
4	7-6-2016	TRUE
5	8-6-2016	TRUE
6	9-6-2016	TRUE
7	10-6-2016	TRUE
8	11-6-2016	TRUE
9	12-6-2016	TRUE
10	13-6-2016	FALSE
Sheet1

Cell Formulas
Range	Formula
B1	=OR(WEEKNUM(TODAY(),2)-WEEKNUM(A1,2)=1,ABS(WEEKNUM(TODAY(),2)-WEEKNUM(A1,2))=52,ABS(WEEKNUM(TODAY(),2)-WEEKNUM(A1,2))=51)

 

[nielf]

Thanks a lot Joris. It is really appreciated.

I can see that this formula will work for the next couple of years. But what happens in year 2020 which has 53 weeks?

 

[jorismoerings]

Hi,

Will still work cause there are 3 checks in the formula:
This part will result in TRUEif it's a regular week;

WEEKNUM(TODAY(),2)-WEEKNUM(A1,2)=1

This part will result in TRUE if the previous year had 53 weeks;

ABS(WEEKNUM(TODAY(),2)-WEEKNUM(A1,2))=52

and this part will result in TRUE if the previous year had 52 weeks;

ABS(WEEKNUM(TODAY(),2)-WEEKNUM(A1,2))=51

as it's an OR construction the formula will also work with a year with 53 weeks.

 

[nielf]

Fantastic.

Thank you very much for your input and explaining the formula.

 

[jorismoerings]

no problem, glad i could help.

 

[nielf]

Would it be possible to include YEAR in the formula they way you did in the first formula you came up with?

The formula is showing TRUE for dates within the same period for other years as well.

 

[jorismoerings]

Book1
	A	B
1		FALSE
889	6-6-2016	TRUE
890	7-6-2016	TRUE
891	8-6-2016	TRUE
892	9-6-2016	TRUE
893	10-6-2016	TRUE
894	11-6-2016	TRUE
895	12-6-2016	TRUE
Sheet1

Cell Formulas
Range	Formula
B1	=OR(AND(YEAR(TODAY())=YEAR(A1),WEEKNUM(TODAY(),2)-WEEKNUM(A1,2)=1),AND(YEAR(TODAY())-YEAR(A1)=1, OR(ABS(WEEKNUM(TODAY(),2)-WEEKNUM(A1,2))=52,ABS(WEEKNUM(TODAY(),2)-WEEKNUM(A1,2))=51)))