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

#### Kearnsy

##### New Member
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?

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Kearnsy

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

thanks

#### DonkeyOte

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

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

Worked perfectly. Many thanks.

Kearnsy

Replies
0
Views
93
Replies
1
Views
307
Replies
5
Views
217
Replies
1
Views
202
Replies
2
Views
97

1,190,852
Messages
5,983,234
Members
439,832
Latest member
Kephart87

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back