Solved

VBA Calculate end date when start date and 6 day work weeks are used

Posted on 2016-11-08
8
24 Views
Last Modified: 2016-11-09
I'm having trouble with this one. I'm sure it's simple for the right person but I've been on it for about three hours now. I've been trying to extrapolate by using NetworkDays.INTL but I haven't had any luck. I would prefer to run this completely in VBA  if possible.

I have two variables:

Start Date

Work Days (Qty)

What I need to determine is the end date when the labor days are added to the start date and Saturdays are not included.

So for example, if the job started on Nov 14 th and included 14 work days then the total chronological days would be 16 as per the attached Jpeg.

Thank you for looking at this


"Work" days vs chronological days
0
Comment
Question by:Member_2_7966101
  • 4
  • 3
8 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41879848
you can have function NETWORKDAYS.INTL to get the total working days within a period.
=NETWORKDAYS.INTL(yourStartDate,yourEndDate,11)

Open in new window


NETWORKDAYS.INTL function
https://support.office.com/en-ie/article/NETWORKDAYS-INTL-function-a9b26239-4f20-46a1-9ab8-4e925bfd5e28?ui=en-US&rs=en-IE&ad=IE
0
 

Author Comment

by:Member_2_7966101
ID: 41879865
That's the whole problem, I don't know the end date. That's what I'm trying to determine. I just have the start date and "work" days which don't include Saturdays.
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41879900
so the idea is to generate a list of chronological days or you just want to know the total chronological days?
0
 

Author Comment

by:Member_2_7966101
ID: 41879922
the quantity of chronological days is what i'm after
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 49

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41879937
the quantity of chronological days is what i'm after

sorry can't think for a solution other than using a macro.

you probably can try this:
Function getChronologicalDays(StartDate As Date, WorkDays As Integer)
    cnt = 0
    v = 0
    Do While cnt < WorkDays
        If Weekday(StartDate + v) <> 7 Then
            cnt = cnt + 1
        End If
        v = v + 1
    Loop
    getChronologicalDays = v
End Function

Open in new window

then use like:
=getChronologicalDays(StartDate, WorkDays)
28981928.xlsm
0
 

Author Comment

by:Member_2_7966101
ID: 41880414
Thanks for that, Ryan. I needed to get this into production this morning. much appreciated
0
 

Author Closing Comment

by:Member_2_7966101
ID: 41880415
Works exaclly as I needed it to. Thanks again
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41880608
With formula:

=B6+INT(C6/6)*7+MOD(C6,6)

Where B6 ist start date and C6 is number of working days.  INT(C6/6) gives number of whole working weeks times by 7 gives number of calendar days. MOD(C6,6) gives number of days after whole weeks.

With check if result is Saturday, if so add 1 more day:
=IF(WEEKDAY(B6+INT(C6/6)*7+MOD(C6,6),1)<7,B6+INT(C6/6)*7+MOD(C6,6),B6+INT(C6/6)*7+MOD(C6,6)+1)
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now