Date = Next Friday

M

MathewPBennett

Good evening all,

I have some simple code, that when 't' is entered into a cell,
todays date (NOW) is automatically entered.
However I would like to have the date of the following Friday
entered instead of today (NOW).

I am unsure if this is possible, but any help on this matter
would as usual be most appreciated.

Here's my code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then
If Target = "t" Then
Target.Value = Format(Now, "mm-dd-yy")
End If
End If
End Sub


Thank you
Mathew
 
W

whisperer

Try this, I am sure that an expert can improve but it works


Code
-------------------

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then
If Target = "t" Then
Select Case Weekday(Now)
Case 1: Target.Value = Format(Now + 5, "mm-dd-yy")
Case 2: Target.Value = Format(Now + 4, "mm-dd-yy")
Case 3: Target.Value = Format(Now + 3, "mm-dd-yy")
Case 4: Target.Value = Format(Now + 2, "mm-dd-yy")
Case 5: Target.Value = Format(Now + 1, "mm-dd-yy")
Case 6: Target.Value = Format(Now + 7, "mm-dd-yy")
Case 7: Target.Value = Format(Now + 6, "mm-dd-yy")
End Select
End If
End If
End Sub
 
R

Ron Rosenfeld

Good evening all,

I have some simple code, that when 't' is entered into a cell,
todays date (NOW) is automatically entered.
However I would like to have the date of the following Friday
entered instead of today (NOW).

I am unsure if this is possible, but any help on this matter
would as usual be most appreciated.

Here's my code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then
If Target = "t" Then
Target.Value = Format(Now, "mm-dd-yy")
End If
End If
End Sub


Thank you
Mathew

Try this:

Target.Value = Format _
(Now + 7 - _
Application.WorksheetFunction.Weekday(Now + 1), "mm-dd-yy")


--ron
 
B

Bob Phillips

Good evening Matthew,

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cDays As Long

If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then
If Target = "t" Then
cDays = Weekday(Now)
If cDays = 6 Then
cDays = 0
Else
cDays = 6 + (cDays * (cDays <> 7))
End If
Target.Value = Format(Now + cDays, "mm-dd-yy")
End If
End If
End Sub

This assumes that a Friday stays as the same date. If you wnat the
foollowing Friday in this case, change

cDays = 0
to
cDays = 7


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
R

Ron Rosenfeld

Good evening all,

I have some simple code, that when 't' is entered into a cell,
todays date (NOW) is automatically entered.
However I would like to have the date of the following Friday
entered instead of today (NOW).

I am unsure if this is possible, but any help on this matter
would as usual be most appreciated.

Here's my code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then
If Target = "t" Then
Target.Value = Format(Now, "mm-dd-yy")
End If
End If
End Sub


Thank you
Mathew

Well, I forgot that WEEKDAY is also a native VBA function, so:

Target.Value = Format _
(Now + 7 - Weekday(Now + 1), "mm-dd-yy")

Should work just as well.


--ron
 
M

MathewPBennett

Hi again all,

With regard to my previous post I have found some code via google search,
and incorporated into my code, to produce:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dtVal As Date
dtVal = Choose(Weekday(Date), 5, 4, 3, 2, 1, 7, 6) + Date
If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then
If Target = "f" Then
Target.Value = dtVal
End If
End If
End Sub

where, if 'f' is entered, next fridays date is entered.
However, I cannot seem to resolve the fact that if todays day is actually Friday,
then I wish it to enter 'todays' date and not next friday.

Any help to finish this off would be appreciated.

Thank you
Mathew



Good evening all,

I have some simple code, that when 't' is entered into a cell,
todays date (NOW) is automatically entered.
However I would like to have the date of the following Friday
entered instead of today (NOW).

I am unsure if this is possible, but any help on this matter
would as usual be most appreciated.

Here's my code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then
If Target = "t" Then
Target.Value = Format(Now, "mm-dd-yy")
End If
End If
End Sub


Thank you
Mathew
 
M

MathewPBennett

Hi Once More,

Got it I think,

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dtVal As Date
dtVal = Choose(Weekday(Date), 5, 4, 3, 2, 1, 7, 6) + Date
If Weekday(Date) = 5 Then
If Target = "f" Then
Target.Value = Format(Now, "dd-mm-yy")
End If
Else
If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then
If Target = "f" Then
Target.Value = dtVal
End If
End If
End If

End Sub

Cheers
Mathew



Hi again all,

With regard to my previous post I have found some code via google search,
and incorporated into my code, to produce:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dtVal As Date
dtVal = Choose(Weekday(Date), 5, 4, 3, 2, 1, 7, 6) + Date
If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then
If Target = "f" Then
Target.Value = dtVal
End If
End If
End Sub

where, if 'f' is entered, next fridays date is entered.
However, I cannot seem to resolve the fact that if todays day is actually Friday,
then I wish it to enter 'todays' date and not next friday.

Any help to finish this off would be appreciated.

Thank you
Mathew



Good evening all,

I have some simple code, that when 't' is entered into a cell,
todays date (NOW) is automatically entered.
However I would like to have the date of the following Friday
entered instead of today (NOW).

I am unsure if this is possible, but any help on this matter
would as usual be most appreciated.

Here's my code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then
If Target = "t" Then
Target.Value = Format(Now, "mm-dd-yy")
End If
End If
End Sub


Thank you
Mathew
 
B

Bob Phillips

Mathew,

This looks some of my old code. I think you should change

dtVal = Choose(Weekday(Date), 5, 4, 3, 2, 1, 7, 6) + Date

to

dtVal = Choose(Weekday(Date), 5, 4, 3, 2, 1, 0, 6) + Date

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
M

MathewPBennett

Hi Whisperer
Thanks for this.
But please explain, why 5 to 0 ?
(I thought weekday 5 = Friday)
Mathew
Change the 7 to a zero
 
M

MathewPBennett

Hi Bob

Actually I cribbed it from a post by Tom Ogilvy in Oct 02.
I am unsure about these weekday parameters,
Please explain why I should change the 7,6 to 0,6.
Remember I am a novice at VBA, have had no formal tuition,
and just cobble my code together from the experts' posts - mainly through
trial & error.
Respectfully yours,
Mathew

Mathew,

This looks some of my old code. I think you should change

dtVal = Choose(Weekday(Date), 5, 4, 3, 2, 1, 7, 6) + Date

to

dtVal = Choose(Weekday(Date), 5, 4, 3, 2, 1, 0, 6) + Date

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
M

Mike Hughes

One way

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A:A"), Target) Is Nothing Then
If Target = "t" Then
Target.Value = Now() + 7
End If
End If
End Sub
 
B

Bob Phillips

Hi Mathew,

Because Friday is the 6th day of the weeks (Sunday is the first), and you
have an array of day offsets. The 6th is 7, so if today is a Friday it adds
7 getting the following Friday. If you want the same day, you need to add 0.
Hence 7,6 becomes 0,6.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top