Need to create function to find first day of following month
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 |
Need to create function to find first day of following month
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 |
Need to create function to find first day of following month
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 |
Need to create function to find first day of following month
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 |
Need to create function to find first day of following month
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 |
Need to create function to find first day of following month
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 |
All times are GMT +1. The time now is 05:20 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com