Hi once again Seb
and thank you for your reply.

After your first reply post, I thought I would add a
second screen button.
Clicking on this button would subtract a single month and
still leave the day as the 5th of the month i.e. 05-09-04
down to 05-08-04.
As an exercise thought I would marry your first post with
your second to achieve what I required. Dismal result I'm
afraid.

I appologise for not asking for what I required in the
first place, but if was a case of "if excel can do that,
perhaps it can also do this"
Not enough forward planning.

What I would like to achieve is;.

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 in B2 to step down 1 month on each click of the
screen button.
Reason Why;
The date in B2 becomes the start date in 20 Vlookup
formulas to view data over differing date periods.
Other formulas in worksheet show totals and percentages
of overall totals.

TIA
Regards Bob C.

-----Original Message-----
Hi Bob,
The Date function returns the current (parenthesis are
automatically dropped
because there are no parameters to this function).
Now, concerning the returned value of the GetDate
function... i had
understood last mont of the current date.
If you want to return last month of any date, use the
below GetLastMonthDate
function bellow:

Sub test()
Range("B2") = GetLastMonthDate(Date) 'put GetDate
in B2
' and Date, here in
this example, means
today
End Sub

Function GetLastMonthDate(d As Date) As Date
GetLastMonthDate = DateSerial(Year(d), Month(d) - 1,
Day(d))
End Function

Regards,
Seb

"Robert Christie" wrote:

Seb

I not sure if this code is correct, the brackets after
d=
Date() disappear after entering them. I'm assuming
DateSerial( Year(d) , Month(d)-1 , Day(d)) replaces
part
of your original Vba"GetDate" code.

This is the code I'm trying without success.

Sub test()
Range("B2") = GetDate() 'put GetDate in B2
End Sub
Function GetDate() As Date
Dim d As Date
d = Date
GetDate = DateSerial(Year(d), Month(d) - 1, Day(d))
End Function

Regards Bob C.


-----Original Message-----
Worksheet function:
Assuming the a date is in A1 and you want same date
one
month before.
=Date( Year(A1) , Month(A1)-1 , Day(A1) )

In Vba:
Assuming 'd'd is your date:
DateSerial( Year(d) , Month(d)-1 , Day(d) )

Regards,
Sebastien

"robert Christie" wrote:

Hi again Seb

Following on from your code below:
How do you code to subtract or add 1 month to a
date
i.e.
date in B2 = 05-9-04 subtract 1 month to show 05-08-
04
or
add 1 month to show 05-10-04

Regards

Bob C.

-----Original Message-----
Hi Bob,

As a worksheet function, it would be:
=Date(Year(Today()), Month(Today()) - 1 * (Day
(Today
()) <= 4), 5)
But Today() recomputes automatically.

You can make it static. Using vba, you could use
the
function:
Function GetDate() As Date
Dim d As Date
d = Date()
GetDate = DateSerial(Year(d), Month(d) - 1 *
(-
(Day
(d) <= 4)), 5)
End Function
Then in a macro:
Sub test()
Range("B2")=getdate() 'put GetDate in B2
End Sub

Regards,
Seb

"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.

.


.


.