date after another date
 

I'm setting up a spreadsheet which alerts us when a certain assignment is due.
In cell a3 is the start date.

I want to look at the start date, add 360 calendar days to it, and then
calculate the first workday in January following the 360 days.

i.e. if start date is 6/30/08, i'd want the formula to return the date
1/4/2010.
if start date is 4/30/09, i'd want the formula to return the date
1/3/2011.

and so forth.

thanx.

date after another date
 

Create a list of all January holiday dates and put them in a range named
holidays, an use

=WORKDAY(DATE(YEAR(A20+360),12,31),1,holidays)

--
__________________________________
HTH

Bob

"Gator Girl" wrote in message
...
I'm setting up a spreadsheet which alerts us when a certain assignment is
due.
In cell a3 is the start date.

I want to look at the start date, add 360 calendar days to it, and then
calculate the first workday in January following the 360 days.

i.e. if start date is 6/30/08, i'd want the formula to return the date
1/4/2010.
if start date is 4/30/09, i'd want the formula to return the date
1/3/2011.

and so forth.

thanx.

date after another date
 

On Wed, 20 Aug 2008 12:22:11 -0700, Gator Girl
wrote:

I'm setting up a spreadsheet which alerts us when a certain assignment is due.
In cell a3 is the start date.

I want to look at the start date, add 360 calendar days to it, and then
calculate the first workday in January following the 360 days.

i.e. if start date is 6/30/08, i'd want the formula to return the date
1/4/2010.
if start date is 4/30/09, i'd want the formula to return the date
1/3/2011.

and so forth.

thanx.

Try this:

=WORKDAY(DATE(YEAR(A3+360),13,1),1)

If the formula gives a #NAME! error, look up the WORKDAY worksheet function in
HELP for instructions to install the Analysis Tool Pack.
--ron

date after another date
 

There is a problem with that approach Ron. If the 1st of Jan is say a
Saturday, your formula returns the Monday date. But that will be the New
Year's day holiday, so it should be skipped.

--
__________________________________
HTH

Bob

"Ron Rosenfeld" wrote in message
...
On Wed, 20 Aug 2008 12:22:11 -0700, Gator Girl
wrote:

I'm setting up a spreadsheet which alerts us when a certain assignment is
due.
In cell a3 is the start date.

I want to look at the start date, add 360 calendar days to it, and then
calculate the first workday in January following the 360 days.

i.e. if start date is 6/30/08, i'd want the formula to return the date
1/4/2010.
if start date is 4/30/09, i'd want the formula to return the date
1/3/2011.

and so forth.

thanx.

Try this:

=WORKDAY(DATE(YEAR(A3+360),13,1),1)

If the formula gives a #NAME! error, look up the WORKDAY worksheet
function in
HELP for instructions to install the Analysis Tool Pack.
--ron

date after another date
 

On Wed, 20 Aug 2008 21:17:19 +0100, "Bob Phillips"
wrote:

There is a problem with that approach Ron. If the 1st of Jan is say a
Saturday, your formula returns the Monday date. But that will be the New
Year's day holiday, so it should be skipped.

Well, there are places or businesses where that is the case. But not in the
businesses that I've been involved in.

And if so, then the OP should include those holiday dates.
--ron

date after another date
 

But if that were the case with the OP, the first working day in 2010 is 1st
not 4th as in his example.

--
__________________________________
HTH

Bob

"Ron Rosenfeld" wrote in message
...
On Wed, 20 Aug 2008 21:17:19 +0100, "Bob Phillips"
wrote:

There is a problem with that approach Ron. If the 1st of Jan is say a
Saturday, your formula returns the Monday date. But that will be the New
Year's day holiday, so it should be skipped.

Well, there are places or businesses where that is the case. But not in
the
businesses that I've been involved in.

And if so, then the OP should include those holiday dates.
--ron

date after another date
 

On Thu, 21 Aug 2008 09:24:22 +0100, "Bob Phillips"
wrote:

But if that were the case with the OP, the first working day in 2010 is 1st
not 4th as in his example.

No, the 1st is always a holiday, and my formula gives the 4th as a result.

(I did not mean that Jan 1 was not a holiday -- only that the Monday following
was not if Jan 1 occurred on a weekend).

In 2010 Jan 1 is a Friday.

A better example would be to determine the OP's first working day in 2011 (Jan
1 = Saturday) or in 2012 (Jan 1 = Sunday)

--ron

date after another date
 

So you are saying that if the 1st is a Saturday, that is the holiday date,
not the 3rd?

--
__________________________________
HTH

Bob

"Ron Rosenfeld" wrote in message
...
On Thu, 21 Aug 2008 09:24:22 +0100, "Bob Phillips"
wrote:

But if that were the case with the OP, the first working day in 2010 is
1st
not 4th as in his example.

No, the 1st is always a holiday, and my formula gives the 4th as a result.

(I did not mean that Jan 1 was not a holiday -- only that the Monday
following
was not if Jan 1 occurred on a weekend).

In 2010 Jan 1 is a Friday.

A better example would be to determine the OP's first working day in 2011
(Jan
1 = Saturday) or in 2012 (Jan 1 = Sunday)

--ron

date after another date
 

On Thu, 21 Aug 2008 13:39:12 +0100, "Bob Phillips"
wrote:

So you are saying that if the 1st is a Saturday, that is the holiday date,
not the 3rd?

That's what it has been at places where I've worked.

All I'm writing is that it depends on the holiday rules for the place where the
OP works.
--ron

date after another date
 

So you lose a public holiday just because it is a Saturday! Wow! And the US
thinks this is an economic model that everyone else should adopt?

--
__________________________________
HTH

Bob

"Ron Rosenfeld" wrote in message
...
On Thu, 21 Aug 2008 13:39:12 +0100, "Bob Phillips"
wrote:

So you are saying that if the 1st is a Saturday, that is the holiday date,
not the 3rd?

That's what it has been at places where I've worked.

All I'm writing is that it depends on the holiday rules for the place
where the
OP works.
--ron

date after another date
 

On Thu, 21 Aug 2008 19:51:47 +0100, "Bob Phillips"
wrote:

So you lose a public holiday just because it is a Saturday! Wow! And the US
thinks this is an economic model that everyone else should adopt?

--

Different businesses work differently. I'm pretty sure that the banks are
closed; don't know about other types of businesses.

I was in health care and we would not close on the Monday after a weekend New
Years. Although sometimes I felt as if we should :-)
--ron

date after another date
 

I'd love to continue the conversation Ron, there is much I could say and I
am sure that you could also <bg, but this would be way OT, so it is
probably best to close it.

--
__________________________________
HTH

Bob

"Ron Rosenfeld" wrote in message
...
On Thu, 21 Aug 2008 19:51:47 +0100, "Bob Phillips"
wrote:

So you lose a public holiday just because it is a Saturday! Wow! And the
US
thinks this is an economic model that everyone else should adopt?

--

Different businesses work differently. I'm pretty sure that the banks are
closed; don't know about other types of businesses.

I was in health care and we would not close on the Monday after a weekend
New
Years. Although sometimes I felt as if we should :-)
--ron