Solved

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

Posted on 2014-04-14
7
1,360 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

863 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

25 Experts available now in Live!

Get 1:1 Help Now