Enter a specific date thats falls between two other dates.

[Robert Christie]

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.

 

[Guest]

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]

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.

 

[Guest]

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]

Thank you Seb, very much appreciated.

Regards
Bob C.

 

[Robert Christie]

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.

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Guest]

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]

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.

 

[Robert Christie]

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.

 

[Ron Rosenfeld]

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

 

[Robert Christie]

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

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
.

 

[Robert Christie]

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

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 Rosenfeld]

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

 

[Guest]

Ok, i think this time, with 2 functions, you can do probably everything.
-GetStartPeriodDate(d As Date, Optional StartDay As Long = 1):
Returns the start period date for a given date , for a given starting day
(1 by default, but 5 in your example)
-GetShiftMonthDate(d As Date, Optional MonthShift As Long = -1)
Returns the date for a given date shifted by so many month (default=-1, ie
last month)

Sub test()
Dim TheDate As Date
TheDate = Date 'eg: today

MsgBox "Date: " & TheDate & vbNewLine & _
"Start Period: " & GetStartPeriodDate(TheDate, 5) & vbNewLine & _
"Last Month: " & GetShiftMonthDate(TheDate, -1) & vbNewLine & _
"Last Month Start Period: " &
GetStartPeriodDate(GetShiftMonthDate(TheDate, -1), 5)
End Sub

Function GetStartPeriodDate(d As Date, Optional StartDay As Long = 1) As Date
GetStartPeriodDate = DateSerial(Year(d), Month(d) - 1 * (-(Day(d) <
StartDay)), StartDay)
End Function

Function GetShiftMonthDate(d As Date, Optional MonthShift As Long = -1) As
Date
GetShiftMonthDate = DateSerial(Year(d), Month(d) + MonthShift, Day(d))
End Function

Regards,
Sebastien

 

[Robert Christie]

Thank you once again Sebastienm
for your time and patience, very much appreciated.

Regards Bob C.

 

[Robert Christie]

Hi Ron

Reading your last post I can see we were at cross
purposes, as to what I wanted to achieve.
I apologise for not stating my requirements clearly in my
first post.
Firstly I should state I will be the only user of the
worksheet.
The worksheets purpose is to alter the data range that 20
Vlookup Formulas reference, this in turn allows formulas
to produce total values and percentages.
I already had two buttons to reduce cell B2 's date value
by 1 and 7 days using a helper column containing -1 and -
7.

My original thought was; with a single click on a screen
button return cell B2 to the 5th of current month with
the criteria being between or on the 5th of Current and
4th of next month.
Sebastienm's and your early posts achieved that first
thought.

But (theirs always a but) I then thought why not click a
button to reduce the date in cell B2 by one calendar
month retaining the 5th day of the month.

My overhaul requirement is the ability to change cell
B2's date to any value using screen buttons, view the
results and then return the cells value to the 5th of
current monthly period.
I didn't spell this requirement out clearly enough (a
point to be taken up by other users of any Newsgroup).
Therefore I didn't require the date to update
automatically upon user entry.

Both Sebastienm and yourself Ron have given me valuable
help and information and indeed given of your valuable
time.
May I thank you both again.

Thanks
Regards Bob C.

 

[Ron Rosenfeld]

My original thought was; with a single click on a screen
button return cell B2 to the 5th of current month with
the criteria being between or on the 5th of Current and
4th of next month.
Sebastienm's and your early posts achieved that first
thought.

But (theirs always a but) I then thought why not click a
button to reduce the date in cell B2 by one calendar
month retaining the 5th day of the month.

For a two macro solution (and you can attach a button to each macro):

=======================
Sub This5th()
[b2].Value = Date + 1 - Day(Date - 4)
End Sub

Sub Last5th()
Dim dt As Date
dt = [b2].Value
[b2].Value = dt - Day(dt - 5)
End Sub

=====================

--ron

 

[Guest]

You're welcome, Bob.
Glad i could help.
Sebastien