A Simple Example Of How To Manage Dates With Excel And VBA
Dates can sometimes present a problem to those new to Excel.
It's usually not enough to simply record the date of something; you probably need to know things like an elapsed interval, the day of the week and various other items of information that VBA can easily deliver.
A Case Study Of Using Dates Effectively One way of understanding dates in Excel is to look at a real life situation.
Managing customers can sometimes be a nightmare, but creating a customer contact schedule based on some simple rules is simple.
An overview of customers and contact dates might look something like this:
But how would you turn that data into solid business information? In a real life situation you'll have more information like the contact person, what was discussed and products purchased, but we'll just focus on the issues for managing dates.
We're going to create a contact schedule which tells us which customers need to be contacted based on the "next contact" schedule that we've set up.
Luckily for us, Excel and VBA make it easy to work with dates.
You'll need two worksheets set up; one called "work" to hold the information above and another called "schedule" where we'll record the customers that need to be contacted.
How VBA Date Functions Can Help In Working With Dates There are a few different date functions in VBA but the one we'll look at is the dateDiff function.
Applying this command returns to us the difference between two dates, which is all we need to create a work schedule.
1.
Select the data
Range("A2").
Activate last = ActiveCell.
End(xlDown).
Row 2.
Loop through the data and assign variables
ct=ct+1 For x = 2 To last With ActiveCell customer =.
Value lastContact =.
Offset(0, 1).
Value nextContact =.
Offset(0, 2).
Value End With 3.
Work out how many weeks have elapsed since the last contact
due = DateDiff("w", lastContact, Now()) 4.
If more weeks has elapsed since the scheduled contact then copy the customer to a new worksheet called "schedule"
If due > nextContact Then ct = ct + 1 Sheets("work").
Range("a" & x).
EntireRow.
Copy _ Sheets("schedule").
Range("a" & ct) End If 5.
Go to the next customer
ActiveCell.
Offset(1, 0).
Activate Next The VBA code creates a current work schedule; a listing of customers that need to be contacted and the worksheet can be printed out or emailed to the relevant staff member to take action.
Enhancements To The VBA Code In a real life situation you could easily add more functionality to form the basis of a fully functioning work schedule:
With a little planning and knowledge of Excel and VBA, you'll have the tools needed to become more efficient and professional in whatever you're doing.
It's usually not enough to simply record the date of something; you probably need to know things like an elapsed interval, the day of the week and various other items of information that VBA can easily deliver.
A Case Study Of Using Dates Effectively One way of understanding dates in Excel is to look at a real life situation.
Managing customers can sometimes be a nightmare, but creating a customer contact schedule based on some simple rules is simple.
An overview of customers and contact dates might look something like this:
Customer, Last Contact, Next Contact (weeks) a, 20-Jul,6 b, 21-Aug, 4 c, 2-Jun, 3 d, 5-Apr, 5 e, 24-Aug, 7 f, 5-Mar, 4You know the customer, the date of the last contact and when the next contact should be made.
But how would you turn that data into solid business information? In a real life situation you'll have more information like the contact person, what was discussed and products purchased, but we'll just focus on the issues for managing dates.
We're going to create a contact schedule which tells us which customers need to be contacted based on the "next contact" schedule that we've set up.
Luckily for us, Excel and VBA make it easy to work with dates.
You'll need two worksheets set up; one called "work" to hold the information above and another called "schedule" where we'll record the customers that need to be contacted.
How VBA Date Functions Can Help In Working With Dates There are a few different date functions in VBA but the one we'll look at is the dateDiff function.
Applying this command returns to us the difference between two dates, which is all we need to create a work schedule.
1.
Select the data
Range("A2").
Activate last = ActiveCell.
End(xlDown).
Row 2.
Loop through the data and assign variables
ct=ct+1 For x = 2 To last With ActiveCell customer =.
Value lastContact =.
Offset(0, 1).
Value nextContact =.
Offset(0, 2).
Value End With 3.
Work out how many weeks have elapsed since the last contact
due = DateDiff("w", lastContact, Now()) 4.
If more weeks has elapsed since the scheduled contact then copy the customer to a new worksheet called "schedule"
If due > nextContact Then ct = ct + 1 Sheets("work").
Range("a" & x).
EntireRow.
Copy _ Sheets("schedule").
Range("a" & ct) End If 5.
Go to the next customer
ActiveCell.
Offset(1, 0).
Activate Next The VBA code creates a current work schedule; a listing of customers that need to be contacted and the worksheet can be printed out or emailed to the relevant staff member to take action.
Enhancements To The VBA Code In a real life situation you could easily add more functionality to form the basis of a fully functioning work schedule:
- Include the last comments and the name of the salesperson
- Create a heading for the newly created schedule
- List any products purchased
With a little planning and knowledge of Excel and VBA, you'll have the tools needed to become more efficient and professional in whatever you're doing.
Source...