• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

return the number of custom calendar days that overlap two time periods

R

Rob Webster

New Member
I need to calculate the number of working days that an activity is being worked on in a certain time frame.

eg>

if an activity starts on January 06 2014, and ends on January 29th 2014,

how many working days are there in week commencing Monday 6th, if i only work on Tuesday, Wednesdays and Thursdays

obviously the answer here is 3.

in week commencing 27th January there are 2 working days and in total there are 11 working days spread across 4 weeks

I need to work out many tasks over many weeks to can anyone suggest a formula, or vba code?

I've managed to create code which works out all overlapping days and an alternative that works out overlapping weekdays, but this one is giving me a headache!
 
Hi Rob ,

Can you specify how you mean to incorporate the following into the formula ?

How will you specify the StartDate , EndDate and the days of the week which are working days ?

Other than this , can we take it that :

a. the week will always commence on Monday ,

b. the working days will remain the same throughout the period of interest

c. none of the specified working days will be holidays i.e. if you work only on Tuesdays , Wednesdays and Thursdays , none of these 3 days of the week will ever be designated holidays during the specified period ?

Narayan
 
Hi Narayan,

Thanks for your interest in my problem.

The particular circumstance is that there are different working periods for different resources, but I can handle that by using an if statement in a formula, that uses the.right udf for the particular case. The're is one resource as described above causing me a problem... I'm new to VB which is also a bit of an issue.

In the case of the 3 day week. Working days are always tues-thur incl, they are affected by holidays but they are infrequent and don't change the cost that much anyway so I was ignoring them (at the moment albeit it's on my nice to have list). Working week does always start on a Monday, I'm forcing the working days to remain standard throughout the period, by combining it with other excel functions but really it is not necessarily standard throughout.

the data set I'm using ignores resource availability. I use it to work out weekly cost of many activities, as the outpit's are presented this way too.

Maybe it would cover most issues if I had code that used the holidays theory in networkdays function to extend the days excluded. As ultimately this would with the addition of a range containing those dates give the ultimate freedom. The trouble with that function is that it just looks at one set of dates, and I need to work out the days between two overlapping sets of time periods.

At the moment I use my udf, that utilises vba if and else if to tell the network days what to do, but I can't get the holidays part of the function to work in the code and give me the benefit of working around the Non-working days.

Thanks very much for any advice you can offer.

Regards

Rob
 
hi
thank you but your file give only the total number of days between the start and end date, which is not quite what im looking for.

I've uploaded what I have written myself which works for a 5 day working week. I need additional code to enable me to choose alternative working calendars, particularly Tuesday- Thursday inclusive as per my example, but if this was adjustable it would be amazing.

I am very grateful for your help so far.
 

Attachments

  • activetaskmw.xlsm
    18.9 KB · Views: 4
OMG genius.

i've been messing about for ages, even trying to remove the non working days with excel built in function with very frustrating ends!

thank you so much.

kind regards

Rob
 
@Rob Webster

Hi. I might not have understood you problem. Based on my understanding please find an array formula solution.

In the file attached on Sheet3 you can define your working days. They will get into a dynamic named range.
On sheet 2 you have your solution via formulas.
Kindly, go through the named range, if you found this to be an usable solution.

regards,
 

Attachments

  • ROB_WEBSTER_CHANDOO.xlsm
    24.7 KB · Views: 11
As background information:-

The works Programme deals with lots of digging and lots of filling activities on an earthworks project.

we use different fill materials, some cant be used when its wet and some can but are slower. we may be digging concrete, we may be digging in sand. Bad weather affects the outputs so we tend to demobilise in the wetter months

so the activities have individual outputs, but are also assigned seasonal calendars to allow for periods of slow work or weather/light affected work

1) "the normal calendar" is 5 day working week, 5 days every week, all year

2) "the bulk fill calendar" is a 5 day week between 1st April and 31 October

3) "the 3 day week calendar" is 3 days per week between 1st Nov and 31st March and 5 days for the remainder of the year - note this is not actually 3 days, it is 5 slow days, but the programme can't use different outputs for the same resource. The outline cost is the same but the work done is less. so i use excel, and not the programme.

4) "the 24/7 calendar" 24 hour a day 7 day per week working


I have to report on the quantities and the costs of doing the work both are distinctly different across the calendars so I cant use the programme reports because they are not reporting the costs as accurately as i would like.

so... I'm calculating working days, (my code works for 1, & 2 by combining the code with an if() function, and I use formulas for 4) then when I have them, I'm doing a multitude of other work on top. which makes using the arrays/ or other formulas ok but they are very complex to look at and not as secure. Most of the users of excel (that work with me) have a tendency to see a formula in a cell and break it, trying to find out what it does, where as they have difficulty even finding the code, (even if they do look at it they leave it alone.)

and if I revisit it in 6 to 9 months, I start with trying to understand what I wrote!

so.. I'm trying to create some functions that remove the need for my more complex but regular tasks. to avoid repeated combination of the many excel functions, mainly because once the VBA is done, its easy to import between workbooks, and more difficult to break by the more inexperienced users. I tend to have to write the calculations for use by others, albeit that is not my (Paid) day job!!

Apologies for this rather long explanation, I am grateful for your input.

regards

Rob
 
You must log in or register to reply here.
Back
Top