455,073 Members | 1,136 Online Need help? Post your question and get tips & solutions from a community of 455,073 IT Pros & Developers. It's quick & easy.

 P: n/a Access 2000 question Hi any help or advise required if possible i am trying to create a formula for a field. here is what i have currently rem: IIf(Time()<[Hours]![StartTime],MinToTime(([Hours]*60)),MinToTime((([Hours]*60))-([Hours]![Non-Productive]*60)-(IIf(Time()>"09:00:00" And Time()<="10:27:00",(((Time()-[Hours]![StartTime])*24*60)*[Personnel]),IIf(Time()>"10:27:00" And Time()<="13:30:00",(((Time()-[Hours]![StartTime])*24*60-12)*[Personnel]),IIf(Time()>"13:30:00" And Time()<="15:27:00",(((Time()-[Hours]![StartTime])*24*60-42)*[Personnel]),(((((Time()-[Hours]![StartTime])*24)*60)-54)*[Personnel]))))))) here is it simplified. i have a table which holds the start/end time for each day, the number of hours for each day and the number of non-productive hours for the day. Our day starts at 09:00 and finishes at 18:00 - Time diff 9 hours. during this time period there is 1 hours worth of breaks making a working day of 8 hours per person. for this excise lets say there is only one person. Now that persons day is made up of 2 hours non-productive work and 6 hours of productive work. now what i am trying to achieve is to spread a 6 hour productive working day over a 9 hour shift, now the breaks are not a problem, but incorporating the non-productive time over the course of the day, is doing my head in, i cannot just throw it in at the start or end of the day i need to account for it thru-out the day any help would be appreciated Feb 14 '06 #1 