Date formula

[promed1]

Hello,

I am trying to write a date formula to automatically update the date on a worksheet to the previous business day. I have accomplished that much with the formula I came up with which is "=TODAY()-IF(WEEKDAY(TODAY())=2,3,1)"; however, I need to exclude holidays (1/1/10, 5/31/10, 7/4/10, 9/6/10, 11/25/10 and 12/25/10) from this formula and I have had a difficult time in writing the formula to do that....

Can you anyone help with how I should write this formula?

I need the formula to automatically update to the previous business day and exclude holidays... HELP!!

Any assistance is greatly appreciated!!!!!

[shg]

Welcome to the forum.

=WORKDAY(TODAY(), -1, A1:A10)

... where A1:A10 contains a list of holidays as Excel dates.

[promed1]

Looks like this is working Thank you so much for your help

[promed1]

Ok. I am still having a problem with this formula. I am using a shared worksheet that is saved on a network drive at work. When my co-worker opens the file, the date field is showing as "?NAME" instead of auto filling the previous business day. I even went to her PC and found that the formula is there but it does not show the date. Can you help??

[shg]

Tools > Add-ins > Analysis Toolpak

[promed1]

I checked this and I have it selected as one of the add-ins...

[shg]

Your computer isn't the problem.

[promed1]

ok so I need to verify that anyone that views this file has this add-in selected? Is that correct?

[shg]

Everyone using a version of Excel pre 2007, yes.

[promed1]

That solved the problem... we use excel 2003. Thanks for your patience and help with this!!