[mso] Re: Date matching query

Robert

Have you tried using a vlookup table to return the 2nd  Working day into
column B.
From your description, a vlookup should work.
You shouldn't need an VBA for this type of situation

Regards
Norman Pearce 
IM&T Learning Manager
The Training Centre
Ealing Hospital NHS Trust 
Developing the foundations for your success
 
 
 


-----Original Message-----
From: robert.allen-turl@xxxxxxxxxxxx [mailto:robert.allen-turl@xxxxxxxxxxxx]

Sent: 24 August 2006 21:50
To: mso@xxxxxxxxxxxxx
Subject: [mso] Date matching query


Hope someone can solve this  problem because it's kept me up for several 
nights.
My daughter needs an Excel formula that matches a list of several hundred 
dates against a table of sixty dates (which will grow as time passes) and 
reports the correct match in each case.

Column A holds dates that documents were received in the office.The dates 
are in random order and will be added to continuously.

Column B needs to calculate when each document should have been despatched 
from the office. This 'deadline' is the 2nd working day of each month. So 
if A7 is 8 June 2006 B7 should be 4 July 2006.  If A8 is 3 August B8 
should be 4 Sept 20006, etc.

Column C holds the actual date of despatch.

Column D calculates whether the document went on time (e.g the date in 
column B is less than the date in column C) or was late. Everything 
depends on the date in Column B being correct in each case.

I created a table listing the '2nd working day' for each month. What I 
can't work out is how to match all the receipt date in column A (e.g. 8 
June 2006) against this table and put the correct date in column B for 
each document.

The 'month' formula will not work because the deadline for documents 
arriving on 1st or 2nd working day of any month will be that same month. 
Only if the document arrives from 3rd  working day onward does the 
deadline become the 2nd working day of the following month.

I tried using a 'Look Up' table. This only works if the receipt date is an 
exact match for one of the dates in the table. Any other date gets 
'rounded down' (e.g. documents received on 8 June were 'matched' to 2 June 
instead of 4 July.

I tried a nested 'IF' approach. (e.g. IF the date in A3 is higher than the 
first date in the month table but lower than the second B3 shows the 
second date, IF A3 is higher than the second but lower than the third 
date, etc.) but cannot work out how to do this for every date in column A 
without changing the formula for every single entry in column A.

If anyone has a solution I would be delighted to hear it. If your proposal 
involves Visual Basic please put it in full because I have never used VBA 
and won't understand general references to "just add the following. . . ."

Appreciate your help in this

Kind regards

Robert Allen-Turl
79 Calver Crescent
Sapcote
Leics LE9 4JD
Tel: +44 1455 272762
Fax: +44 1455 271478
Mobile: 07818 272762

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the
dropdown menu at the top.  This will allow you to unsubscribe your email
address or change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions.  Once you are a member of the files group, you can go
here to upload/download files: http://www.smartgroups.com/vault/msofiles

If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, visit the group's homepage and use the dropdown 
menu at the top.  This will allow you to unsubscribe your email address or 
change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles

If you are using Outlook and you see a lot of unnecessary code in your email 
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************

Other related posts: