Adding days to a date, but excluding weekends & pub holidays

[Zabman]

Hi,

I am trying to develop a formula that adds 3 working days to a formula if the date stamp is after 10am, and 2 days if it is before. The cell containing the time I want to modify is E2. I have the following, but cannot work out how to rule out the weekends! I am sure networkdays is the way to go due to the public holiday factor, but just cant get my head around it!

=IF(MOD(E2,1)>0.41667,INT(E2) + 3,INT(E2) + 2)

 

[T. Valko]

Hi,

I am trying to develop a formula that adds 3 working days to a formula if the date stamp is after 10am, and 2 days if it is before. The cell containing the time I want to modify is E2. I have the following, but cannot work out how to rule out the weekends! I am sure networkdays is the way to go due to the public holiday factor, but just cant get my head around it!

=IF(MOD(E2,1)>0.41667,INT(E2) + 3,INT(E2) + 2)

Use the WOKDAY function...

=WORKDAY(E2,IF(HOUR(E2)>10,3,2),A1:A10)

Where A1:A10 is the list of holiday dates.

Note that the WORKDAY function requires the Analysis ToolPak add-in be installed if you're using a version of Excel prior to Excel 2007. If you enter the formula and get a #NAME? error look in Excel help for the WORKDAY function. It'll tell you how to fix the problem.

 

[Zabman]

Thanks for that. I had looked at that function, but ruled it out due to the public holiday thing, its quite important that holidays are excluded the same as weekends. Am I missing something with the weekday function?

 

[T. Valko]

Thanks for that. I had looked at that function, but ruled it out due to the public holiday thing, its quite important that holidays are excluded the same as weekends. Am I missing something with the weekday function?

WORKDAY does have a holidays argument.

I think you're confusing the WEEKDAY function with the WORKDAY function.