Using the solver in excel 2000
 

I use excel to record my timesheets for work.

I write in the time i arrive, the time i leave and the time spent on my
lunch break.

I use basic formulas to calculate the hours for each day and the total for
the week, which is a maximum of 36 hours.

DAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY
START 8:30 8:30 8:15 8:00 8:00
FINISH 17:00 17:00 16:00 16:30 14:30
LUNCH 0:45 0:45 0:45 0:45 0:45
TOTAL 7:45 7:45 7:00 7:45 5:45
GRAND TOTAL 7:45:00 15:30:00 22:30:00 30:15:00 36:00:00

I want to use the solver to allow me to see what time i could finish later
in the week by solving the timesheet to reach my target hours of 36.

When i try to use a solver it says it cannot get an answer.

Any help would be much appreciated

Jody Williams

No need to use solver, try the following. Assuming your data you posted is
in range A1:F5
Enter the following in B8: 36:00:00
C8: =$B$8-B5
D8: =$B$8-C5
E8: =$B$8-D5
F8: =$B$8-E5

Modify your total row (row 4) to
=IF(AND(B1<"",B2<""),B2-B1-B3,0)
and copy across

Row 7 will have expected finish times.
Enter in B7
=IF(B2="",IF(B1TIME(4,0,0),B1+B8/(5-COUNT($B$2:$F$2))+B3,B1+B8/(5-COUNT($B$
2:$F$2))),"")
and copy across for all days.


Mangesh




"traineeross" wrote in message
...

I use excel to record my timesheets for work.

I write in the time i arrive, the time i leave and the time spent on my
lunch break.

I use basic formulas to calculate the hours for each day and the total for
the week, which is a maximum of 36 hours.

DAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY
START 8:30 8:30 8:15 8:00 8:00
FINISH 17:00 17:00 16:00 16:30 14:30
LUNCH 0:45 0:45 0:45 0:45 0:45
TOTAL 7:45 7:45 7:00 7:45 5:45
GRAND TOTAL 7:45:00 15:30:00 22:30:00 30:15:00 36:00:00

I want to use the solver to allow me to see what time i could finish later
in the week by solving the timesheet to reach my target hours of 36.

When i try to use a solver it says it cannot get an answer.

Any help would be much appreciated

Jody Williams