Excel Formula to Calculate Leadtime minus Weekend

**Nu2Java** · 05-24-2012, 11:25 AM

Hello,

I am really stuck on a formula to calculate leadtime for jobs in my shop. The formula I have been using seems to work ok but when the job takes less than 1 day it gives me negative numbers. Any help would be greatly appreciated.

Here is what I use now:

Please Login or Register  to view this content.

**Ron Coderre** · 05-24-2012, 11:34 AM

I can't quite figure out what you're working with so here's a mock-up
Col_A contains job references
Col_B contains Due Dates
Col_C contains number of effort days to complete the work
and
J1:J10 contains a list of holiday dates.

This regular formula returns the start date that will result in the work being completed on the due date, taking holidays into consideration:
D2: =WORKDAY(B2,-C2+1,$J$1:$J$10)

Example:
Job: Alpha0123
DueDate: 16-Jan-2012
EffortDays: 7
StartDate: 05-Jan-2012 (I had Jan 10th as a randomly placed holiday)

Is that something you can work with?

**Nu2Java** · 05-24-2012, 12:35 PM

Hi Ron,

Thanks for the reply, I am sorry for the lack of information.... Yes, you are on the right track with what I need. I just have a start date and finish date to calculate the total leadtime. The formula I have been using takes out 2 days if the weekend happens to fall in between. The biggest problem I have is that if the job finishes on the same day it started, I get a negative number returned. For example I have one that finished in 0.19 days but the leadtime shows -1.81 so it seems to be accounting for the subtraction of 2 days if it is less than 1.

**Ron Coderre** · 05-24-2012, 12:58 PM

If you can post some representative sample data, we'll see what we can do to help.

**Nu2Java** · 05-29-2012, 09:48 PM

Hi Ron,

Sorry for the delay, I have been gone for an extended Holiday weekend. I think I figured out what had happened to the code I was using. In my line of code, I needed to take out the "=" in ">=WEEKDAY". Thanks very much for your time, greatly appreciated.