VLOOKUP and IF Functions

[Peter W Soady \(UK\)]

Hi all

I have a small problem which I seem unable to correct. I have a spreadsheet
which contain several columns of which two are dates. To simplify I have re
created a smaller table below. The last test date is the important date as
the week numbers are against this column

Col 1 Col 2 Col 3 Initial Test Last Test
Next Test Week Number
aaa aaa aaa 01 Dec 06 20 Jan 07
01 Mar 07
bbb bbb bbb 09 Dec 06 12 Jan 07
15 Feb 07
ccc ccc ccc 11 Nov 06 03 Jan 07
10 Mar 07

I need to calculate the week number that the next test is due on. It is
quite simple for weeks in the current financial year (last week of March 07)
as I use the formulae WEEKNUM(Next Test,2)-39, but of course when it gets
into the new financial year, the first week of April 07 the week number is
incorrect. I considered using VLOOKUP using the wee ending dates and week
numbers from a new worksheet, but unfortunately I seem to be stuck with this
function as I believe is needs to use the IF function and or the AND
function alongside it.

The VLOOKUP worksheet would look something like this:

Week End Date Week Number
03 Jan 07 42 / 07
10 Jan 07 43 / 07
17 Jan 07 44 / 07

and so on

I believe there must be an easier way. Can anyone please help before I tear
the remaining solitary hair out.

Many thanks in advance

Peter W S (UK)

 

[Bernie Deitrick]

Peter,

Something along the lines of

=WEEKNUM(NextTest,2)-IF(NextTest>DATEVALUE("31/3/2007"),X,Y)

The X and Y should be replaced by appropriate constants ....you'll have to
figure out your specific week number math values to use... and I may have
gotten the date string mixed up, since the US uses different strings than
the UK....

HTH,
Bernie
MS Excel MVP

 

[Peter W Soady \(UK\)]

Bernie

Many thanks for the time. I will try this today.

You have the date format correct

Peter
----------------------