Non-holiday workday nearest to a given date

[Texas Longhorn]

Hi all,

I'm trying to calculate one year stock returns for various stock symbols in a spreadsheet that updates generally daily. My company has a vendor that supplies an add-in that allows us to pull historical stock prices into excel by using a function that references a date in a cell. The problem is, if the reference date is a date on which the market was closed (i.e. weekend or US holiday), then I receive an error. Currently, my historical date is found via =TODAY()-364.

Is there a way to have Excel return the non-holiday/non-weekend day closest to TODAY()-364?

Thanks for any help,
Bill

 

[barry houdini]

Hello Texas Longhorn.

Do you really want the closest date, i.e. it could be earlier or later than TODAY()-364?

If you wanted the first workday BEFORE (when TODAY()-364 is not a workday) you could try using the WORKDAY function from Analysis ToolPak add-in, i.e.

=WORKDAY(TODAY()-363,-1,holidays)

where holidays is a named range containing a list of your holiday dates

 

[Texas Longhorn]

Perfect! Thanks Barry...that's exactly what I needed.