Projected Working Days_Hours

Hello Team,

We need to calculate projected working days, based on today to end date we have in our data sheet.
Please see attached sheet for data and we are looking results like "Projected Working Days" tab.

Working days to be calculated excluding sat/sun and holidays we mentioned in holiday tab.

thanks for your help in advance.

Using mcmahobt's file and formulae:

=NETWORKDAYS(INDEX(Companies!$E$2:$E$3,MATCH(C2,Companies!$A$2:$A$3,0)),INDEX(Companies!$F$2:$F$3,MATCH(C2,Companies!$A$2:$A$3,0)),INDIRECT(D2&"_HOL"))

Using Named Ranges for the holidays:

Simpler than this:

=NETWORKDAYS(INDEX(Companies!$E$2:$E$3,MATCH(C3,Companies!$A$2:$A$3,0)),INDEX(Companies!$F$2:$F$3,MATCH(C3,Companies!$A$2:$A$3,0)),IF(Holidays!$A$2:$A$7='Projected Working Days'!D3,Holidays!$B$2:$B$7,0))

Hello mcmahobt and John, Thank you for your response.

I need projected days to be calculated monthly,
for e.g.
Month Days
June 2017 22 (excluding sat/sun and holidays specified in holidays tab)
July 2017 20 (excluding sat/sun and holidays specified in holidays tab)

I have already attached this format in my initial file.

OR

Simply change the Start and End date in the current formula:

NETWORKDAYS(Start_Date,End_date),IF(Holidays!$A$2:$A$7='Projected Working Days'!D2,Holidays!$B$2:$B$7,0))

You know the Start date and should be able to calculate the month-end date.

Yes, John. this function looks very simple and easy to apply.

=NETWORKDAYS.INTL (start_date, end_date, [weekend], [holidays])

but the challenges I am having here is to get the values for each month, based on startdate and enddate. As of now, I am new to excel formulas and can only apply basic formulas with simple calculations.

The Start date is the date in H2 and the end date is EOMONTH(H2,0)

apply to this ,,,

NETWORKDAYS(Start_Date,End_date),IF(Holidays!$A$2:$A$7='Projected Working Days'!D2,Holidays!$B$2:$B$7,0))

Hey John,
Thanks for your inputs again.

I looking something similar to the solution you provided for Post "Calculate Working Hours Monthly", that worked perfectly and was exactly we were looking.

So, I'm looking like to calculate Projected working hours for a member from next month (June) to end date (December), if in case members end date is increased to January 2018 (that month should be added to Projected working days).

I have attached sample data sheet and expected results (Projected Working Days tab).

I tried this, =NETWORKDAYS($H2,EOMONTH($H2,0)),IF(Holidays!$A$2:$A$7='Projected Working Days'!D2,Holidays!$B$2:$B$7,0)
and it's giving me an error.

1.JPG

Use the formula I posted in the attachment in post #10.

Hi,

See the attached file.

I could accomplish this by adding few helper columns and a lengthy formula!

Formula:

Please Login or Register  to view this content.

I have elaborated the formula to calculate the number of working days even if the Start Date & End Dates are NOT the End/Beginning of the months.

See attached:

=NETWORKDAYS(H2,EOMONTH(H2,0),INDIRECT(D2&"_HOL"))

Thanks for all your efforts and help Team..!!!

John, I was looking something solution you provided in post "Calculate Working Hours Monthly", where I was just adding a month and it's entry was getting added to Results tab (please see attached "Sample Time Tracker JT (1)" sheet ) so I can say it was much automated process which we feel most relevant to us and also liked most.

So, here I'm looking something similar automated (if we can say) Projected days/hours to be calculated. Let say if any resource is added to the Companies tab with startdate/enddate his/her projected hours should be calculated based on next month (of current month) and enddate. Please see attached sample file again.

Thanks again for all your efforts.

You have all the formula you need: simple apply the required start and end dates.

I have no idea what you want in "Projected Working days" other than that which has already been provided.

The "Sample time Tracker" file is OLD and I have provided various formulae for this file to address your requirements.

if you add a resource in companies tab that should be added in projected working days tab automatically with projected working days monthly.

You will need VBA to do this: in particular if you want a change to "Companies" (Add OR Delete) to automatically generate a new "Projected Working Days".

I take it the first month is the Current month +1 i.e. addition in June will mean the first month is July ? [rather then based on Employee Start Date].

Attached is VBA routine to create "Projected Work days".

The Start month is current month+1 i.e. as we are in May, start month is June. [Previous data is lost].

End month (for purposes of testing) is hard-coded (Highlighted) in Red.

and data in "Companies" is incomplete.

Currently macro invoked with RUN button but could be invoked by any change to "Companies" data

Please Login or Register  to view this content.

thank you John,

I'm trying to do this with formula, I tried to generate 7 rows (current month to enddate (december)) for first resource in Projected Working Days tab, but gives an error, please see attached sheet.

formula is: =INDEX(Companies!$A$2:$A$999,INT((ROWS($1:1)-1)/(YEAR(Companies!$F2)-YEAR(EDATE(TODAY(),1)))*12+MONTH(Companies!$F2)-MONTH(EDATE(TODAY(),1))))

