Hi Ron
Thank you for your reply.
To answer your questions I should point out I'm very much
the novice at code/macro writing.

What do you mean by a "macro"?
To me it's the recorded mouse and/or keys actioned to
complete a task.
I sometimes modify this recorded macro to reduce the
number of lines of code & speed things up, into what Tom
Ogilvy once discribe to me as "normal code".

Do you want it to automatically change the entry when
the user enters a date?
No. I will attach the code/macro to a screen button to
change an existing date in B2 to always be the 5th of a
month.
The date steps down 1 month on each click on the screen
button.
The date in B2 becomes the start date in 20 Vlookup
formulas to view data over differing date periods.
Other formulas indicate totals and percentages of overall
totals.

If you want to use an event macro to change it
automatically, post back.
No. it's a manual operation with the use of screen button
to change the date period of data viewed.

From Sebastienm's previous post I believe a User Defined
Function is what I require.

Regards Bob C.

-----Original Message-----
On Fri, 17 Sep 2004 22:03:42 -0700, "Robert Christie"
wrote:

Using Windows XP Home SP2 and Excel 2003 SP1

I require a macro to check that todays date falls on or
between two dates, one in current month and second in
next month and then enter the 5th day of first month in
a
cell i.e.

If Today() is 05-09-04 enter 5-09-04 in cell B2
If Today() is 04-10-04 enter 5-09-04 in cell B2
or another way to explain would be;
If todays date falls on or between the 5th of current
month and the 4th of next month enter the 5th of first
month.

I hope I have explained my question clearly enough for
all to understand.
I'll be attaching macro to a screen button to return
cell
B2 back to normal.

TIA
BobC.

As a worksheet function it's pretty simple:

=A1-DAY(A1-4)+1

What do you mean by a "macro"?

Do you want it to automatically change the entry when
the user enters a date?

If you mean a User Defined Function, you can use the
same formula:

==============
Function Last5th(dt) As Date
Last5th = dt + 1 - Day(dt - 4)
End Function
==============

If you want to use an event macro to change it
automatically, post back.
--ron
.