OK so say I have a shop and I need to forecast the maximum number of hours people can work.
OK so on the first row in my Excel Sheet I have a list of months 'Jan-15,Feb-15,Mar-15,Apr-15' and so on.....(in reality these are '01/01/15,01/02/15,01/03/15,01/04/15' but they have been formatted as mmm-yy.)
I have a variety of employees that have worked varying periods for example:
1. Person A worked 01/01/15-30/04/15
2. Person B worked 17/02/15-26/04/15
3. Person C worked 31/01/15-01/03/15
4. Person D worked 01/01/15-25/01/15
Each month the total hours available to work is slightly different: Jan 150 hrs, Feb 125 hours, Mar 160hrs, Apr 140 hrs
I need an automatic formula on an Excel sheet so that I can see that:
Person A can work Jan: 150, Feb 125, Mar 160, Apr 140
Person B can work Jan: 0, Feb 49.10 (125hrs*(11/28 days worked in month), Mar 160, Apr 121.33 (140hrs*(26/30 days worked in month))
Person C can work Jan: 4.83 (150hrs*(1/31 days worked in month)), Feb 125, Mar 5.16 (160hrs*(1/31 days worked in month)), Apr 0
Person D can work Jan: 120.96 (150hrs*(25/31 days worked in month)), Feb 0, Mar 0, Apr 0
Is this available to do with a simple IF formula?
Thanks in advance
Bookmarks