Populate row information based on start end dates

**rdesai7677** · 03-09-2012, 04:50 PM

Hi there

I was wondering if someone could help.

I have a spreadsheet where I would put in resource name, start date, end date. Based on start date and end date I would like a formula to populate the date in row 1 of each corresponding column>

Spreadsheet Setup (attached)
Std Hours > 24 40 40
St Week> 2/1/2012 2/5/2012 2/12/2012
En Week> 2/4/2012 2/11/2012 2/18/2012
Name Start End Std Hrs
Person A 2/1/2012 1/31/2013 40 24 40 40
Person B 2/1/2012 2/18/2012 20 12 20 20

Does anybody know how I could achieve this / if it is even possible?

Thank you in advance
R

**icestationzbra** · 03-09-2012, 05:07 PM

the description is not explanatory enough - can you provide your expected results also (preferably header-ed / formatted / highlighted, without formulae)?

**rdesai7677** · 03-09-2012, 05:23 PM

Thank you,

Apologies for not being clear - what I am expecting to see is the hours get populated with a formula based on start date and end date in respective week ranges. Basically I am trying to populate the cells hightlighed in yellow by a formula.

Thanks again

**icestationzbra** · 03-09-2012, 06:23 PM

put this formula in E6 on your sheet. then, drag it right up to G column, and down up to row 7.

=NETWORKDAYS(E$3,E$4)*$D6/40*8

by the way, if you want to account for holidays, then you will have to capture those in separate cell (or cells) and pass the range of the cell (or cells) to the NETWORKDAYS formula, for example:

=NETWORKDAYS(E$3,E$4,$A$1:$A$3)*$D6/40*8

where A1, A2 and A3 contain the dates on which certain holidays fall.

**rdesai7677** · 03-12-2012, 03:58 PM

Thank you - this worked however if I copy the formula across it won't stop past the End Date.

**icestationzbra** · 03-12-2012, 04:20 PM

post a file with your issue.

**daddylonglegs** · 03-12-2012, 06:41 PM

Try changing to this formula in E6 copied across and down

=MAX(0,NETWORKDAYS(MAX(E$3,$B6),MIN($C6,E$4)))*$D6/E$2*8

Populate row information based on start end dates

LinkBack

Thread Tools

Rate This Thread

Display

Populate row information based on start end dates

Re: Populate row information based on start end dates

Re: Populate row information based on start end dates

Re: Populate row information based on start end dates

Re: Populate row information based on start end dates

Re: Populate row information based on start end dates

Re: Populate row information based on start end dates

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions