How to compare changing dates in VBA

mckmac

New Member
Joined
Jun 3, 2015
Messages
1
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,396
Messages
6,178,396
Members
452,844
Latest member
Shebl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top