adding filter by date to a macro

Hi,

I'm new to creating macro's in excel but not new to writing code. I've just been given a task by my boss to create some macro's in an excel spreadsheet we use as a booking database.

He has asked me to create a macro that will filter the bookings relative to the date it is today. The date filters required are:
-> Show only last weeks bookings
-> Show only the upcoming weeks bookings

I'm using excel 2007 and have found the built in "xlfilter" functions. The only problem is that Ecxel see's a week starting on Sunday. Being that we are an entertainment agency most of our bookings are "Thursday to Sunday" so I need excel to see a week as starting on Monday and finishing on the following Sunday.

In my research I just haven't been able to find a way to define the week. I can get the filter to work on everything else the way I want but not dates.

I'm sure I'm missing something totally obvious.

I've included the code to the macro I have for your convenience.

Please Login or Register  to view this content.

Yeah I think so. I'm using Excel 2007 and I've got the macro working showing the previous work week.

Only Excel's work week is Sunday to Saturday. So this means I get the gigs from the sunday before and then the gigs for the following Friday and Saturday.

I've set this up on the company website using asp, SQL and VBScript and found it relatively simple. I'm just totally confused about how I would do this in excel.

Seeing that Excel 2007 has the "XLFilterLastWeek" option I was just hoping to be able to get it to display Monday to Sunday instead of Sunday to Saturday.

Otherwise I will have to work out a way to get the macro to calculate the dates according to todays date or something and then place that into the filter criteria.

In asp I get the system to work out the day it is today then performs 2 date calculations on variables containting the Today() variable depending on the day in the week it is to create a start and finish variables holding the dates.

My apologies!

I've uploaded the workbook here:
click here

I couldn't seem to get it to upload using the attatchment option. I have PM'd you the password to the archive.

If you look at the macro in the workbook titled "Weekly_Report_Copy" you will see what I'm trying to do.

When you run the macro that it creates a new worksheet and pastes the dates into it. You will notice that it show's the dates booked from Sun 12/04/09 to Saturday 18/04/09.

All I need it to display is Monday 13/04/09 to Sunday 19/04/09. Pushing the week filter forward by 1 week day. Once that's worked out I'm sure I will be able to continue on. I think I'm just missing something simple to do with dates.

My aim is to get it so I don't have to manually apply a filter for the start and end dates before running the macro.

Just another note, I received this workbook as a hand me down from the previous IT staff member. I'm in the process of improving it.