YEAR(Companies!$F2)-YEAR(EDATE(TODAY(),1)))*12+MONTH(Companies!$F2)-MONTH(EDATE(TODAY(),1))) -- Gives me 7

plz let me know whats wrong here.

hey John, appreciate your help again.

But, I'm not clear with the columns K and L, both these are generating months and why we need to two column with months value.
Also, about the count of working days for each month, where are we getting that, I mean in which column.

They are there simply to show the options available: just choose one.

I expected you to work out that the formulae in J,K,L need to transferred to the appropriate column in the table in A:H. You could then use INDEX/MATCH to fill in the missing data from "Companies".

You already have various formulae for working days.

Great!

I was able to apply some formulas, now I have got two questions...

1. In "Projected Working Days" tab cell F5, it's not showing correct value, not sure why.
2. I was able to calculate working days based on this
=NETWORKDAYS($H2,EOMONTH($H2,0),IF(Holidays!$A$2:$A$7='Projected Working Days'!D2,Holidays!$B$2:$B$7,0)) -- this works well till row 6

but then I tried below and got expected results.
=NETWORKDAYS($H8,EOMONTH($H8,0),IF(Holidays!$A$2:$A$999='Projected Working Days'!D8,Holidays!$B$2:$B$999,0))

So here, why do we need to give range till $A$999 or $B$999, even when we have values only till row 7 in holidays tab ?
please see attached file.

Great!, thanks for your help John and that works perfectly for me.

I need to do a small correction in formula, I need to calculate projected hours for current month too.
Initially I said, need to calculate from next month. So, just want to correct that.

I need to calculate projected hours for current month too.

All of the month (of May) or remainder?

Either way you should be able to amend the current formulae (as the "this month version" has been posted previously I am sure).

Thank you, John. I'll check that.
I'm trying to add one column in the data now, where if effective is greater than current month that should be added to tab called "SP"

I tried this formula, but I don't see the expected results, Please see attached sheet too.

"IF(EOMONTH(TODAY(),INT((ROWS($1:3)-1)/COUNTA(Companies!$A$2:$A$2999)))< INDEX(Companies!$E$2:$E$2999,MOD((ROWS($1:4)-1),COUNTA(Companies!$A$2:$A$2999))+1),INDEX(Companies!$E$2:$E$2999,MOD((ROWS($1:3)-1),COUNTA(Companies!$A$2:$A$2999))+1))"

I'm trying to get records only if effective date is greater than current month.

In A2

=IFERROR(INDEX(Companies!A$2:A$2999,SMALL(IF(Companies!$E$2:$E$2999>EOMONTH(TODAY(),0),ROW(Companies!$E$2:$E$2999)-ROW($E$2)+1,""),ROWS(A$2:$E2))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Copy across

Hey John, Just to update on my previous note.
Adding current month record too.

I tried with this formula,
=IF(EOMONTH(TODAY(),INT((ROWS($1:1)-1)/COUNTA(Companies!$A$2:$A$999)))+1<=INDEX(Companies!$F$2:$F$999,MOD((ROWS($1:1)-1),COUNTA(Companies!$A$2:$A$999))+1),EOMONTH(TODAY(),INT((ROWS($1:1)-1)/COUNTA(Companies!$A$2:$A$999))-1)+1,"")

I'm getting current months records here, but it disturbs last months records. It's shows values only till 2nd last month of each member.
Please see attached sheet.

Unable to activate array. Yes, I tried with CTRL+SHIFT+ENTER to activate array.

Then I removed iferror to see error, it throws "The value used in the Formula is of wrong type". See tab "SP" in attached sheet.

Nothing wrong with the formula ....

=IFERROR(INDEX(Companies!A$2:A$2999,SMALL(IF(Companies!$E$2:$E$2999>EOMONTH(TODAY(),0),ROW(Companies!$E$2:$E$2999)-ROW($E$2)+1,""),ROWS(A$2:$A2))),"")

Copy/paste formula into A2 of "SF" then do Ctrl+Shift+Enter

Thanks John, that works perfectly now.

Can you please help to answer for my post #31

in H2

=IF(EOMONTH(TODAY(),INT((ROWS($1:1)-1)/COUNTA(Companies!$A$2:$A$999))-1)+1<=INDEX(Companies!$F$2:$F$999,MOD((ROWS($1:1)-1),COUNTA(Companies!$A$2:$A$999))+1),EOMONTH(TODAY(),INT((ROWS($1:1)-1)/COUNTA(Companies!$A$2:$A$999))-1)+1,"")

In O2

=DATEDIF(EOMONTH(TODAY(),-1)+1,MAX(Companies!$F$2:$F$999),"m")

Great!, that works perfectly now,

I just missed to update highlighted value.

=IF(EOMONTH(TODAY(),INT((ROWS($1:1)-1)/COUNTA(Companies!$A$2:$A$999))-1)+1<=INDEX(Companies!$F$2:$F$999,MOD((ROWS($1:1)-1),COUNTA(Companies!$A$2:$A$999))+1),EOMONTH(TODAY(),INT((ROWS($1:1)-1)/COUNTA(Companies!$A$2:$A$999))-1)+1,"")

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Thanks for all your efforts and help John.

Thank you for your feedback. Hopefully, you are now in "production".