Week Date Function

Hello,

I would like help on calculating business date after deducting a number from a date.

For example, if I have the date as 04/02/2008 in cell A1 and number 38 in cell A2, I want to calculate the difference between A1 and A2 and write that difference in cell A3 as the date (formatting the cell as date). However, I want to make sure that this value returned is a weekday. If it is not, then it should automatically go to the previous week day.

In the example mentioned above, the value falls on Sunday and I would like this value to automatically roll over to previous Friday.

Week Day = All days excluding Saturday and Sunday. Also, if Holiday can be excluded, it will be great.

Please suggest me the formula I can apply. Thanks a bunch for the help. I appreciate it in advance.

Do you mean you want the date 38 days before the date in A1? If so try

=WORKDAY(A1-A2+1,-1)

Note: WORKDAY is an Analysis ToolPak function. If you don't have ATP installed then use Tools > Add-ins > Tick Analysis ToolPak

You can exclude holidays too. List your holiday dates, name the range holidays and use

=WORKDAY(A1-A2+1,-1,holidays)