Caculated column formula for Workdays between two dates, Excluding weekends using DateDiff

• chamdan1,

By not wanting to include weekends it sounds like you simply want the number of working days. There is a lot simpler formula for that, namely;

ProjDateDiff([Start],[Finish])/[Minutes Per Day]

Is there a reason that won't work?

John

• Marked as answer by Friday, September 26, 2014 8:05 PM
Friday, September 26, 2014 7:42 PM
• Chuck,

First of all, you're welcome and thanks for the feedback.

The AND logical operator is alive and well but as used in the formula you show, the context is wrong for the IIF statement. But then Project also doesn't have a field called Start Date or End Date, it's simply Start and Finish (or Scheduled Start and Scheduled Finish). And the FLOOR function isn't available for Project either, so I think the whole formula was destined for failure from the get go.

In this case simplicity rules but in reality, the normal Duration field already has the number of working days from task start to task finish, so why even have the formula?

John

Friday, September 26, 2014 10:05 PM

All replies

• chamdan1,

By not wanting to include weekends it sounds like you simply want the number of working days. There is a lot simpler formula for that, namely;

ProjDateDiff([Start],[Finish])/[Minutes Per Day]

Is there a reason that won't work?

John

• Marked as answer by Friday, September 26, 2014 8:05 PM
Friday, September 26, 2014 7:42 PM
• Hi John,

Thanks it worked! I had made a mistake when selecting the Custom filed as Date1 instead of Number1.

Cheers

Chuck

Friday, September 26, 2014 8:05 PM
• John,

I forgot to show you the error I was getting earlier with the other formula.

Friday, September 26, 2014 8:35 PM
• John,

I guess the function does not work with MSP as it is not listed. Is this correct?

Regards,

Chuck

Friday, September 26, 2014 8:40 PM
• Chuck,

First of all, you're welcome and thanks for the feedback.

The AND logical operator is alive and well but as used in the formula you show, the context is wrong for the IIF statement. But then Project also doesn't have a field called Start Date or End Date, it's simply Start and Finish (or Scheduled Start and Scheduled Finish). And the FLOOR function isn't available for Project either, so I think the whole formula was destined for failure from the get go.

In this case simplicity rules but in reality, the normal Duration field already has the number of working days from task start to task finish, so why even have the formula?

John

Friday, September 26, 2014 10:05 PM
• You're totally right, WoW! I did not pay attention to the "Start Date" and "End Date".

I am aware that those two do not exist but did not pay attention to it.

Thanks John!

Cheers!

Chuck

Saturday, September 27, 2014 2:43 AM