Exclude Weekends in formulas involving dates

I don't know of a formula that will do what you are wanting (doesn't mean that is doesn't exist), but I believe this VBA code will do it.

It takes the date in cell A1, adds the number of days entered in cell B1 (excluding weekends), and then puts the end date in cell C1. You can probably easily modify this to fit your needs.

Please Login or Register  to view this content.

rbruza,

I thought a little more about your question and I believe this is more of what you are wanting to do. The function below should do what you are wanting to do. The code needs to go in a regular module in the VBA editor.

On your worksheet you will use projdays(Start Date, Number of Days). Of course this can be a cell references where you have the date and number of days listed on your worksheet.

Please Login or Register  to view this content.

I have not dealt a whole lot with functions, but this appeared to work on my end. Hopefully, it will do what you are wanting it to do. Let me know what happens.

If you have the Analysis Toolpak Addin installed (found under Tools|Addins)...

then this formula should do it:

=WORKDAY(A1,6)

NBVC,

Well, =WORKDAY(A1,6), is definitely the easier route to take.

I did learn a little something about creating functions which I have not used a lot in the past and now I know about the workday function. I have learned two things today so I am blessed.

This forum is definitely a wonderful thing.

Originally Posted by NBVC

If you have the Analysis Toolpak Addin installed (found under Tools|Addins)...

then this formula should do it:

=WORKDAY(A1,6)

I do have the Analysis Toolpack, but I have a question,

I have dates that are generated from a report in my CRM (.csv file). Then we copy this file into an Analyzing Excel file that we have created. One of the objectives of this file is to count how many days have passed between 2 dates. Currently, the formula is as follows:

=IF(ISERROR(DATEDIF($D4,$H4,"D"))," ",(DATEDIF($D4,$H4,"D"))) and then =IF(REPORT!I4=" "," ", IF(REPORT!I4>=3,1,0)) [I have some extra stuff in there to hid the erroneous values]

These formulas are simply counting all days and not taking a 5 day business week into consideration.

Is there a way to edit the existing formula to not include weekends?

Hope you guys can help.

Thanking you in advance!