ExcelFanatic
Board Regular
- Joined
- May 22, 2009
- Messages
- 83
Need some assistance. I have a spreadsheet that track available hours in a month using the NETWORK day function. My worksheet is setup as follows:
HEADER ROW
Column C: Hire Date
Column D: Termination Date
Columns E through DW: 10/1/2007 through 12/1/2017 (each column header is the first day of the month
In cell E2, I have the following formula that tells me the number of hours available for each month from either the Hire Date (cell C2), or the Month Start Date (cell E1)
IF($C2 > EOMONTH(E$1,0),"",NETWORKDAYS.INTL(IF($C2 < E$1,E$1,$C2),EOMONTH(E$1,0))*8)
The formula works correctly at the front end, but I need to add to the formula to account for the Termination Date in the month column that corresponds to the Termination Date (D2). If there is a Termination Date it needs to use that date for the hours in the final month, but if the Termination Date is blank, then keep using the formula above.
HEADER ROW
Column C: Hire Date
Column D: Termination Date
Columns E through DW: 10/1/2007 through 12/1/2017 (each column header is the first day of the month
In cell E2, I have the following formula that tells me the number of hours available for each month from either the Hire Date (cell C2), or the Month Start Date (cell E1)
IF($C2 > EOMONTH(E$1,0),"",NETWORKDAYS.INTL(IF($C2 < E$1,E$1,$C2),EOMONTH(E$1,0))*8)
The formula works correctly at the front end, but I need to add to the formula to account for the Termination Date in the month column that corresponds to the Termination Date (D2). If there is a Termination Date it needs to use that date for the hours in the final month, but if the Termination Date is blank, then keep using the formula above.
