One way
Sub next22nd()
x = Day(Date)
'x = 22
MsgBox x
If x < 22 Then
y = DateSerial(Year(Date), Month(Date), 22)
Else
y = DateSerial(Year(Date), Month(Date) + 1, 22)
End If
MsgBox y
End Sub

--
Don Guillett
SalesAid Software

"Justin Smith" wrote in message
...
John Green posted this elegant formula to find the next Friday based on
today's date:

=TODAY()-WEEKDAY(TODAY()+1)+7

I want to do something similar, but in VBA. I want to write a custom
function that will return the next 22nd of the month based on today's
date,
e.g. if today is 3rd December, the function returns 22 December 2006. If
today is 26th December, then 22 January 2007 is returned. Can anyone help?

Perfect Don, thanks :-)

"Don Guillett" wrote:

One way
Sub next22nd()
x = Day(Date)
'x = 22
MsgBox x
If x < 22 Then
y = DateSerial(Year(Date), Month(Date), 22)
Else
y = DateSerial(Year(Date), Month(Date) + 1, 22)
End If
MsgBox y
End Sub

--
Don Guillett
SalesAid Software

"Justin Smith" wrote in message
...
John Green posted this elegant formula to find the next Friday based on
today's date:

=TODAY()-WEEKDAY(TODAY()+1)+7

I want to do something similar, but in VBA. I want to write a custom
function that will return the next 22nd of the month based on today's
date,
e.g. if today is 3rd December, the function returns 22 December 2006. If
today is 26th December, then 22 January 2007 is returned. Can anyone help?

Thanks, You did say VBA but Martins formula is good.

--
Don Guillett
SalesAid Software

"Justin Smith" wrote in message
...
Perfect Don, thanks :-)

"Don Guillett" wrote:

One way
Sub next22nd()
x = Day(Date)
'x = 22
MsgBox x
If x < 22 Then
y = DateSerial(Year(Date), Month(Date), 22)
Else
y = DateSerial(Year(Date), Month(Date) + 1, 22)
End If
MsgBox y
End Sub

--
Don Guillett
SalesAid Software

"Justin Smith" wrote in message
...
John Green posted this elegant formula to find the next Friday based on
today's date:

=TODAY()-WEEKDAY(TODAY()+1)+7

I want to do something similar, but in VBA. I want to write a custom
function that will return the next 22nd of the month based on today's
date,
e.g. if today is 3rd December, the function returns 22 December 2006.
If
today is 26th December, then 22 January 2007 is returned. Can anyone
help?