Need to create function to find first day of following month

[Guest]

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

 

[Guest]

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)

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Thanks John

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

S

 

[Guest]

Hi Tom,

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

S