Adding a week to current year

[fireflyfranch]

How do I run a query for the last week of the previous year and the current year excluding the last week?

 

[Minty]

If you are basing this from the current date 2017-10-16 and always will be, then simply take 7 days off the current date as your end date, and then a year off of that.

Something like;

Between DateAdd("yyyy",-1, DateAdd("d",-7,Date() ) ) And DateAdd("d",-7,Date())

If yuo want to specify the date used, substitute them where the Date() is in the above

 

[Uncle Gizmo]

Depends how you define a week...

 

[fireflyfranch]

It needs to be the week before the week that has January 1st in it. Counting 7 days before that date will not always work.

 

[plog]

Perhaps you can post sample data. For this I suggest opening up a few calendars and showing what dates you expect to be shown based on the year.

So, starting with 2013, tell us what dates you expect to be included for each of the next 5 years.

 

[arnelgp]

iif(7-datepart("w", dateseria(year(date), 1,1)) =0, dateadd("d", -7,dateserial(year(date), 1,1)), dateseria(year(date), 1,1)-(7-datepart("w", dateseria(year(date), 1,1))))

 

[fireflyfranch]

2013 - 12/23/12 to 12/21/13
2014 - 12/22/13 to 12/27/14
2015 - 12/28/14 to 12/26/15
2016 - 12/27/15 to 12/24/16
2017 - 12/25/16 to 12/23/17

 

[plog]

Is this really what you want? I mean look at how many days your intervals are:

2013 - 363
2014 - 370
2015 - 363


2014 has a full week's worth of days more than 2013 & 2015. Further, 2015 only has 4 days in the prior year while 2014 has 10.

 

[plog]

From a logical standpoint its hard to determine the rules for when you start a time frame.

Can you provide a good definition of when to determine the start date of a year?

 

[Minty]

I'm glad It wasn't just me that was a little confused by the requirement. When does a week start - it needs to be definable?

 

[plog]

The main issue is that nobody seems to realize that a week isn't a true base unit to years. They just don't fit.

1 week = 7 days
1 year = 365 days (about 75% of the time)

365/7 = 52.1428

That remainder always kills any comparisons you try to do week by week in differing years. Eventually they add up and you have to assign them to some year.

 

[fireflyfranch]

Thank you I figured out a work around.

 

[arnelgp]

Would you share ur solution?