[mso] Re: Date matching query
- From: "David Smart" <smartware@xxxxxxxxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Fri, 25 Aug 2006 18:29:29 +1000
Sounds as though the date check you want is
received date < current month's deadline and >= previous month's deadline
or possibly
received date <= current month's deadline and > previous month's deadline
depends what happens if it arrives on the deadline date.
Now, SumProduct should be able to do this. Assume your column of deadline
dates is in X10:X310
=SumProduct((X10:X310>A7)*(X9:X309<=A7)*(X10:X30))
I haven't tested this.
The idea is that the first bit will find all deadlines that the document has
met (i.e. the one it really met and all those in the future); the second bit
will find all the deadlines that the document missed. There should only be
one occasion when both of these are true at once. The third bit is the
actual deadline date involved. The binary arithmetic should do the rest.
Remember that dates are numbers, so the result of this will be a number.
Then simply format the result cell as a date.
Hope it's right, or close to right.
Regards, Dave S
----- Original Message -----
From: <robert.allen-turl@xxxxxxxxxxxx>
To: <mso@xxxxxxxxxxxxx>
Sent: Friday, August 25, 2006 6:50 AM
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
*************************************************************
Other related posts:
