Month lookup with multiple years

[MrIainMacleod]

I have a spreadsheet which derives project milestone dates. Within our
company’s process, there is a monthly meeting where the agenda
deadline is the 1st Thursday of every month.

In order to derive the first meeting that the project can be
discussed, I have used the following formula:

=WORKDAY(IF((AD15)>=((LOOKUP(MONTH(AD15),B69:F92,F69:F92))),(LOOKUP
(MONTH(AD15)+1,B69:F92,F69:F92)),(LOOKUP(MONTH
(AD15),B69:F92,F69:F92)))-1,1)

Here, AD15 is the date where everything is ready for the meeting
(which can be any time in the month), B69:F92 is a range of date-
related columns* and F69:F92 the dates of the meetings.

* e.g. Column A = month (Jan-Dec), Column B = month number (1-12),
Column F = agenda deadline dates (8th Jan, 5th Feb etc.)

The formula worked whilst I only had the meeting dates for 2009 in the
table (it was initially a demo, that has grown into something we want
to use in anger).

Now that I have added dates for 2010, it’s all gone wrong. If the date
‘should’ be a 2010 date, it always delivers the 2009 version – because
it is looking for the month number, so picks the first April (or
whatever) in the table.

I’m sure that this is an easy one for you guys, and there is probably
an easy =IF statement I can add, but I’ve been banging my head against
the desk for a while, and would appreciate any guidance.

Cheers

Iain

 

[Bob Phillips]

Iain,

It is very difficult to visualise the data. Can you post the workbook
somewhere?

--
__________________________________
HTH

Bob

I have a spreadsheet which derives project milestone dates. Within our
company’s process, there is a monthly meeting where the agenda
deadline is the 1st Thursday of every month.

In order to derive the first meeting that the project can be
discussed, I have used the following formula:

=WORKDAY(IF((AD15)>=((LOOKUP(MONTH(AD15),B69:F92,F69:F92))),(LOOKUP
(MONTH(AD15)+1,B69:F92,F69:F92)),(LOOKUP(MONTH
(AD15),B69:F92,F69:F92)))-1,1)

Here, AD15 is the date where everything is ready for the meeting
(which can be any time in the month), B69:F92 is a range of date-
related columns* and F69:F92 the dates of the meetings.

* e.g. Column A = month (Jan-Dec), Column B = month number (1-12),
Column F = agenda deadline dates (8th Jan, 5th Feb etc.)

The formula worked whilst I only had the meeting dates for 2009 in the
table (it was initially a demo, that has grown into something we want
to use in anger).

Now that I have added dates for 2010, it’s all gone wrong. If the date
‘should’ be a 2010 date, it always delivers the 2009 version – because
it is looking for the month number, so picks the first April (or
whatever) in the table.

I’m sure that this is an easy one for you guys, and there is probably
an easy =IF statement I can add, but I’ve been banging my head against
the desk for a while, and would appreciate any guidance.

Cheers

Iain

 

[iainmacleod]

Can't post the workbook, but does this help?


A B C D E F
68 Meetings Agenda deadline
69 January 1 14 Wed 14 Jan 14th Thu 08 Jan
70 February 2 11 Wed 11 Feb 11th Thu 05 Feb
71 March 3 11 Wed 11 Mar 11th Thu 05 Mar
72 April 4 8 Wed 08 Apr 8th Thu 02 Apr
73 May 5 13 Wed 13 May 13th Thu 07 May
74 June 6 10 Wed 10 Jun 10th Thu 04 Jun
75 July 7 8 Wed 08 Jul 8th Thu 02 Jul
76 August 8 12 Wed 12 Aug 12th Thu 06 Aug
77 September 9 9 Wed 09 Sep 9th Thu 03 Sep
78 October 10 14 Wed 14 Oct 14th Thu 08 Oct
79 November 11 11 Wed 11 Nov 11th Thu 05 Nov
80 December 12 9 Wed 09 Dec 9th Thu 03 Dec
81 January 1 13 Wed 13 Jan 13th Thu 07 Jan
82 February 2 10 Wed 10 Feb 10th Thu 04 Feb
83 March 3 10 Wed 10 Mar 10th Thu 04 Mar
84 April 4 14 Wed 14 Apr 14th Thu 08 Apr
85 May 5 12 Wed 12 May 12th Thu 06 May
86 June 6 9 Wed 09 Jun 9th Thu 03 Jun
87 July 7 14 Wed 14 Jul 14th Thu 08 Jul

Column B is =MONTH(A69) etc.
Column C is =DAY(D69) etc.
Column D is =DATE(YEAR(A69),MONTH(A69),1+7*2)-WEEKDAY(DATE(YEAR
(A69),MONTH(A69),8-4)) etc.
Column E is =C69&IF(AND(MOD(C69,100)>=10,MOD(C69,100)<=14),"th",
CHOOSE(MOD
(C69,10)+1,"th","st","nd","rd","th","th","th","th","th","th")) etc.
Column F is =D69-6 etc.

The original formula is used to find the next agenda deadline from
column F.
i.e. If the date in AD15 is 6th March 2009, it would provide 2nd April
2009 (user has just missed the deadline for the March meeting, so has
to wait until April).

However, if the date in AD15 is 6th March 2010 it should produce 8th
April 2010 - what it currently gives is 2nd April 2009.

 

[iainmacleod]

A very messy solution, pending responses from the experts....

I've added a 'Year' column and created the following formula:

=IF(AND(AE15=LOOKUP(YEAR(AE15),B81:G92,G81:G92),AE15>=LOOKUP(MONTH
(AE15),B81:G92,G81:G92)),WORKDAY(IF((AE15)>=((LOOKUP(MONTH
(AE15),B69:G92,G69:G92))),(LOOKUP(MONTH(AE15)+1,B69:G92,G69:G92)),
(LOOKUP(MONTH(AE15),B69:G92,G69:G92)))-1,1),WORKDAY(IF((AE15)>=((LOOKUP
(MONTH(AE15),B81:G92,G81:G92))),(LOOKUP(MONTH
(AE15)+1,B81:G92,G81:G92)),(LOOKUP(MONTH
(AE15),B81:G92,G69:G92)))-1,1))

It works, but I'm not looking forward to updating all the other cells
which need similar logic....

 

[Ron Rosenfeld]

I have a spreadsheet which derives project milestone dates. Within our
company’s process, there is a monthly meeting where the agenda
deadline is the 1st Thursday of every month.

In order to derive the first meeting that the project can be
discussed, I have used the following formula:

=WORKDAY(IF((AD15)>=((LOOKUP(MONTH(AD15),B69:F92,F69:F92))),(LOOKUP
(MONTH(AD15)+1,B69:F92,F69:F92)),(LOOKUP(MONTH
(AD15),B69:F92,F69:F92)))-1,1)

You should be able to modify this formula to your data.

It assumes a "normal Excel date" in A1, and will return the date that is the
first "first Thu of the month" that occurs *after* the date in A1.

=IF(A1<(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-5)),
A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-5),A1-DAY(A1)+
32-DAY(A1-DAY(A1)+32)+8-WEEKDAY(A1-DAY(A1)+32-
DAY(A1-DAY(A1)+32)+8-5))

You could construct a "normal Excel date" in a variety of ways, depending on
how your data is set up, and merely substitute that for A1 in the above (or put
it into some cell and substitute that cell reference.
--ron

 

[iainmacleod]

You should be able to modify this formula to your data.

It assumes a "normal Excel date" in A1, and will return the date that is the
first "first Thu of the month" that occurs *after* the date in A1.

=IF(A1<(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-5)),
A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-5),A1-DAY(A1)+
32-DAY(A1-DAY(A1)+32)+8-WEEKDAY(A1-DAY(A1)+32-
DAY(A1-DAY(A1)+32)+8-5))

You could construct a "normal Excel date" in a variety of ways, dependingon
how your data is set up, and merely substitute that for A1 in the above (or put
it into some cell and substitute that cell reference.
--ron

Ron,

That's fantastic - many thanks.

I also need to have the formulas working backwards - i.e. to find the
first Thursday in the month (or whatever) prior. I'm guessing I need
to change some "+"s for "-"s?

 

[Ron Rosenfeld]

Ron,

That's fantastic - many thanks.

You're welcome. Glad to help.

I also need to have the formulas working backwards - i.e. to find the
first Thursday in the month (or whatever) prior. I'm guessing I need
to change some "+"s for "-"s?

First Thursday in the month prior to what?
--ron

 

[Ron Rosenfeld]

I also need to have the formulas working backwards - i.e. to find the
first Thursday in the month (or whatever) prior. I'm guessing I need
to change some "+"s for "-"s?

If you mean the first Thursday prior to the report completion date, then try:

=IF(A1>(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-5)),
A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-5),A1-DAY(A1)-
DAY(A1-DAY(A1))+8-WEEKDAY(A1-DAY(A1)-DAY(A1-DAY(A1))+8-5))

I'm not sure what you want if that date is the first Thursday. As written, it
will return the previous Thu (5/1/08 -- 4/3/08); but that behavior can be
changed by changing the ">" to ">="
--ron

 

[iainmacleod]

If you mean the first Thursday prior to the report completion date, then try:

=IF(A1>(A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-5)),
A1-DAY(A1)+8-WEEKDAY(A1-DAY(A1)+8-5),A1-DAY(A1)-
DAY(A1-DAY(A1))+8-WEEKDAY(A1-DAY(A1)-DAY(A1-DAY(A1))+8-5))

I'm not sure what you want if that date is the first Thursday.  As written, it
will return the previous Thu  (5/1/08 -- 4/3/08); but that behavior canbe
changed by changing the ">" to ">="
--ron

Ron,

That certainly seems to work - many, many thanks.

To explain the purpose of the formula...

The spreadsheet is designed to give users indicative dates of project
milestones, based on corporate authorisation processes. They can
either put the date they wish to initiate the project, hence the need
for the initial 'meeting on the following first Thursday' formula.
Alternatively, they can put in the date they want the project to
deliver, so that they know when they need to start - hence the 'prior
first Thursday' option.

Cheers

Iain

 

[Ron Rosenfeld]

Ron,

That certainly seems to work - many, many thanks.

To explain the purpose of the formula...

The spreadsheet is designed to give users indicative dates of project
milestones, based on corporate authorisation processes. They can
either put the date they wish to initiate the project, hence the need
for the initial 'meeting on the following first Thursday' formula.
Alternatively, they can put in the date they want the project to
deliver, so that they know when they need to start - hence the 'prior
first Thursday' option.

Cheers

Iain

Glad to help. Thanks for the feedback.
--ron