[mso] Date matching query
- From: robert.allen-turl@xxxxxxxxxxxx
- To: mso@xxxxxxxxxxxxx
- Date: Fri, 25 Aug 2006 17:28:55 +0100
Ladies and gentlemen,
Huge vote of thanks to everyone who has looked at this.
David Smart (smartware@xxxxxxxxxxxxxxx) provided the solution using
SumProduct:
=SUMPRODUCT((F$3:F$17>A2)*(F$2:F$16<=A2)*(F$3:F$17))
(Column F is the list of '2nd working day' dates, Column A the date a
particular document was received.)
Have never used this function before but will certainly be working it to
death from now on!
Kind regards
Robert Allen-Turl
79 Calver Crescent
Sapcote
Leics LE9 4JD
Tel: +44 1455 272762
Fax: +44 1455 271478
Mobile: 07818 272762
"Pearce, Norman" <Norman.Pearce@xxxxxxxxxx>
Sent by: mso-bounce@xxxxxxxxxxxxx
25/08/2006 14:31
Please respond to
mso@xxxxxxxxxxxxx
To
"'mso@xxxxxxxxxxxxx'" <mso@xxxxxxxxxxxxx>
cc
Subject
[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).
//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).
//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).
//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:
