business days - trade date plus 3

[clegge]

is there a calendar function in excel that will allow me to add 3 days
to a *yyyymmdd * formatted date and skip weekends... Basically want to
derive a settlement date, which is always trade date +3.. i am given
the trade date... we only count buisness days... weekends are not
include - i dont care about holidays (can fix those manually)

thanks!!!!

 

[Bob Phillips]

Yes,

=WORKDAY(date,3)

This is part of the Analysis Toolpak add-in, so that needs to be installed
(check it in Tools>Addins)

You don't need to fix holidays manually, if you create a named list, you can
include that as a further parameter in the formula.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Roger Govier]

Hi

Try
=WORKDAY(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),3)
If you want to include holidays as well, then create a range of cells
containing the holiday dates (as true Excel type dates e.g. 12/25/2006)
and include that in the formula as
=WORKDAY(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),3,$H$1:$H$10) where
H1:H10 is where you have entered your holidays.

If your existing dates are true Excel dates, but just formatted to
display as yyyymmdd, then you don't need the conversion to date
=WORKDAY(A1,3,$H$1:$H$10)