Excel (2007) "workday" how to add days to a date and include saturday, for a schedule.

[drg7001]

Hello All,

I am new to Excel, and working on a project timeline.

I am trying to insert a number of days in column "b" for the amount of time each job will take.

In column "c" I have the start date, and in column "d" i have the end date.

I would like it to work so that when I enter the days to complete and the Start Date entered, it will automatically calculate the End Date (using working days) and include Saturdays. I do not need holidays included.

For example: B7: Duration: 22 days
C7: Start date: 2/15/2016
D7: End Date: 3/10/2016 - (I have tried numerous different macros with no success, I usually come up with 3/08/2016 or 3/16/2016).


The problem with most macros are that they used "networkdays" and needed a start and end date, I do not want to use a start and end date however, I need the macro to be placed in the end date column, so that when I change my duration, it automatically calculates the end date.

I have also tried using workday2 from here Better Workday but it would not work for me.

Thank you for your time.

 

[eggyjla]

Just to be clear, You are looking for a VBA solution? There are two other recent posts which are very similar to your issue. Have you reviewed these: http://www.mrexcel.com/forum/excel-questions/906060-networkdays-manipulation.html and http://www.mrexcel.com/forum/excel-questions/670652-challange-all-experts.html. Both were looking for solutions using xL2007.

 

[drg7001]

I am looking for any solution I can get to work at this point. I will check them out right now, to be sure. Thank you!

 

[drg7001]

I tried the following string in D7 (end date): =CHOOSE(WEEKDAY(C7,2),1,1,1,1,1,1,1,0)+C7+B7

Again, C7 is the "start date" and B7 is the "duration" of the work being done.

It is the closest I have gotten (This is from your link). http://www.mrexcel.com/forum/excel-questions/670652-challange-all-experts.html

Still, It is not computing correctly though.

It is giving me a date of 3/09/2016 instead of 3/10/2016.

 

[Jonmo1]

Which version of Excel are you using?
XL2010+, try
=WORKDAY.INTL(C7-1,B7,11)

 

[drg7001]

XL2007... I am trying to get approved for the 2010 version atm.

Thank you, if I get the download approval, I will try that!

 

[Jonmo1]

Ok, this isn't pretty, but I think it works..
Put this code in a new standard module

Public Function CountSaturday(strt As Date, cnt As Long)
Dim MyDate As Date, MyDates() As Date, x As Long
MyDate = strt
x = 0
Do Until x = cnt
    If Weekday(MyDate) = vbSunday Then
    Else
        ReDim Preserve MyDates(0 To x)
        MyDates(x) = MyDate
        x = x + 1
    End If
    MyDate = MyDate + 1
Loop
CountSaturday = MyDates(UBound(MyDates))
End Function

And use it like
=CountSaturday(C7,B7)

C7 is start Date, B7 is duration.

 

[drg7001]

Okay, I tried that, but it came back as an error: #NAME?

This is exactly what I did, just in case I did something wrong.

I made a new sheet.
Then I right clicked, and clicked the "View Code" selection
Then pasted in the script and hit Alt, Q
Then saved.
I then, copied and pasted =CountSaturday(C7,B7) into D7 (FINISH DATE)

 

[Jonmo1]

The code needs to go in a NEW Standard module.
Add a step to your process

Then I right clicked, and clicked the "View Code" selection
Click Insert - Module
Then pasted in the script and hit Alt, Q

 

[drg7001]

Okay, I did exactly as you said and then entered the macro. At first it came up as #NAME?

Then after left clicking D7 again, it came up as 1/0/1900 for the finish date