My macro is embedded in an Excel workbook. On 'Sheet1' there are 2 input
fields, (1) the 'Start' date, and (2) the 'End' date (for a 1 week period).
Users will enter both dates.

There are several restrictions: (1) the 'Start' date must be a Sunday, (2)
the 'End' date must be a Saturday. and (3) these dates can only be for a 1
week period (7 days). There are edits already in place to identify issues
for these 3 items.

It will also be okay for users to enter a week in the future. However, they
cannot enter last week's dates.

For example, as of today, (5/2/10 and 5/8/10), (5/9/10 and 5/15), and
(5/16/10 and 5/22/10) are all acceptable ranges.

However, (4/25/10 and 5/1/10) is not an acceptable range because it starts
in a prior week. Any Sunday 'Start' date prior to 5/2/10 is not allowed and
I need an edit to trap this error.

The user can run the macro any day in the current week for the current week
and any week in the future. They just cannot run it for prior weeks.

I think I'm looking for code that will tell me what the date is for the
current week's Sunday and then I can compare that to the 'Start' date to see
if is prior to the date for the current week's Sunday (unless I'm making this
too difficult and there is an easier way to do this.)

Thanks for the help.

--
JT

As ALWAYS, post YOUR code for comments.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JT" wrote in message
...
My macro is embedded in an Excel workbook. On 'Sheet1' there are 2 input
fields, (1) the 'Start' date, and (2) the 'End' date (for a 1 week
period).
Users will enter both dates.

There are several restrictions: (1) the 'Start' date must be a Sunday, (2)
the 'End' date must be a Saturday. and (3) these dates can only be for a 1
week period (7 days). There are edits already in place to identify issues
for these 3 items.

It will also be okay for users to enter a week in the future. However,
they
cannot enter last week's dates.

For example, as of today, (5/2/10 and 5/8/10), (5/9/10 and 5/15), and
(5/16/10 and 5/22/10) are all acceptable ranges.

However, (4/25/10 and 5/1/10) is not an acceptable range because it starts
in a prior week. Any Sunday 'Start' date prior to 5/2/10 is not allowed
and
I need an edit to trap this error.

The user can run the macro any day in the current week for the current
week
and any week in the future. They just cannot run it for prior weeks.

I think I'm looking for code that will tell me what the date is for the
current week's Sunday and then I can compare that to the 'Start' date to
see
if is prior to the date for the current week's Sunday (unless I'm making
this
too difficult and there is an easier way to do this.)

Thanks for the help.

--
JT

hi
see if you can use this.......
MsgBox Date - Weekday(Date, 2)
MsgBox WeekdayName(Weekday(Date, 1) - Weekday(Date, 2))
If [A1].Value < Date - Weekday(Date, 2) Then
MsgBox "In the past!!! Abort! Abort!"
End If

also read up on the weekday function in vb help.
regards
FSt1
"JT" wrote:

My macro is embedded in an Excel workbook. On 'Sheet1' there are 2 input
fields, (1) the 'Start' date, and (2) the 'End' date (for a 1 week period).
Users will enter both dates.

There are several restrictions: (1) the 'Start' date must be a Sunday, (2)
the 'End' date must be a Saturday. and (3) these dates can only be for a 1
week period (7 days). There are edits already in place to identify issues
for these 3 items.

It will also be okay for users to enter a week in the future. However, they
cannot enter last week's dates.

For example, as of today, (5/2/10 and 5/8/10), (5/9/10 and 5/15), and
(5/16/10 and 5/22/10) are all acceptable ranges.

However, (4/25/10 and 5/1/10) is not an acceptable range because it starts
in a prior week. Any Sunday 'Start' date prior to 5/2/10 is not allowed and
I need an edit to trap this error.

The user can run the macro any day in the current week for the current week
and any week in the future. They just cannot run it for prior weeks.

I think I'm looking for code that will tell me what the date is for the
current week's Sunday and then I can compare that to the 'Start' date to see
if is prior to the date for the current week's Sunday (unless I'm making this
too difficult and there is an easier way to do this.)

Thanks for the help.

--
JT

On Tue, 4 May 2010 12:01:01 -0700, JT wrote:

My macro is embedded in an Excel workbook. On 'Sheet1' there are 2 input
fields, (1) the 'Start' date, and (2) the 'End' date (for a 1 week period).
Users will enter both dates.

There are several restrictions: (1) the 'Start' date must be a Sunday, (2)
the 'End' date must be a Saturday. and (3) these dates can only be for a 1
week period (7 days). There are edits already in place to identify issues
for these 3 items.

It will also be okay for users to enter a week in the future. However, they
cannot enter last week's dates.

For example, as of today, (5/2/10 and 5/8/10), (5/9/10 and 5/15), and
(5/16/10 and 5/22/10) are all acceptable ranges.

However, (4/25/10 and 5/1/10) is not an acceptable range because it starts
in a prior week. Any Sunday 'Start' date prior to 5/2/10 is not allowed and
I need an edit to trap this error.

The user can run the macro any day in the current week for the current week
and any week in the future. They just cannot run it for prior weeks.

I think I'm looking for code that will tell me what the date is for the
current week's Sunday and then I can compare that to the 'Start' date to see
if is prior to the date for the current week's Sunday (unless I'm making this
too difficult and there is an easier way to do this.)

Thanks for the help.

Why must the user enter BOTH dates?

If I needed to restrict the entry dates, without knowing what else you are
doing, I would use Data Validation for the Start date, and I would compute the
End Date.

For Data Validation, I would use, assuming your Start Date is in A2:

Data/Data Validation
Settings: Custom
Formula: =AND(WEEKDAY(A2)=1,A2(TODAY()-WEEKDAY(TODAY())))
You can then put appropriate messages and prompts in the other tabs.

Then for the end date, it would be simply:

=if(a2="","",a2+6)

--ron