date after another date

[Gator Girl]

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.

 

[Bob Phillips]

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)

 

[Ron Rosenfeld]

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

 

[Bob Phillips]

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.

 

[Ron Rosenfeld]

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

 

[Bob Phillips]

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

 

[Ron Rosenfeld]

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

 

[Bob Phillips]

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

 

[Ron Rosenfeld]

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

 

[Bob Phillips]

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?

 

[Ron Rosenfeld]

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

 

[Bob Phillips]

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.