Calculate start end dates and working days

Hi All,
I have recently been given the task to create a Master Resource file that includes - resources spent & their respective travel logs.

I have created the template but I'm having difficulty in getting it to function the way I would like.

Functioning of the template:
1. Master resource - To be populated manually by adding the data
2. Travel log - Resource selection is a list. When a particular resource is selected, all relative columns (From date, To date & Days Onsite) should be filled with information from the Master Resource.

So as an example: EMP1 as per Master Resource was Onsite on from 6th Jan, 2019 to 11th Jan, 2019. The Travel log, when the selection is for EMP1 should auto populate the From Date:6th Jan, 2019 and To Date: 11th Jan, 2019 and Days Onsite: 6. But I need all these details to be displayed in the Travel log by just selecting the resource.

If there is a better way to design this template please do let me know.

Thanks in advance
Dev

You are an infrequent visitor here. You have probably forgotten Forum Rule 1. Please re-read the forum rules. Your title is far too vague!! I have changed it for you, ON THIS OCCASION. Please try to remember to make your thread titles more meaningful (what would you have used as a Google search term??) in future.

In E3:
IFERROR(INDEX('Master Resource'!$B$3:$B$17,MATCH("Onsite",INDEX('Master Resource'!$D$3:$H$17,,MATCH($A3,'Master Resource'!$D$2:$H$2,0)),0)),"")

In F3:
=IFERROR(LOOKUP(10^300,'Master Resource'!$B$3:$B$17/(INDEX('Master Resource'!$D$3:$H$17,,MATCH($A3,'Master Resource'!$D$2:$H$2,0))="Onsite")),"")

In G3:
=IFERROR(LOOKUP(10^300,'Master Resource'!$B$3:$B$17/(INDEX('Master Resource'!$D$3:$H$17,,MATCH($A3,'Master Resource'!$D$2:$H$2,0))="Onsite")),"")

1. You did not specify the working days. I have assumed Mon-Fri. If something different, this can be accommodated.

2. You did not allow for public holidays. These can be accounted for with NETWORKDAYS. Let me know if this is an issue.

Hi Glenn,
Thank you for your reply. Yes, it's true I'm not a frequent visitor and I appreciate updating the title.

Your formulas are what I was looking for and I realize that i should've included a better sample file. So, as you mentioned that I haven't specified working days, it's Saturday-Thursday. But even though we're off on Fridays we need to account Fridays as working days in out travel log as we are hiring consultants and base our calculations on the fact that they are Onsite. Also, I have update the excel sheet to show that a single employee could be Onsite at different dates in a month. So, is it possible to calculate the "Days Onsite" as per their travel schedule? I have expanded the table in the Master Resource sheet to give a better idea of what I'm talking about.

Thanks again & really appreciate your help.

Dev

I'll look at this again tomorrow am (French time). Time for dinner here!!

I was travelling today (unexpectedly) and only have had a few minutes here. Unfortunately, your results layout is not compatible with the revised raw data. EMPs 1, 2 & 3 have multiple (here two.. but can there be more??) different periods onsite. How do you want the results to look. The layout is designed for one only. With two or more the from/to dates become meaningless. Then the Working Days between first start and last end also become meaningless. Can you re-look at your desired results, redesign it a bit and mock up what you'd like to see. I'll then see if I can deliver!!

back again... So Friday is a day off, except for the fact that it counts as a working day??? That sounds a bit Irish to me....

If you really mean that forget about the start end dates for the employee and use this:

=COUNTIF(INDEX('Master Resource'!$D$3:$H$33,,MATCH('Travel Log'!A3,'Master Resource'!$D$2:$H$2,0)),"Onsite")


If Friday is NOT to be counted then that can be built in...

1. Any possibility of making the travel log specific for an individual? Emp would be selected from a dropdown box and the table would populate with that person's data. If that's possible, it'll be easy to rattle something together.

2. Since, in effect, EVERY day counts as a working day, why do you need NETWORKDAYS when calculating days on-site? Why not just subtract finish from start?

3. Please amend your profile to show which version of Excel you are using. Clearly it's more advanced than Excel 2003 (.xlsx extension).

i may have other Qs later. I'm going to look at this again now, and then let it ferment overnight in my head....

Ok.... That increases the level of difficulty a bit... So. Is it possible to add helper columns to Master resource, or on a hidden sheet?

Got it!! I was looking at this problem backwards!! Take a look. Check that the results are as expected. It is NOT finalised yet - so don't rush off and put it in your real sheet. There are a couple of things I need to tweak (the non contiguous ranges may cause confusion, so I'll deal with that by using named ranges); and I'll make the formulae in columns A, E & F non-array at the final stage. However, before doing that, play around with THIS sheet and try to break it.

new formulae in muddy green cells.

Just in case:

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Formula in column F doesn't work in previous version. It does now. Same comments apply as in previous 2 posts.

Check it over & get back to me ... on THIS sheet.

Originally Posted by Glenn Kennedy

Formula in column F doesn't work in previous version. It does now. Same comments apply as in previous 2 posts.

Check it over & get back to me ... on THIS sheet.

Glenn, you are a genius. This sheet that you have made actually works just the way I want it. I mean seriously it's so brilliant that I no longer need to select the employee in the travel log. It just populates the cells in the travel log as I fill the details in the master resource.

