EDATE function

[mrexcelrc]

Hello, I need help with this function. Is there a way for this function to factor in weekends and named holiday? In other words if that particular target date is a weekend the date will be adjusted to a weekday and if that particular target date is a holiday the date will be adjusted to the next good working day.

Thanks in advance.

 

[JackDanIce]

Try:

=WORKDAY(A1,A2,A3)

Where
A1 = your start date
A2 = number of days
A3 = holiday date*
If you make a range of holiday dates (obtain these by doing a google search, enter into your sheet and then highlight the whole range and name it, passing that name variable into the above function instead of A3), the output will be as required.

E.g.
A1 = 01/07/2011
A2 = 9 days
A3 = 07/07/2011

=WORKDAY(A1,A2,A3)

Will give answer of 15th July 2011

 

[mrexcelrc]

Hi,

In my example below, 9/5/2011 is a holiday so i need the formula to come up with the correct date of 9/6/2011. Also in your example below and in my case, A2 is a variable.




Start date=7/5/2011
EDATE("7/5/2011",2)=9/5/2011

 

[Aladin Akyurek]

Hello, I need help with this function. Is there a way for this function to factor in weekends and named holiday? In other words if that particular target date is a weekend the date will be adjusted to a weekday and if that particular target date is a holiday the date will be adjusted to the next good working day.

Thanks in advance.

Something like:

=WORKDAY(A1,EDATE(A1,1)-A1,Holidays)

where A1 houses a date of interest and Holidays stands for a range housing
the holiday dates. Replace 1 with a cell housing an edate parameter value.

 

[mrexcelrc]

ok will try it. thanks.

 

[markmzz]

Try this:

=WORKDAY(EDATE(A1,B1)-1,1,Holidays)

Markmzz

 

[mrexcelrc]

Hi Mark,
I assume A1 is the start date. What is B1? What is -1, 1?





Try this:

=WORKDAY(EDATE(A1,B1)-1,1,Holidays)

Markmzz

 

[barry houdini]

Try this:

=WORKDAY(EDATE(A1,B1)-1,1,Holidays)

Markmzz

A1 is your start date and B1 the number of months you wish to add. holidays is a named range containing your holiday dates.

If A1 is 7/5/2011 and B1 is 2 as per your example....and if 9/5/2011 is on the holiday list then the formula will return 9/6/2011 as you asked......

 

[Aladin Akyurek]

Hi Mark,
I assume A1 is the start date. What is B1? What is -1, 1?





Try this:

=WORKDAY(EDATE(A1,B1)-1,1,Holidays)

Markmzz

=WORKDAY(EDATE(A1,2)-1,1,Holidays)

means: Create EDATE that is 2 months later than A1 (say X), subtract 1 day from that date (X-1), and calculate WORKDAY using X-1 where the number of days to use is 1, taking Holidays into account.

 

[mrexcelrc]

It worked! Thank you.