Changing Beginning Day of Week

[oanion]

Good Afternoon,

I'm trying to create a selection formula that will select records for the previous week. However, the week should start on a Friday and end on a Thursday. In addition, there is a small twist. The records dated the last 2 days of a month should be included in the next months record selection.

Does anyone have any ideas?

Thanks

 

[synapsevampire]

Keep in mind that work weeks are NOT related to months, they are not a subset of months, they are determined based on the week of the year.

So you may be after weeks of a month, but I'll wait to start coding that out until you clarify what you mean by all of this.

In particular, if a onth has 30 days, and the friday starts on the 29th, and it is now the 6th, would the previous week be the 29th thru the 5th?

Then if a firday was on the 28th, and it is now the 5th, are you saying that the previous week is only one day because the 29th and 30th are part of this week?

Very strange definition for a week...

-k

 

[Madawc]

Using Friday should be simple enough. Define the current week as

DatePart("ww", currentdate, 6)

Define target dates as

DatePart("ww", {your.field}, 6)

This will give numbers that you can compair. Note that you'll also need to allow for weeks at the start and end of years. Datepart("yyyy" ... for years.

As for the last two days, you could hard-code it, allowing for leap-years. Or use Date to get the first day of the next month - again, remember about year ends. Subtract one day or two days from the date to get the last two days.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[synapsevampire]

Madawc: The Friday part is certainly simple, but I don't understand how that helps with the last 2 days aspect.

If the last 2 days are part of the next month, this implies that they are not work weeks at all as work weeks do not relate to months.

Perhaps you can explain the questions I asked, as I must be missing something here.

-k

 

[Madawc]

I'm assuming months and weeks are separate. If one grouped on both 'sort-of month' and work-week, then some work-weeks would be split between months. Maybe that's what they want.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[synapsevampire]

What's a sort-of-month?

But even if you group by a month first, you'd get partial weeks, which is what threw the monkey wrench into my logic when I looked at the post, you either have work seeks, or you have the weeks of a month, the latter being simple and your logic should work OK with lots more tweaking...

But I agree, they probably haven't thought through the requirements.

-k