Date plus month, year, or years in workdays
 

I've been trying to come up with a formula that will give me the date entered
(A2) plus the entry in B2, but also end up on a weekday.

A2 = User entered date
B2 = Data Validation List: 2 WK, 1 MO, 3 MO, 6 MO 1 YR, 2 YR, 3 YR, 4 YR

Example, A2=Jul 18 2009 (Saturday); if "2 WK" is chosen, the date will
should be Jul 31 2009 (Friday); if "1 YR" is chosen, the date should be Jul
16 2010 (Friday); etc., etc.

Suzanne

Date plus month, year, or years in workdays
 

I would recommend having a simple function to get the new date, for example,
2 weeks from now would be =today()+14, and then wrap the result of that
inside a WEEKDAY function to find out if it's a Saturday or Sunday. If it's
a Saturday, do the date minus 1 to arrive at Friday, and if it's a Sunday do
the date minus 2 (or plus 1 if you want to arrive at a Monday).

For month you can use EDATE, and I think you could also use that for 1 year,
2 years, etc.

-EddieO

"Suzanne" wrote:

I've been trying to come up with a formula that will give me the date entered
(A2) plus the entry in B2, but also end up on a weekday.

A2 = User entered date
B2 = Data Validation List: 2 WK, 1 MO, 3 MO, 6 MO 1 YR, 2 YR, 3 YR, 4 YR

Example, A2=Jul 18 2009 (Saturday); if "2 WK" is chosen, the date will
should be Jul 31 2009 (Friday); if "1 YR" is chosen, the date should be Jul
16 2010 (Friday); etc., etc.

Suzanne

Date plus month, year, or years in workdays
 

This is what I'm trying to work with

=IF(B2="2 WEEK",WORKDAY(A2,10),IF(B2="1
MONTH",DATE(YEAR(A2),MONTH(A2)+1,WEEKDAY(A2)),IF(B 2="1
YEAR",DATE(YEAR(A2)+1,MONTH(A2),WEEKDAY(A2)))))

The survey date = 14 Jul 09 (Tuesday)
The resulting date (+ 1 month) = 3 Aug 09 (why not 14 Aug?)
The resulting date (+ 1 year) = 3 Jul 10 (Saturday; why not 14 Jul 10?)

If "WEEKDAY" is changed to "DAY", the resulting dates are 14 Aug 09 (1
month), 14 Jul 10 (1 year); however, I want to make sure the resulting dates
are on a weekday.

Suz


"EddieO" wrote:

I would recommend having a simple function to get the new date, for example,
2 weeks from now would be =today()+14, and then wrap the result of that
inside a WEEKDAY function to find out if it's a Saturday or Sunday. If it's
a Saturday, do the date minus 1 to arrive at Friday, and if it's a Sunday do
the date minus 2 (or plus 1 if you want to arrive at a Monday).

For month you can use EDATE, and I think you could also use that for 1 year,
2 years, etc.

-EddieO

"Suzanne" wrote:

I've been trying to come up with a formula that will give me the date entered
(A2) plus the entry in B2, but also end up on a weekday.

A2 = User entered date
B2 = Data Validation List: 2 WK, 1 MO, 3 MO, 6 MO 1 YR, 2 YR, 3 YR, 4 YR

Example, A2=Jul 18 2009 (Saturday); if "2 WK" is chosen, the date will
should be Jul 31 2009 (Friday); if "1 YR" is chosen, the date should be Jul
16 2010 (Friday); etc., etc.

Suzanne

Date plus month, year, or years in workdays
 

On Mon, 13 Jul 2009 10:35:01 -0700, Suzanne
wrote:

I've been trying to come up with a formula that will give me the date entered
(A2) plus the entry in B2, but also end up on a weekday.

A2 = User entered date
B2 = Data Validation List: 2 WK, 1 MO, 3 MO, 6 MO 1 YR, 2 YR, 3 YR, 4 YR

Example, A2=Jul 18 2009 (Saturday); if "2 WK" is chosen, the date will
should be Jul 31 2009 (Friday); if "1 YR" is chosen, the date should be Jul
16 2010 (Friday); etc., etc.

Suzanne

Given your validation List, then:

=WORKDAY(IF(RIGHT(B2,2)="WK",DATE(YEAR(A2),MONTH(A 2),DAY(A2)+LEFT(B2)*7),
IF(RIGHT(B2,2)="MO",DATE(YEAR(A2),MONTH(A2)+LEFT(B 2),DAY(A2)),
DATE(YEAR(A2)+LEFT(B2),MONTH(A2),DAY(A2))))+1,-1)

If this returns a #NAME! error, and you have an earlier version of Excel than
2007, you will need to install the Analysis ToolPak. See HELP for the WORKDAY
function to learn how to do this.
--ron

Date plus month, year, or years in workdays
 

SUPERB!!

BTW... I have been pouring over help on the workday function, in addition to
many Excel sites. None that I encountered had this type of problem/solution.

Thanks VERY much!!

"Ron Rosenfeld" wrote:

On Mon, 13 Jul 2009 10:35:01 -0700, Suzanne
wrote:

I've been trying to come up with a formula that will give me the date entered
(A2) plus the entry in B2, but also end up on a weekday.

A2 = User entered date
B2 = Data Validation List: 2 WK, 1 MO, 3 MO, 6 MO 1 YR, 2 YR, 3 YR, 4 YR

Example, A2=Jul 18 2009 (Saturday); if "2 WK" is chosen, the date will
should be Jul 31 2009 (Friday); if "1 YR" is chosen, the date should be Jul
16 2010 (Friday); etc., etc.

Suzanne

Given your validation List, then:

=WORKDAY(IF(RIGHT(B2,2)="WK",DATE(YEAR(A2),MONTH(A 2),DAY(A2)+LEFT(B2)*7),
IF(RIGHT(B2,2)="MO",DATE(YEAR(A2),MONTH(A2)+LEFT(B 2),DAY(A2)),
DATE(YEAR(A2)+LEFT(B2),MONTH(A2),DAY(A2))))+1,-1)

If this returns a #NAME! error, and you have an earlier version of Excel than
2007, you will need to install the Analysis ToolPak. See HELP for the WORKDAY
function to learn how to do this.
--ron

Date plus month, year, or years in workdays
 

On Mon, 13 Jul 2009 11:58:01 -0700, Suzanne
wrote:

SUPERB!!

BTW... I have been pouring over help on the workday function, in addition to
many Excel sites. None that I encountered had this type of problem/solution.

Thanks VERY much!!

You're welcome. Glad to help. Thanks for the feedback.
--ron