Just a question, I added a row after the September to have Q3 Total of Days onSite. Basically that is how the original design of the template is where we total every quarter and then a yearly total in the end. But then the array formulae doesn't pic the details for October. If I remove the row it works. Would it be possible to include the Quarterly totals in this template. Sorry for being such a pain.

I have attached the sheet to show the Q3 Total.

I'm away having fun. Back tomorrow,probably... I'll look at it/maybe fix it then, unless I have more questions...

Sorry, in my excitement forgot to reply to this. Yes, you can add helper columns to the Master resource or on a hidden sheet as well.

Pl see file.
For quarter calculation in L3 then copy down.

Please Login or Register  to view this content.

In M3 then copy down

Please Login or Register  to view this content.

Hi. Back again. One comment and 2 Qs.

I will strongly suggest an alternative to dropping your quarterly total into the body of the data. It will cause endless grief and pain later on.

Q1. How many employees (maximum)?

Q2. On the raw data, how many rows (maximum)?..Is this an annual sheet? If so, are you working off calendar quarters, or financial quarters... If financial, what is the 1st day of your FY?

It's all done bar the quarterly summary. Formulae are all non-array and non-volatile. The quarterly summaries will be offset to the right. DO NOT attempt to reproduce your old template. It is badly laid out (I can tell, without even seeing it!!!!). Having summaries in the body of the data will kill you, sooner or later. So... improve on it.

I REALLY need answers:

Does your FY match the calendar year? If not, what does it match?

How many people (maximum)?

How many rows (maximum)? Is this intended to be an annual sheet so 400 is plenty, or what??

If it is annual, do you complete the date column (column B of Master Resource) month by month or at the start of the year?

If an onsite period STARTS in Q2 and ENDS in Q3, which summary period do you want the days to count as?

Please answer ALL questions with total clarity.

I REALLY need answers:

Does your FY match the calendar year? If not, what does it match? - I'm not sure about my FY as I'm the Project coordinator here. But if it is a must information then I can check with my accounts dept.

How many people (maximum)? - At the moment we are at 10 employees but based on the project requirement we may have additional consultants coming in for a short visit of a week or two.

How many rows (maximum)? Is this intended to be an annual sheet so 400 is plenty, or what?? - Yes, this is intended to be an annual sheet. 400 rows completely depends on the travel plans of the employees as they have a limited duration of stay and need to exit the country, have a few days for R&R and then return.

If it is annual, do you complete the date column (column B of Master Resource) month by month or at the start of the year? - As it is annual, we complete the date column from the start of the year.

If an onsite period STARTS in Q2 and ENDS in Q3, which summary period do you want the days to count as? - So, this is a good example to show as what we usually do as per the design of the Travel Log. If an onSite start date is say for example on March 27th and the end date is 4th April, then the travel log is calculated for both March & April as such:
March: Start date: 27-Mar-19 End date: 31-Mar-19 (Days onsite= 5), &
April: Start date: 01-Apr-19 End date: 04-Apr-19 (Days onsite= 4)
There is a column(Tickets/ Travel Details) before the Start date in the original file that would allow to identify when the employee has travel inbound or outbound as per the dates.

I know it is a very complicated sheet, but to be honest this is the sheet I was handed and was asked to work with. Even I think as per design it's very poorly constructed but once a week I need to email the travel log to the management to keep them informed of the travel plans of the employees. It is a difficult task at the moment because our employees travel a lot and I have to manually add every detail in 2 sheets(Master resource & Travel Log).

Hope this clarifies your questions.

Originally Posted by kvsrinivasamurthy

Pl see file.
For quarter calculation in L3 then copy down.

Please Login or Register  to view this content.

In M3 then copy down

Please Login or Register  to view this content.

I think this was the wrong file attached??

Yes. I do need to know the dates of your FY, so I know when to put in formulae for the Q1, Q2, etc, summaries. After that, it's a short step to complete the job.

Originally Posted by ecronic

I think this was the wrong file attached??

Sorry for the mistake.
For quarter calculation.
Now correct file attached.

Alright, checked with the accounts dept. Our FY matches the calendar year.

Super. I'll post the file, with two alternatives for the summary in the morning. It's time to eat shellfish now....

Here it is, in final form, I hope. A few named ranges (CTRL-F3 to view and edit) automatically adjust the ranges used in the formulae. IMHO, its more sensible to do the quarterly summaries at the top of the sheet, instead of in another column and scrolling down, down, down, to find them.

Happy to explain any aspect for you. See a few notes on both main sheets.

Hi Glenn,

Thank you so much for your help.

This design is much simpler and easier to work with. I will be adding this to my original file and will send it out as the first report this weekend. Fingers crossed they accept this new design.

Just wanted to check on 2 things:

1. The total for days on site in the summary section (248) vs the total in the manually selected range G12:G25 (250) are different.
2. In the travel log, is it possible calculate the days Onsite for every employee/month. For e.g. EMP2 in A13 has traveled from 01Jan - 09FEB. So, is it possible to keep the days Onsite as Jan - 31days and in the next row show for Feb - 9days

Thanks again.

I'll check that out ASAP.

"My"formula (the summary) is correct. "Your" formula is correct (and incorrect). See row 20. It staggers the Q end by 2 days. Hence the difference. "Your" formula is counting the difference End-Start. "Mine" is taking into account the fact that 1st and 2nd April are in Q2.

You're welcome.

---

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

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

I get it now. Thank you very much for you support.

Woo Hoo.You're welcome.

---

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

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.