Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
datediff help
Hello,
I have this sub which needs to give me each end of month between the first date and now Dim diffdatemonth() dim currentdate, todaydate as date dim currentmonth, todaymonth, currentyear as date dim inceptiondate as date dim difference as string dim i as integer dim datedifference as integer inceptiondate = "2006/01/06" todaydate = Now() currentdate = DateSerial(Year(inceptiondatedate), Month(inceptiondate) + 1, 0) ' the current date it is the first end of month so here I would like to have 2006/01/31 currentmonth = Month(currentdate) todaymonth = Month(todaydate) datedifference = 1 i = 1 'until the difference beetween currentdate and todaydate it is greater than 0 Do While datedifference 0 currentmonth = Month(currentdate) currentyear = Year(currentdate) difference = datediff("d", todaydate, currentdate) datedifferencee = CInt(difference) currentdate = DateSerial(currentyear, (currentmonth + i), 1) difference = datediff("m", todaydate, currentdate) datedifference = CInt(difference) currentmonth = currentmonth + 1 Loop End Sub I have something wrong in this code and I do not understand why; could someone to help me Ina |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
datediff help
ina Change Dim currentmonth, todaymonth, currentyear As Date to Dim currentmonth, todaymonth, currentyear As Integer Also you have a couple of typos with your variables within your code they are not the same as what you declared. To force variable declartion place this OPTION EXPLICIT as the 1st lin entry on your vba module sheet. This will then highlight mistype variables when you try to run it or use the Debug option. You can also turn this option on for all new workbooks by Tools Menu Options Editor Tab and place a tick in the Require Variabl Declaration When Declaring variables I also use a mix of upper and lower cas letters I then enter all my macro code in lower case and if I type th variable name correctly it changes to the exact way I declared i (lower and upper case letters) try this code which I made a couple of changes to dim diffdatemonth() Dim currentdate, todaydate As Date 'Dim currentmonth, todaymonth, currentyear As Date Dim currentmonth, todaymonth, currentyear As Integer Dim inceptiondate As Date Dim difference As String Dim i As Integer Dim datedifference As Integer inceptiondate = "2006/01/06" todaydate = Now() currentdate = DateSerial(Year(inceptiondate), Month(inceptiondate) + 1 0) ' the current date it is the first end of month so here I would lik to 'have 2006 / 1 / 31 currentmonth = Month(currentdate) todaymonth = Month(todaydate) datedifference = 1 i = 1 'until the difference beetween currentdate and todaydate it is greater 'than 0 Do While datedifference 0 currentmonth = Month(currentdate) currentyear = Year(currentdate) difference = DateDiff("d", todaydate, currentdate) datedifference = CInt(difference) currentdate = DateSerial(currentyear, (currentmonth + i), 1) difference = DateDiff("m", todaydate, currentdate) datedifference = CInt(difference) currentmonth = currentmonth + 1 Loo -- mudrake ----------------------------------------------------------------------- mudraker's Profile: http://www.excelforum.com/member.php...nfo&userid=247 View this thread: http://www.excelforum.com/showthread.php?threadid=53526 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
datediff help
I'm not sure where you're going with your code, but wouldn't something like
this be easier? '---beginning of code---- Option Explicit Sub CalcEOMths() Dim intCtr As Integer Dim intMthNum As Integer Dim intYrNum As Integer Dim dtCalcd As Date Const dtStartDate As Date = #1/6/2006# intMthNum = Month(dtStartDate) intYrNum = Year(dtStartDate) intCtr = 1 dtCalcd = DateSerial(Year:=intYrNum, Month:=intMthNum + intCtr, Day:=1) - 1 Do Until dtCalcd Date MsgBox Format(dtCalcd, "mm/dd/yyyy") intCtr = intCtr + 1 dtCalcd = DateSerial(Year:=intYrNum, Month:=intMthNum + intCtr, Day:=1) - 1 Loop MsgBox "done" End Sub '---end of code---- Does that give you something to work with? *********** Regards, Ron XL2002, WinXP-Pro "ina" wrote: Hello, I have this sub which needs to give me each end of month between the first date and now Dim diffdatemonth() dim currentdate, todaydate as date dim currentmonth, todaymonth, currentyear as date dim inceptiondate as date dim difference as string dim i as integer dim datedifference as integer inceptiondate = "2006/01/06" todaydate = Now() currentdate = DateSerial(Year(inceptiondatedate), Month(inceptiondate) + 1, 0) ' the current date it is the first end of month so here I would like to have 2006/01/31 currentmonth = Month(currentdate) todaymonth = Month(todaydate) datedifference = 1 i = 1 'until the difference beetween currentdate and todaydate it is greater than 0 Do While datedifference 0 currentmonth = Month(currentdate) currentyear = Year(currentdate) difference = datediff("d", todaydate, currentdate) datedifferencee = CInt(difference) currentdate = DateSerial(currentyear, (currentmonth + i), 1) difference = datediff("m", todaydate, currentdate) datedifference = CInt(difference) currentmonth = currentmonth + 1 Loop End Sub I have something wrong in this code and I do not understand why; could someone to help me Ina |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
datediff help
mudraker wrote: ina Change Dim currentmonth, todaymonth, currentyear As Date to Dim currentmonth, todaymonth, currentyear As Integer Careful: in both cases these lines declare the first two variables as Variants and only the third as the specified type. rest snipped |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
datediff help
Thanks a lot for this help
:) Ina |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
DateDIFF | Excel Programming | |||
datediff in excel 2003.. Agecalculation | Excel Programming | |||
Help with datediff vba | Excel Programming | |||
DateDiff in Excel | Excel Programming | |||
DateDiff problem | Excel Programming |