Today I have 2 formula challenges that you may find interesting. They are both different types of allocation formulas.
Challenge 1:
Problem: You have 9 tasks that each take a different amount of time to complete. The project will be completed over 2 weeks and it can start on any day of the week. Each day of the week has a maximum work time. Tasks can be started on one day and finish on another.
Challenge: Write 3 formulas:
1) Formula 1 in Cell B4 that displays the weekday name of the date entered in Cell B3.
2) Formula 2 for the start of the project that you can copy across the range C4:I4. Formula starts in Cell C4 and displays the word Start if the day of the week = start date of the project.
3) Formula 3 for the allocation of hours by task by day for the 2 week time period. Hours for the day cannot exceed the max hours for the day in row 2. Tasks can be started on one day and finish on another. If there is time left in the day, then the next task should be started.
Here is the data file: Allocation Challenge 1
This is what the final layout will look like if you are successful:
Challenge 2:
Problem: You have multiple allocations that you need to hire for over several months. You don’t want to have too many hires sitting on the bench, so you want to know how many you need in a given month. Allocations may be open ended or may have an end date. There are 2 types of designations of hires – Managers and Engineers.
Challenge: Write 1 formulas:
1) Formula 1 for the count of resource types that you need for a given month that can be copied across the range J2:U2.
Here is the data file: Allocation Challenge 2
This is what the final layout will look like if you are successful:
PS: Please put your formulas in the comments below.
Steve=True
