Omiting Weekend/Holidays Dates Problem

[Don Lee]

I am having trouble creating a function that overlook
weekends/holidays.


I do a lot of trading in the stock market and when you sell stock, i
usually takes three days for the trade to "settle" (this gives broker
time to correct any mistakes that may have been made during
transaction). So if I sell on a Monday, the trade will settle in 7
hours and I can have the money wired to my account on Thursday. If
sell on Monday or Tuesday I can use a formula like

=DATE(YEAR($A$2),MONTH($A$2),DAY($A$2)+3) (where A2 is the sellin
date)

to get my settling date. However, weekends/holidays are not counte
toward the settle date (business days only), so if I sell on
Wednesday, Thursday, or Friday the settle date is the following week.


eg-> If I sell on Friday the settle date would be Wednesday.

This means if I use the above formula I will get incorrect settle date
(false settle dates that occur on weekends).

Can anyone help me devise a formula that recognizes weekends/holiday
to accurately show the settle date? I want to be able to enter th
sell date and have Excel calculate the settle date for me.

Any help is most appreciated.

-Do

 

[Ron Rosenfeld]

Can anyone help me devise a formula that recognizes weekends/holidays
to accurately show the settle date? I want to be able to enter the
sell date and have Excel calculate the settle date for me.

Any help is most appreciated.

=WORKDAY(Trade_Date,3,Holiday_List)

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.


On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.


--ron

 

[Frank Kabel]

Hi
try
=WORKDAY(A2,3,<list_of_holiday>)

Note: you must have the Analysis Toolpak Add-in installed for this

 

[Don Lee]

Thanks for your help guys. Worked.


I have one more question on this thread if anyone has an answer. No
that I have the weekends/holidays gone is there a way to leave th
settle date blank until I put a sell date in?

Right now all settle date cells are returning 1/4/1900 which I'
guessing is 3 days after the first date Excel will allow (1/1/1900).
Is there away to prevent the formula from returning this date on
blank cell? I just want the settle date calculated when I enter a dat
into the corresponding sell date cell.

I am sorry if this seems confusing. If there are new viewers you nee
to look at the previous posts.

-Do

 

[Dave Peterson]

=WORKDAY(A2,3,<list_of_holiday>)
becomes:
=if(a2="","",WORKDAY(A2,3,<list_of_holiday>))

 

[Don Lee]

Thanks for your help guys. Worked.


I have one more question on this thread if anyone has an answer. No
that I have the weekends/holidays gone is there a way to leave th
settle date blank until I put a sell date in?

Right now all settle date cells are returning 1/4/1900 which I'
guessing is 3 days after the first date Excel will allow (1/1/1900).
Is there away to prevent the formula from returning this date on
blank cell? I just want the settle date calculated when I enter a dat
into the corresponding sell date cell.

I am sorry if this seems confusing. If there are new viewers you nee
to look at the previous posts.

-Do

 

[Ron Rosenfeld]

Thanks for your help guys. Worked.


I have one more question on this thread if anyone has an answer. Now
that I have the weekends/holidays gone is there a way to leave the
settle date blank until I put a sell date in?

Right now all settle date cells are returning 1/4/1900 which I'm
guessing is 3 days after the first date Excel will allow (1/1/1900).
Is there away to prevent the formula from returning this date on a
blank cell? I just want the settle date calculated when I enter a date
into the corresponding sell date cell.

I am sorry if this seems confusing. If there are new viewers you need
to look at the previous posts.

-Don

=IF(TradeDate="","",WORKDAY(Trade_Date,3,Holiday_List))


--ron

 

[Dave Peterson]

You got a reply last Friday:

http://groups.google.com/[email protected]