date calcuation

[gs]

how would one express 1st Monday 3 months later?
given start date in a1 as 2007-06-11

adding 3 months is easy: =date(year(a10),month(a1)+3, day)
but how do I make sure if that is not Monday, I grab the coming Monday?


hopefully I don't macro for that

 

[Peo Sjoblom]

Here you go, with start date in A10 (you used both A10 and A1)

=DATE(YEAR(A10),MONTH(A10)+3, DAY(A10))-WEEKDAY(DATE(YEAR(A10),MONTH(A10)+3,
DAY(A10))-2)+7

 

[Peo Sjoblom]

If you always want it using today's date you can replace A10 with TODAY()

 

[Dana DeLouis]

Not any better, but here was my attempt.

=EDATE(A1,3)+MOD(14,WEEKDAY(EDATE(A1,3),3)+7)

 

[gs]

great, thank you all. wonderful and timely answers

 

[gs]

Edate is a bit easier to use.


In my case, despite excel 2003 help has edate but excel did not like it
=edate(A1,3) gave me #name?

 

[JE McGimpsey]

Take a look in Help for what it says about needing to load the Analysis
Toolpak Add-in...

 

[Peo Sjoblom]

Also note that if you distribute it to other users they must have ATP
installed as well or they will get the same error. That's the reason I
always try to use a built in function when possible

 

[gs]

I am not inclined to install the analysis pack either. otherwise I will
also have to install the other 9 PCs.
for now a couple of hidden work rows works wonderfully with date and weekday
functions