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, 4
You 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 salespersonCreate a heading for the newly created scheduleList any products purchased
Summary
It's one thing to record data in a spreadsheet; the tricky bit is to turn that data into information that can become a business asset. 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.
