Resource availability and schedular

[Arun5555558]

Hi All,

I have no idea, how much is the difficulty level for this task or is it achievable. I reached to this forum via google and found that this forum is quite useful, users are active and also helpful.

I have an excel file where I used to put data for assigning new work to team, whenever we receive new task, we make an entry into it. Example for such sheet uploaded as "Scheduler"

Now senior member wants to get a similar workbook where we can get data for resource availability of each employee daily and biweekly basis. We are now achieving such thing doing this manually. Example for such sheet uploaded as "Availability sheet"


Now the question is, how to do this via formula/vba macro find out daily and biweekly availability of hours of each employee based on the factors
- Employee on leave
- Budgeted hours assigned in Scheduler sheet for each task
- weekly off on Saturday and Sunday
- Public Holiday
- Not more than 8 hours working for each day

I want to achieve similar sheet as used in uploaded file "Availability sheet".

Let me know if I am unclear or need some other information.

Dave

 

[vletm]

How do You set the 1st Monday?
How do solve 'Bi Weekly Availability'? Is there any clear logic?
What to do if 'Scheduler' have over 8hrs days? (because Start-End dates)
How many 'Employees' or always 4?
How many 'Tasks' or always 21?
Do that need to make 'manual modifications'
... if given result just no match for someone?

 

[Arun5555558]

How do You set the 1st Monday?
Ans: We input all the received entries manually before monday, so we have little observation for next week. We also update it later, if we have new received entries/leave details available, we add it to scheduler and Availability sheet throughout next 2 weeks.
How do solve 'Bi Weekly Availability'? Is there any clear logic?
Ans: For Daily availability we check how much of work hr assigned in a day.
For biweekly availability: it shoud be like
No. of hrs assigned - leave hr - holiday hr

What to do if 'Scheduler' have over 8hrs days? (because Start-End dates)
Ans: Assuming received as assigned day, if more than 8 hr of work, we will allocate rest number of hrs on next day and so on. (We also consider, if there is another task have more priority of End day, we than allocate the rest hours later after priority work)

How many 'Employees' or always 4?
Ans: not fixed, it can be any number mostly 15-20


How many 'Tasks' or always 21?
Ans: Not fixed

Do that need to make 'manual modifications'
... if given result just no match for someone?

Please see my answers above
Ans: Yes, we modify, as per situation come or new entries received in scheduler.

I am open to get solutions or if require I can change my scheduler template to get better results.

Thanks

 

[vletm]

the 1st version ... = not final, needs modify ...
How do You set the 1st Monday?
> hmm ... now checks the earliest date...
biweekly availability: it shoud be like No. of hrs assigned - leave hr - holiday hr
... not clear answer => show me!
Press [DO IT] to see the sample ver 1.
>> Answers - Ideas - Questions?

 

[Arun5555558]

the 1st version ... = not final, needs modify ...
How do You set the 1st Monday?
> hmm ... now checks the earliest date...
biweekly availability: it shoud be like No. of hrs assigned - leave hr - holiday hr
... not clear answer => show me!
Press [DO IT] to see the sample ver 1.
>> Answers - Ideas - Questions?

Thanks for your effort and time given on this, below are the required new answers.

How do You set the 1st Monday?
----- Our last biweekly data was from 26-Dec 2016 to 6-Jan 2017, so the next data report should be 9-Jan to 20-Jan and so on. To get this result, I think vba code should read what task are assigned on these days.

biweekly availability: it shoud be like No. of hrs assigned - leave hr - holiday hr
... not clear answer => show me!
----- Sorry, it should be addition of free hour lefts in all 15 days. for e.g. Employee 1 as per schedular have full 8 hr available on 3-Jan and 4-jan and on 6-Jan there are 7 hrs hence it is 8+8+7=23 hrs

Few more points:
A) In original, there are more than 2 weeks of data available on schedular, considering that
In your provided file, Column L to W should show only 2 weeks of data. (In current scenario, If I changed the date in column F to something random, I am getting some unnecessary detail after column W, please check.)

B) In original, Task assigned on scheduler are in random order for employee name. Currently it is Employee 1 for task 1 to 10, Employee 2 for task 11 to 17.
That is why we have created Column B to E in my sample file in availability sheet, so we can look easily what task are assigned to employee 1 and so on.

Let me know if any other info is required.

 

[vletm]

1) How do You set the 1st Monday?
Our last biweekly data was from 26-Dec 2016 to 6-Jan 2017, so the next data report should be 9-Jan to 20-Jan and so on. ... => Where has saved 'last biweekly data's date?
If someone do it 9-Jan ... which biweekly data would show?
<= NOW, it shows also 'UNDONE' tasks = from the earliest days
2) biweekly availability = Free hours ... Okay
A) I changed the date in column F to something random ...
Could You specify any sample?
Could You give limits for dates?
NOW, it checks that 'start date' would be between L4&W4.
B) In original, Task assigned on scheduler are in random order for employee name.
=> Could You send 'more normal schedule' with same data or more real data?
I would be more easy to work with real raw sample data.
we can look easily what task...
=> There are same information
3) If every employees names would be in row 4 and dates(8hrs) in column ...
and tasks could see there ... gotta make better sample later if needed.

 

[vletm]

I add 'Sort'-option. Click any header below Sort-text.
Now, You can edit data as You would like and also see as You would like.
+ that 'biweekly availability'-hours
>> Answers - Ideas - Questions?

 

[vletm]

the 3rd version with some new/updated features ...
I am open to get solutions or if require I can change my scheduler template to get better results.
>> Answers - Ideas - Questions?

 

[Arun5555558]

Hi vletm,

Thanks again for your time and effort.

I am gathering information required and will come back later on this.

Dave

 

[vletm]

... just 15-35 rows of data would be enough ...
I add few functions like: You can see those 'leave' only as You wanted too.