I am so stuck with this coding... Any ideas??

[Kearnsy]

I am trying to create a construction programme and havig a few problems.

I have two columns one called Start Date and another called End Date.

The formula in the cells under Start is as follows:

=IF(F12>=1, $F$9, IF(G12>=1, $F$9+1, IF(H12>=1, $F$9+2, IF(I12>=1, $F$9+3, IF(J12>=1, $F$9+4)))))

Forluma in the cells under End Date is:

=IF(F12>=2, F9, IF(G12>=2, F9+1, IF(H12>=2, F9+2, IF(I12>=2, F9+3, IF(J12>=2, F9+4)))))

The cell F9 is a date and is the first day of a week i.e 16/6/2008.

F12 to J12 has been formatted using coditional formatting so that if a 1 or 2 is inputed into the cell it changes colour and highlights the days when construction work is being carried out on. The idea is 1 is the start date all the other days are 1 as well but the last day that work is carried out on i would input 2 and that would go straight into the end date cell.

This only works for one week. I have a 36 week programme. I have tried the formlua.....IF(F12:Z12>=1, F9) but that doesn't work.

Any ideas?

 

[DonkeyOte]

dupe post

http://www.mrexcel.com/forum/showthread.php?t=328928

I was about to look at this...

 

[Kearnsy]

sorry dude yeah if you got some spare time would appreciate some feed back if your able to help.

thanks

 

[DonkeyOte]

I think you want to use a MATCH :

start date:
=$F$9+(MATCH(1,$F12:$J12,0)-1)

end date
=$F$9+(MATCH(2,$F12:$J12,0)-1)

though I don't know what you would do if you started and ended on the same day

 

[Kearnsy]

Awesome that works but does it only work with a seven day week??

I would ideally want this to run on a five day week as Saturday and Sunday is not normally shown on a programme.

 

[DonkeyOte]

I would say IMHO that you'd be better off placing the actual dates in say row 11

so assuming your old set up such that F9 = 16/Jun/08

in F11

=$F$9

then in G11

=F$11+IF(WEEKDAY(F$11)=6,3,1)

and copy that out across row 11 -- this will then only list out weekdays... assume this has gone out to say column AX11 (15/08/08)

you can alter your match formula to:

Start
=OFFSET($F11,0,MATCH(1,$F12:$AX12,0)-1)

End
=OFFSET($F11,0,MATCH(2,$F12:$AX12,0)-1)

HTH.

 

[Kearnsy]

Chap without getting too emotional.........your an absolutely legend as well a genius.

Worked perfectly. Many thanks.

Kearnsy