By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,073 Members | 1,136 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,073 IT Pros & Developers. It's quick & easy.

Spread time accross day

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
Share this Question
Share on Google+
2 Replies


P: n/a
sm*******@burbidge.co.uk wrote:
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


Perhaps this will help get you started:

http://groups.google.com/group/comp....ed54b162a1aa3c

James A. Fortune
CD********@FortuneJames.com

Interesting site:
http://www.doityourself.com

Feb 14 '06 #2

P: n/a
Thanks for your help, however my boss finally got it to work, here is
the formula
IIf(Time()<[Hours]![StartTime],MinToTime([Hours]*60-[Non-Productive]*60),+MinToTime(+[Hours]*60-[Non-Productive]*60-DateDiff("n",[StartTime],Time())*[Personnel]+IIf(Time()>"10:15:00",IIf(Time()<"10:27:00",DateD iff("n","10:15:00",Time()),12),0)*[Personnel]+IIf(Time()>"13:00:00",IIf(Time()<"13:30:00",DateD iff("n","13:00:00",Time()),30),0)*[Personnel]+IIf(Time()>"15:15:00",IIf(Time()<"15:27:00",DateD iff("n","15:15:00",Time()),12),0)*[Personnel]))

Feb 15 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.