Hi,

I'm struggling to get the function working to figure out the first day of
the following month. So if I have a cell with say 23 July I want my function
to result in 1st Aug.

I need this to be function not a formula cos I'm going to refer to it in my
code.

any help greatly appreciated


Thanks

Suzanne

see if this is what you want, may have to change sheet and cell ref

startmonth = Month(Sheets(1).Cells(1, 2))
endmonth = startmonth + 1
MsgBox "1st " & MonthName(endmonth)

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Grd" wrote:

Hi,

I'm struggling to get the function working to figure out the first day of
the following month. So if I have a cell with say 23 July I want my function
to result in 1st Aug.

I need this to be function not a formula cos I'm going to refer to it in my
code.

any help greatly appreciated


Thanks

Suzanne

demo't from the immediate window:

dt = DateValue("July 23, 2007")
dtNext = DateSerial(year(dt),Month(dt)+1,0)
? format(dtNext,"dddd")
Tuesday
? dtNext
07/31/2007

--
regards,
Tom Ogilvy


"Grd" wrote:

Hi,

I'm struggling to get the function working to figure out the first day of
the following month. So if I have a cell with say 23 July I want my function
to result in 1st Aug.

I need this to be function not a formula cos I'm going to refer to it in my
code.

any help greatly appreciated


Thanks

Suzanne

Sorry, had a typo in my dtNext formula and wasn't paying attention:

dt = DateValue("July 23, 2007")
dtNext = DateSerial(year(dt),Month(dt)+1,1)
? format(dtNext,"dddd")
Wednesday
? dtNext
08/01/2007
? format(dtNext,"1\st mmm")
1st Aug


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

demo't from the immediate window:

dt = DateValue("July 23, 2007")
dtNext = DateSerial(year(dt),Month(dt)+1,0)
? format(dtNext,"dddd")
Tuesday
? dtNext
07/31/2007

--
regards,
Tom Ogilvy


"Grd" wrote:

Hi,

I'm struggling to get the function working to figure out the first day of
the following month. So if I have a cell with say 23 July I want my function
to result in 1st Aug.

I need this to be function not a formula cos I'm going to refer to it in my
code.

any help greatly appreciated


Thanks

Suzanne

Thanks John

I'll have a look at this and Tom Ogilvy's but I think its what I want

S

"John Bundy" wrote:

see if this is what you want, may have to change sheet and cell ref

startmonth = Month(Sheets(1).Cells(1, 2))
endmonth = startmonth + 1
MsgBox "1st " & MonthName(endmonth)

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"Grd" wrote:

Hi,

I'm struggling to get the function working to figure out the first day of
the following month. So if I have a cell with say 23 July I want my function
to result in 1st Aug.

I need this to be function not a formula cos I'm going to refer to it in my
code.

any help greatly appreciated


Thanks

Suzanne

Hi Tom,

This works nicely for me. I couldn't get this working on my own so thanks
very much

S

"Tom Ogilvy" wrote:

Sorry, had a typo in my dtNext formula and wasn't paying attention:

dt = DateValue("July 23, 2007")
dtNext = DateSerial(year(dt),Month(dt)+1,1)
? format(dtNext,"dddd")
Wednesday
? dtNext
08/01/2007
? format(dtNext,"1\st mmm")
1st Aug


--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

demo't from the immediate window:

dt = DateValue("July 23, 2007")
dtNext = DateSerial(year(dt),Month(dt)+1,0)
? format(dtNext,"dddd")
Tuesday
? dtNext
07/31/2007

--
regards,
Tom Ogilvy


"Grd" wrote:

Hi,

I'm struggling to get the function working to figure out the first day of
the following month. So if I have a cell with say 23 July I want my function
to result in 1st Aug.

I need this to be function not a formula cos I'm going to refer to it in my
code.

any help greatly appreciated


Thanks

Suzanne