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

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



--
Regards,

Peo Sjoblom



"gs" <(E-Mail Removed)> wrote in message
news:uKqAp$(E-Mail Removed)...
> 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
>

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


--
Regards,

Peo Sjoblom



"Peo Sjoblom" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>
> --
> Regards,
>
> Peo Sjoblom
>
>
>
> "gs" <(E-Mail Removed)> wrote in message
> news:uKqAp$(E-Mail Removed)...
>> 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
>>
>
>

Not any better, but here was my attempt.

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

--
Dana DeLouis

"Peo Sjoblom" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>
> --
> Regards,
>
> Peo Sjoblom
>
>
>
> "gs" <(E-Mail Removed)> wrote in message
> news:uKqAp$(E-Mail Removed)...
>> 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
>>
>
>

great, thank you all. wonderful and timely answers
"gs" <(E-Mail Removed)> wrote in message
news:uKqAp$(E-Mail Removed)...
> 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
>

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?

"Dana DeLouis" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Not any better, but here was my attempt.
>
> =EDATE(A1,3)+MOD(14,WEEKDAY(EDATE(A1,3),3)+7)
>
> --
> Dana DeLouis
>
> "Peo Sjoblom" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>>
>>
>> --
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>>
>> "gs" <(E-Mail Removed)> wrote in message
>> news:uKqAp$(E-Mail Removed)...
>>> 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
>>>
>>
>>
>
>

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

In article <(E-Mail Removed)>,
"gs" <(E-Mail Removed)> wrote:

> 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?

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


--
Regards,

Peo Sjoblom



"gs" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>
> "Dana DeLouis" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Not any better, but here was my attempt.
>>
>> =EDATE(A1,3)+MOD(14,WEEKDAY(EDATE(A1,3),3)+7)
>>
>> --
>> Dana DeLouis
>>
>> "Peo Sjoblom" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>>
>>>
>>>
>>> --
>>> Regards,
>>>
>>> Peo Sjoblom
>>>
>>>
>>>
>>> "gs" <(E-Mail Removed)> wrote in message
>>> news:uKqAp$(E-Mail Removed)...
>>>> 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
>>>>
>>>
>>>
>>
>>
>
>

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


"Peo Sjoblom" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
> --
> Regards,
>
> Peo Sjoblom
>
>
>
> "gs" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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?
>>
>> "Dana DeLouis" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Not any better, but here was my attempt.
>>>
>>> =EDATE(A1,3)+MOD(14,WEEKDAY(EDATE(A1,3),3)+7)
>>>
>>> --
>>> Dana DeLouis
>>>
>>> "Peo Sjoblom" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>>
>>>>
>>>>
>>>> --
>>>> Regards,
>>>>
>>>> Peo Sjoblom
>>>>
>>>>
>>>>
>>>> "gs" <(E-Mail Removed)> wrote in message
>>>> news:uKqAp$(E-Mail Removed)...
>>>>> 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
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>