Solved

Excel VBA - How to loop through a range and compare dates

Posted on 2014-04-14
7
1,339 Views
Last Modified: 2014-04-14
Good day

I have a client who hires out vehicles and needs to charge their client toll road charges. The scenario is as follows: a invoice is generated for each hire for the period of time the vehicle was hired out for. The company managing the toll roads sends my client a spreadsheet of all the toll points the companies vehicles went through over a specific period of time (normally monthly). The client then needs to calculate how much to charge their client for the tolls charge over the hire period.

I've got two lists that I need to work with, firstly a list from the toll company of vehicle registration numbers, the date and the charge. My client then has a list of invoices and with the start and end date of the hire.

The toll data looks like this:
Vehicle            Date                 Charge
BD71MKGP      8/12/2013      3.3
BD75MKGP      8/12/2013      2.31
BD82XRGP      11/12/2013      2.43
BD82XRGP      11/12/2013      2.46
BF72YEGP      13/12/2013      0.6
BF72YEGP      13/12/2013      2.52

The client data looks like this:
Invoice                    Start Date         End Date         Vehicle
IN187626                  06/12/2013      10/12/2013      BD71MKGP
IN187649                    10/12/2013      13/12/2013      BF72YEGP
IN187819                11/12/2013      11/12/2013      BD82XRGP


So for example, if we take the first vehicle from the first list we would then have to search the second list to first find the list then to determine if the date from the first list falls between the start and end date of the row in the second list.

My VBA is very rusty and I'm not sure how to go about stepping through the second list and checking the date.

Could anybody give me a code example?

Many thanks

Rob
0
Comment
Question by:KempstonIT
  • 3
  • 3
7 Comments
 
LVL 24

Accepted Solution

by:
Steve earned 500 total points
ID: 39998746
You dont' really need VBA for this one.

You could use a SUMIFS.

See the attached workbook for example.
Example.xlsx
0
 

Author Comment

by:KempstonIT
ID: 39998790
Hi Steve

Thanks for the example. It's not calculating correctly though. For example invoice IN187649 for vehice BF72YEGP should add up to 5.64 as all the toll charges fall within the contract start and end date.

I've never done a SUMIFS before, I'll try and get my head around it though.
0
 
LVL 24

Expert Comment

by:Steve
ID: 39998871
Ah missed some dollar signs out... try the following:

=SUMIFS($D$2:$D$8,$B$2:$B$8,K2,$C$2:$C$8,">="&I2,$C$2:$C$8,"<="&J2)

or

=SUMIFS(D:D,B:B,K2,C:C,">="&I2,C:C,"<="&J2)

This second formula should make more sense.
They both do the same, but the second looks at the whole column rather than a specific range or rows.
Example.xlsx
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 45

Expert Comment

by:aikimark
ID: 39998872
Do lots of testing, since you're using European dates (d/m/yyyy) instead of US dates (m/d/yyyy).  This difference can cause unusual behavior.
0
 
LVL 24

Expert Comment

by:Steve
ID: 39998886
Indeed, date formats can be the curse of Excel some times.
I prefer dates in format "dd mmm yyyy" as they are easier to manage.

As for the SUMIFS formula.. a breakdown...

=SUMIFS(D:D,  = Sum up column D (Cost)
B:B, K2,             = Where column B is equal to K2 (Registration Number)
C:C, ">="&I2,    = Where column C is Greater or Equal to I2 (Start Date)
C:C,"<="&J2)    = Where column C is Less Than or Equal to J2 (End Date)
0
 

Author Comment

by:KempstonIT
ID: 39999213
Thanks Steve, I've managed to get it working with SUMIFS but am battling with the dates!!! Any suggestions on formatting them?
0
 

Author Comment

by:KempstonIT
ID: 40000946
All sorted, I had to format the date using the following formula:
=DATEVALUE(CONCATENATE(TEXT(DAY(N5),"0#"),"/",TEXT(MONTH(N5),"0#"),"/",YEAR(N5)))
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now