calculate calender days resource available
Hi
I am building a spread sheet that shows how many days a resource will be available in each month of a year based on a start and end date.
Each line of the sheet (B6 onwards) is dedicated to 1 individual resource and that individual is not repeated in other rows.
The formula below works great using a start date but I need to build in an end date so be more accurate.
=IF($L6>0,IF(O$5>$I6,O$4*$L6,IF(O$4-NETWORKDAYS(O$5,$I6,Data!$B$6:$B$14)>0,(O$4-NETWORKDAYS(O$5,$I6,Data!$B$6:$B$14))*$L6,0)),"")
This formula is used in cells C6 through Z6 therefore each cell represents 1 month.
formula Cell ref defination.
Cell L6 is a percentage number stating their default availability per working day i.e.
100% will be full working day whereas 50% would mean only for 1/2 a day.
Cell O5 is a calender month in the format Jan-11
Cell O4 is the working days available in the month shown in O5
Cell I6 is the start date i.e.
05/04/2011
Cell j6 would be the end date
Any questions please ask and many thanks.
Colin
Anwsers to the Problem calculate calender days resource available
Download SmartPCFixer to Fix It (Free)
It seems like you can add an end date in J6 and get rid of a few IF() statements with the judicious use of MIN() and MAX() functions.
In O6 try,
=MIN(O4,MAX(0,NETWORKDAYS(MAX($I6,O5),MIN($J$6,DATE(Data!$B$5,MONTH(O$5)+1,0)),Data!$B$6:$B$15)))*$L$6
(my holiday list was B6:B15 and O5:Z5 are 1-Jan-11 to 1-Dec-11)
Sample worksheet uploaded
here.
Cleaning your computer, components, and peripherals help keep everything in good working condition and helps prevent germs from spreading. You can't image how dirty the inside of your computer case can get. All the dust and dirt is going to prevent proper air flow and may even prevent the fan from working.
Another Safe way to Repair the Problem: calculate calender days resource available:
How to Fix calculate calender days resource available with SmartPCFixer?
1. Click the button to download SmartPCFixer . Install it on your system. Open it, and it will perform a scan for your system. The errors will be shown in the scan result.
2. After the scan is finished, you can see the errors and problems need to be repaired. Click Fix All.
3. When the Fixing part is finished, your computer has been speeded up and the errors have been fixed
Related: How to Download Toshiba Portege R500 HDD/SSD Alert Utility v.2.2.0.0 driver,Way to Update & Download Toshiba Satellite A355-S69403 Motorola Modem Region Select Utility v.2.2.3.0 driver,Where to Download Toshiba Satellite L305-S5926 HW Setup Utility v.2.00.11 driver,[Solved] Download Toshiba Satellite L675-S7115 Laptop Checkup v.2.0.6.22 driver,Method to Download Toshiba Satellite M645-S4080 Media Controller v.1.1.88.1 driver,Best Way to Download NVidia GeForce 6100 VGA Driver v.304.51 Certified,How Can You Update & Download NVidia GeForce 9300/nForce 730i VGA Driver v.310.19 Certified,Method to Download NVidia GeForce GT 330M Driver v.340.65,How Can I Update & Download NVidia GeForce GTX 590 Driver v.280.26 WHQL,Method to Download NVidia Tesla C2050 Driver v.319.17
Read More: Tech Support: BSOD after sleeping mode,Fast Solution to Error: Browser uploading issues,How to Fix - Broken transceiver?,[Solved] BSOD on Windows 8 - Driver Overrun State Buffer,How to Fix Problem - Bluetrack wireless mouse stopped working??,All diagnostic tools give 0x800700C1, troubleshooting wizard can't continue,all files try to open with adobe,all icons on desktop have changed to Adobe icon and won't open,All functions of my outlook are working except it will not let me "REPLY" or start a "NEW" email,all f1-f12 shortcuts not work