Finding a future date

[Francis Hookham]

I need to find the date when several events occur forward from today.

Typical would be the Wednesday in the second week of next May.

Given:
A1 = 2 (second week of the month)
A2 = 4 (fourth day of the week when Sunday = 1)
A3 = 5 (the fifth month - May)

Can you help me with the formula in A4?
I just cannot see it yet!

 

[Sandy Mann]

With the year in A4, adapting a formula posted by Peo Sjoblom in February:

=DATE(A4,A3,1)+7*A1-WEEKDAY(DATE(A4,A3,1)-A2)

returns May 9 2007

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Francis Hookham]

Many thanks Mandy - that's great - but what I am now struggling with is to
get next year's date if we are past it today.

In my trial I have the data in col A,B and C.



The Date column 4 is calculated using:

=DATE(YEAR(TODAY()),C5,1)+7*A5-WEEKDAY(DATE(YEAR(TODAY()),C5,1)-B5)



I have added column 5 to show what the date should be when today is reached
(14/9/06).



Try as I may for the last 2 hours I cannot get an IF statement to get dates
before TODAY() to be next year



Week WeekDay Month Date Should be

2 4 9 Wed, 13 Sep 06 Thu, 13
Sep 07

2 5 9 Thu, 14 Sep 06 Fri,
14 Sep 07

3 6 9 Fri, 15 Sep 06 Fri,
15 Sep 06

3 7 9 Sat, 16 Sep 06 Sat,
16 Sep 06

3 1 9 Sun, 17 Sep 06 Sun,
17 Sep 06



This is as far as I have got but it does not make sense!!!

=if(DATE(YEAR(TODAY()),C2,1)+7*A2-WEEKDAY(DATE(YEAR(TODAY()),C2,1)-B2)<today(),DATE(YEAR(TODAY())+1,C2,1)+7*A2-WEEKDAY(DATE(YEAR(TODAY())+1,C2,1)-B2)<today(),
DATE(YEAR(TODAY()),C2,1)+7*A2-WEEKDAY(DATE(YEAR(TODAY()),C2,1)-B2)<today())



Any thoughts up there a few miles north of Kinross where we enjoy a snack at
the Loch Leven Larder when staying with our son.



Francis Hookham

 

[Roger Govier]

Hi Francis

Rather long winded, but seems to work
=IF(DATE(YEAR(TODAY()),C1,1)+7*A1-WEEKDAY(DATE(YEAR(TODAY()),C1,1)-B1)
<=TODAY(),
DATE(YEAR(TODAY())+1,C1,1)+7*A1-WEEKDAY(DATE(YEAR(TODAY())+1,C1,1)-B1),
DATE(YEAR(TODAY()),C1,1)+7*A1-WEEKDAY(DATE(YEAR(TODAY()),C1,1)-B1))

 

[Sandy Mann]

Hi Frances,

Taking your formula, (I don't write formulas I just steal other people's
<g>), and adding a test :

###(DATE(YEAR(TODAY()),C5,1)+7*A5-WEEKDAY(DATE(YEAR(TODAY()),C5,1)-B5)<=TODAY())###

to see if the calculated date has past which will add a year, (ie TRUE which
XL will coerce to 1), if it has:

=DATE(YEAR(TODAY())+(DATE(YEAR(TODAY()),C5,1)+7*A5-WEEKDAY(DATE(YEAR(TODAY()),C5,1)-B5)<=TODAY()),C5,1)+7*A5-WEEKDAY(DATE(YEAR(TODAY()),C5,1)-B5)

Many thanks Mandy

What? do you think I'm Mandy Miller?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Roger Govier]

Hi Sandy & Frances

I can see your amendment is shorter, and fully understand the addition
of 1 to the Year if True, however your formula returns
Thursday 13 September in row 1, whereas mine returns Wednesday 12th
September 2007.
This is with data of
A1=2, B1=4, C1=9.

I know Francis put in the second posting that the original formula
returned Wednesday 12th September 2006 whilst the correct result was
expected to be Thursday 13th September 2007.

That result however is inconsistent with the first stated requirement
which was column B stated weekday, where 1 = Sunday and therefore 4
should be Wednesday.

 

[Sandy Mann]

Hi Roger,

I didn't check into it that close, I just took the OP's reported #Date# &
#Should Be# dates.

If it is what you say then adding # +1 # to the WEEKDAY()'s as in:

=DATE(YEAR(TODAY())+(DATE(YEAR(TODAY()),C5,1)+7*A5-WEEKDAY(DATE(YEAR(TODAY()),C5,1)-B5)+1<=TODAY()),C5,1)+7*A5-WEEKDAY(DATE(YEAR(TODAY()),C5,1)-B5+1)

should produce the right answer ie Wednesday 12 September 2007 for

A5 = 2, B5 = 4, C5 = 9


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Francis Hookham]

Thanks both Sandy (apologies for the typo - it's me age!) and Roger.

I now have just what I want:

=IF(DATE(YEAR(TODAY()),C3,1)+7*A3-WEEKDAY(DATE(YEAR(TODAY()),C3,1)-B3)<=TODAY(),DATE(YEAR(TODAY())+1,C3,1)+7*A3-WEEKDAY(DATE(YEAR(TODAY())+1,C3,1)-B3),DATE(YEAR(TODAY()),C3,1)+7*A3-WEEKDAY(DATE(YEAR(TODAY()),C3,1)-B3))

I am most grateful

Francis (not Frances!)

 

[Sandy Mann]

You're very welcome Francis.

Thanks both Sandy (apologies for the typo - it's me age!)

If it's an age thing then you may well remember Mandy Miller. She was a
child star in British films in the late 1940's/early 1950's - sort of an
English Shirley Temple.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


"

 

[Roger Govier]

Sorry Francis
That was my typo - and I claim age exemption as well!!!
Glad you got it all sorted to your satisfaction.

 

[Francis Hookham]

Actually there is one more unnecessary time wasting thing it would be
interesting to try. What you have helped do is to save a friend having to
constantly check when the next of a series of meetings will occur - he could
perfectly well look up in a calendar and what we have now is adequate.
However:



Meetings appear in, say, A1 as '4th Thursday in October' and are represented
in

B1 4

C1 5

D1 10

With the future date (the formula you have given) in E1



I should like try breaking down 4th Thursday in October into its
constituents by formulae in columns B, C and D which could be hidden, rather
than let him make mistakes in entering B, C and D himself.



Finding the LEFT(week) is easy. MID(weekday) and RIGHT(month) are easy to
pick out, although I shall have to scratch my balding head to find the 3rd
space. But I have no idea how to convert Thursday into WEEKDAY and October
into MONTH.



Any suggestions?



Francis



I didn't remember Mandy Miller but Wikipedia brought her rushing back

 

[Roger Govier]

Hi Francis

Create two named ranges,
Days Sunday, ....... ,Saturday
Months January, .... ,December

A2 =LEFT(A1)
A3 =MATCH(MID(A1,5,FIND(" ",A1,5)-5),Days,0)
A4 =MATCH(MID(A1,FIND("^",SUBSTITUTE(A1," ","^",3))+1,255),Months,0)

 

[Francis Hookham]

Thanks Roger - I remembered FIND from the early days but have not used MATCH
before

Francis - I'll zip up now!