Excel date formula

[DanWo]

I would like to calculate :
End Date (B1)
from a Start Date (A1)
+ Duration (C1) in Working days (without WE & holidays)
BUT
if cell D1=1 ,Saterdays are working days too.

WORKDAY function consider only monday to Friday as working days!
How to do it?

 

[jdhilljr]

DanWo,

There's no set way to do this that I know. Here is a way to set up a lookup to give you the results. I have basically taken a listing of dates and removed the Sundays. Then in the next cell I numbered them using auto fill. Different companies observe different holidays so you want to go through and remove the dates your company observes. Then sort column "A". Then number column "B" sequentially. After you can either move or hide those cells. The VLOOKUP will do the rest. Here's a sample

http://www.procollectortips.com/DATES.xls

Jim

 

[Zathras]

This formula should give you the number of Saturdays between two dates:
[blue]

  =INT((A2-$A$1)/7)+(WEEKDAY($A$1)>MOD(WEEKDAY(A2),7))

[/color]
It should be a simple matter to incorporate this into your current formula.

 

[jdhilljr]

He wants to include Saturdays. But remove Sundays and Holidays.

Jim

 

[Zathras]

jdhilljr: You have a point. I missed that the requirement was to calculate the End date given the Start date and number of workdays. I was attacking it from the perspective of simply calculating workdays between two dates.