How to compare changing dates in VBA

[mckmac]

I am very new to VBA and am teaching myself as I go. The one thing I need but cannot figure out is:

I have a spreadsheet. Each day user fills out a userform to input data as needed (around 50 inputs). The code once the user clicks "Submit" on the userform takes all of that data and places it into the FirstBlankRow in the data's corresponding columns. It also puts a date time stamp in column A of when the "Submit" button was clicked. In a sense, each row is a new day of data.

I need the code to be able to look at the sales from one month ago and compare that to today's sales. However, since different months have differing number of days, it is not as easy as an offset by -30 etc. If today is june 3, I need it to know to go back to may 3.

I have no idea how I would even begin to code this

Simplified Example:

Date DailySales +/-Day
5/1 367.42 24.08
5/2 501.89 104.94
5/3 445.23 -11.54
...
...
...
...
6/1 409.87 42.45
6/2 498.01 -3.89
6/3 527.17 81.94

where the sales from 6/2 is compared to 5/2 and the difference is placed in the row for 6/2 in column C

 

[Gary McMaster]

Welcome aboard!

The "Date" data type will handle most of the worries for you.

Perhaps the biggest worry will be what happens on the 31st day of months preceded by 28,29 or 30 day months. That may require some thoughtful logic on your part which may be simply notifying the user that last month only had 30 days and they are asking for data from a day that does not exist.

The sample below shows that the Date data type has a lot of versatiity and won't let you feed it an invalid date as in the last line.

Public Sub Test()

Dim myDate As Date

myDate = "6/30/15"

MsgBox Format(myDate, "dd mmm yyyy")

MsgBox myDate - 30

MsgBox myDate + 62

MsgBox myDate + 63

MsgBox Format(Now, "mmm")

MsgBox Format(Now + 93, "mmm")

MsgBox Now + 93

MsgBox Format(myDate, "m")

Select Case Format(myDate, "m")

    Case 4, 6, 9, 11
        MsgBox myDate & " is a 30 day month"
    Case 2
        MsgBox myDate & " could be a 28 or 29 day month "
    Case Else
        MsgBox myDate & " is a 31 day month"
        
End Select

myDate = "6/31/15" 'Raise error, invalid date

'Leap year would be a good one to test. It will surely be handled properly