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.

 

[asad]

You could try this also. if the data is in starting from A1 and going down all the way, use this

=IF((A1+7-(WEEKDAY(A1,2)))+7=TODAY()+(7-WEEKDAY(TODAY(),2)),"Last Week","Not Last Week")

in B1 and copy down. Change the "A1" in formula if your range is starting from some other cell.

 

[nielf]

Thanks a lot for your time and effort, Joris. It works like a charm.

 

[nielf]

You could try this also. if the data is in starting from A1 and going down all the way, use this

=IF((A1+7-(WEEKDAY(A1,2)))+7=TODAY()+(7-WEEKDAY(TODAY(),2)),"Last Week","Not Last Week")

in B1 and copy down. Change the "A1" in formula if your range is starting from some other cell.

Thanks for the input asad.

Both formulas does the job but the one you came up with is definitely shorter.

 

[asad]

Thanks for the feedback nielf.

 

[jorismoerings]

Nielf,

Be careful with the shorten formula. I thought of a pretty much the similar formula but dismissed it cause it failed to get the year over correctly. Highly dependable on how you want handle the "broken weeks" in ayear over eg 4 days in week 53 and 3 days in week 1

 

[asad]

It does for me

21/12/2015Not Last Week
22/12/2015Not Last Week
23/12/2015Not Last Week
24/12/2015Not Last Week
25/12/2015Not Last Week
26/12/2015Not Last Week
27/12/2015Not Last Week
28/12/2015Last Week
29/12/2015Last Week
30/12/2015Last Week
31/12/2015Last Week
1/01/2016Last Week
2/01/2016Last Week
3/01/2016Last Week
4/01/2016Not Last Week
5/01/2016Not Last Week
6/01/2016Not Last Week
7/01/2016Not Last Week
8/01/2016Not Last Week
9/01/2016Not Last Week
10/01/2016Not Last Week
11/01/2016Not Last Week

the above has been compared to 10/01/2016.

 

[jorismoerings]

Asad,

Select 02/01/2016 as date for today; the weeknum will be 1 hence last week will be week 53. Your formula takes the 7 days back selecting week 53 and week 1. If that what's Nielf is aiming for , fine but as mentioned it all depends on how to handle the broken week.

NB: mine selects 52 and 53 which is fully correct either but haven't thought of a way to fix that.

 

[nielf]

It does for me

21/12/2015Not Last Week
22/12/2015Not Last Week
23/12/2015Not Last Week
24/12/2015Not Last Week
25/12/2015Not Last Week
26/12/2015Not Last Week
27/12/2015Not Last Week
28/12/2015Last Week
29/12/2015Last Week
30/12/2015Last Week
31/12/2015Last Week
1/01/2016Last Week
2/01/2016Last Week
3/01/2016Last Week
4/01/2016Not Last Week
5/01/2016Not Last Week
6/01/2016Not Last Week
7/01/2016Not Last Week
8/01/2016Not Last Week
9/01/2016Not Last Week
10/01/2016Not Last Week
11/01/2016Not Last Week

the above has been compared to 10/01/2016.

asad, have you testet the formula for years that have 53 weeks, e.g. 2020?

 

[asad]

It worked for all the examples you asked for.
the thing is that my formula is not looking for week number. It actually finds out the last day for the last week and checks if the date in question has the last day of the week as same day or not.

 

[nielf]

Thanks for clarifying that, asad.