Using the solver in excel 2000

[Guest]

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

 

[Mangesh Yadav]

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(B1>TIME(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