Date plus month, year, or years in workdays

[Suzanne]

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

 

[EddieO]

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]

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(B2="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

 

[Ron Rosenfeld]

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(A2),DAY(A2)+LEFT(B2)*7),
IF(RIGHT(B2,2)="MO",DATE(YEAR(A2),MONTH(A2)+LEFT(B2),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

 

[Suzanne]

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]

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