date calcuation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gs - 12 Jun 2007 17:37 GMT

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

Reply to this Message

Peo Sjoblom - 12 Jun 2007 17:47 GMT

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

Signature

Regards,

Peo Sjoblom

> how would one express 1st Monday 3 months later?
> given start date in a1 as 2007-06-11
[quoted text clipped - 3 lines]
>
> hopefully I don't macro for that

Reply to this Message

Peo Sjoblom - 12 Jun 2007 17:51 GMT

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

Signature

Regards,

Peo Sjoblom

> Here you go, with start date in A10 (you used both A10 and A1)
>
[quoted text clipped - 8 lines]
>>
>> hopefully I don't macro for that

Reply to this Message

Dana DeLouis - 12 Jun 2007 18:18 GMT

Not any better, but here was my attempt.

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

Signature

Dana DeLouis

> Here you go, with start date in A10 (you used both A10 and A1)
>
[quoted text clipped - 8 lines]
>>
>> hopefully I don't macro for that

Reply to this Message

gs - 12 Jun 2007 22:08 GMT

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?

> Not any better, but here was my attempt.
>
[quoted text clipped - 12 lines]
>>>
>>> hopefully I don't macro for that

Reply to this Message

JE McGimpsey - 12 Jun 2007 22:18 GMT

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

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

Reply to this Message

Peo Sjoblom - 12 Jun 2007 22:27 GMT

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

Signature

Regards,

Peo Sjoblom

> Edate is a bit easier to use.
>
[quoted text clipped - 17 lines]
>>>>
>>>> hopefully I don't macro for that

Reply to this Message

gs - 13 Jun 2007 00:27 GMT

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

> 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
[quoted text clipped - 22 lines]
>>>>>
>>>>> hopefully I don't macro for that

Reply to this Message

gs - 12 Jun 2007 18:19 GMT

great, thank you all. wonderful and timely answers

> how would one express 1st Monday 3 months later?
> given start date in a1 as 2007-06-11
[quoted text clipped - 3 lines]
>
> hopefully I don't macro for that

Reply to this Message