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.

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.

Hi Seb
Thank you for your quick reply and sorry for my late
reply.

Thanks also for the worksheet function.

I tried both out and either would work in my situation.

Thanks again

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.

.

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

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
.

On Mon, 20 Sep 2004 19:38:06 -0700, "Robert Christie"
wrote:

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

Then you can use the simple UDF that I posted in the previous message (which
uses the same algorithm as my formula):

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



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.


A UDF is used as a formula. To attach to a button, you need a SUB procedure.

====================
Sub Last5th()
Dim dt As Date
Dim rg As Range

Set rg = [b2]

If IsDate(rg) Then rg = rg - Day(rg - 4) + 1

End Sub
==================

The date steps down 1 month on each click on the screen
button.

Well your original request won't do that.

Your original request said that if the date was on the 5th of the month, you
wanted to keep it on the 5th of the month. In order for it to step down 1
month, it would have to go to the previous 5th if it was the 5th. But then you
have the problem of what to do if the user enters the 5th -- stay the same or
drop back. If you want it to stay the same if the user entered the 5th, but
drop back each time the button is pressed, then you have to differentiate
between a user entered and a modified date in that cell.

To have the 5th step back to the previous month, the formula becomes:

rg = rg - Day(rg - 5)

If you need to determine if the date which is the 5th was user entered or
machine modified, that's more complex, so post back.


--ron

Sorry Ron,

Forgot to try out the last part of your post.
I got tried up with User Entered verses Machine Modified
Dates.

To have the 5th step back to the previous month, the
formula becomes:

rg = rg - Day(rg - 5)

That works just fine on my test sheet, changes date back
month by month.

Thank you for your time and effort, very much appreciated.

Regards Bob C.

-----Original Message-----
On Mon, 20 Sep 2004 19:38:06 -0700, "Robert Christie"
wrote:

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

Then you can use the simple UDF that I posted in the
previous message (which
uses the same algorithm as my formula):

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



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.


A UDF is used as a formula. To attach to a button, you
need a SUB procedure.

====================
Sub Last5th()
Dim dt As Date
Dim rg As Range

Set rg = [b2]

If IsDate(rg) Then rg = rg - Day(rg - 4) + 1

End Sub
==================

The date steps down 1 month on each click on the screen
button.

Well your original request won't do that.

Your original request said that if the date was on the
5th of the month, you
wanted to keep it on the 5th of the month. In order for
it to step down 1
month, it would have to go to the previous 5th if it was
the 5th. But then you
have the problem of what to do if the user enters the
5th -- stay the same or
drop back. If you want it to stay the same if the user
entered the 5th, but
drop back each time the button is pressed, then you have
to differentiate
between a user entered and a modified date in that cell.

To have the 5th step back to the previous month, the
formula becomes:

rg = rg - Day(rg - 5)

If you need to determine if the date which is the 5th
was user entered or
machine modified, that's more complex, so post back.


--ron
.

Ron

it seems my second to last post got lost.
basically I was asking what is the difference between
user entered dates and machine modified dates.
Also does Vba code re-act differently on each.

Tia
Bob C.

-----Original Message-----
On Mon, 20 Sep 2004 19:38:06 -0700, "Robert Christie"
wrote:

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

Then you can use the simple UDF that I posted in the
previous message (which
uses the same algorithm as my formula):

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



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.


A UDF is used as a formula. To attach to a button, you
need a SUB procedure.

====================
Sub Last5th()
Dim dt As Date
Dim rg As Range

Set rg = [b2]

If IsDate(rg) Then rg = rg - Day(rg - 4) + 1

End Sub
==================

The date steps down 1 month on each click on the screen
button.

Well your original request won't do that.

Your original request said that if the date was on the
5th of the month, you
wanted to keep it on the 5th of the month. In order for
it to step down 1
month, it would have to go to the previous 5th if it was
the 5th. But then you
have the problem of what to do if the user enters the
5th -- stay the same or
drop back. If you want it to stay the same if the user
entered the 5th, but
drop back each time the button is pressed, then you have
to differentiate
between a user entered and a modified date in that cell.

To have the 5th step back to the previous month, the
formula becomes:

rg = rg - Day(rg - 5)

If you need to determine if the date which is the 5th
was user entered or
machine modified, that's more complex, so post back.


--ron
.

On Tue, 21 Sep 2004 07:33:38 -0700, "Robert Christie"
wrote:

Ron

it seems my second to last post got lost.
basically I was asking what is the difference between
user entered dates and machine modified dates.
Also does Vba code re-act differently on each.

Tia
Bob C.


If the user enters, let us say, 6 Sep 2004 and then pushes the button, the date
will, of course, back up to 5 Sep. That is in accord with what you have posted
for the design.

But, if the user enters 5 Sep and pushes the button, the date will back up to 5
Aug. (Using the last code posted). I was not sure if this was behavior that
you wanted.

So the "user-entered" date is the date that the user types into the cell. The
"machine-modified" date is the result after executing the macro by pushing the
button.


--ron

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.

I misread. But just substitute TODAY() for A1 in my other formula:

=TODAY()-DAY(TODAY()-4)+1


--ron