VBA DateAdd Outputs Wrong Format

[patticlaire]

'm trying to add one month to a date, formatted like 11/30/2018, using the DateAdd fucntion, but no matter what date is input, the output is 12:00:00AM
The ranges for oneCell and twoCell are both in Date formatting in the excel worksheet. The billMonth is not being posted to a cell anywhere, it gets passed back to the first sub for more logic to be applied to it.


<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;"> For Each aWorksheet In ActiveWorkbook.Worksheets
If Left(aWorksheet.Name, 1) = "#" Then
siteNum = Trim(Right(Left(aWorksheet.Name, 5), 4))

Set oneCell = aWorksheet.Range("C:C").Find("2018")
Set twoCell = aWorksheet.Range("D:D").Find("2018")

If oneCell.Row > twoCell.Row Then
Set oneCell = oneCell.Offset(-1, 0)
End If

Do While oneCell.Value <> ""

billMonthDate = GetBillMonth(oneCell.Value, twoCell.Value)

'Then the function that is called:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Public Function GetBillMonth(ByVal startDate As Date, ByVal endDate As Date) As Date
Dim billMonth As Date
Dim startMonthDays As Integer
Dim endMonthDays As Integer

If DateDiff("m", startDate, endDate) > 1 Then
billMonth = DateTime.DateAdd("m", 1, startDate)

Else
startMonthDays = DateDiff("d", startDate, DateSerial(Year(startDate), Month(startDate) + 1, -1))
endMonthDays = Day(endDate)

If startMonthDays > endMonthDays Then
billMonth = oneCellDate
ElseIf startMonthDays < endMonthDays Then
billMonth = endDate
Else
'**** me
End If
End If

GetBillMonth = billMonth

End Function</code>The startDate is coming from a spreadsheet I'm analyzing. The macro loops through and looks at lots of dates, so I can't just put the date in serially to solve this problem. I just need dateadd to work.

I also posted this question here: https://stackoverflow.com/questions...-format?noredirect=1#comment97409677_55340636
</code></pre>

 

[DanteAmor]

Try this:
Change this line

[COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit]billMonth [/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit]=[/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit] DateTime[/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit]DateAdd[/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]"m"[/FONT][/FONT][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit],[/FONT][/FONT][/COLOR][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]1[/FONT][/FONT][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit],[/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit] startDate[/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit])[/FONT][/FONT][/COLOR][/COLOR]

By this line:

billMonth = DateSerial(Year(startDate), Month(startDate) + 1, Day(startDate))

Tell me, what date do you expect to get here?

[COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit]DateSerial[/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit]Year[/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit]startDate[/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit]),[/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit] Month[/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit]startDate[/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit])[/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit]+[/FONT][/FONT][/COLOR][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]1[/FONT][/FONT][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit],[/FONT][/FONT][/COLOR][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit]-[/FONT][/FONT][/COLOR][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]1[/FONT][/FONT][/COLOR][COLOR=#303336][COLOR=#303336][FONT=Consolas][FONT=inherit])[/FONT][/FONT][/COLOR][/COLOR]

If you want the end of the month date, it should look like this:

DateSerial(Year(startdate), Month(startdate) + 1[COLOR=#0000ff], 1[/COLOR]) [COLOR=#ff0000]- 1[/COLOR]