Hours (1 Viewer)

[Sdbuk]

Hi

I run a children’s nursery. Parents have “Free” hours they can use between 9:00 and 15:00 outside of those hours they are charged.

So if someone starts at 8:00 and finishes at 16:00 they will be charged for 2 hours. I have the start and finish times but run into problems if a parent starts at say 14:00 and finishes at 18:00. In this example they would be charged for 3 hours.

I have spent days on this so far and reached the point of not being able to see the wood for the trees.

Any help would be appreciated.

 

[June7]

What have you tried? Are you writing a VBA custom function to calculate daily charge?

Provide sample of table structure.

 

[plog]

So you have 2 possibilities for chargetime--the start time is before 9 or the end time is after 15.

ChargeTime: iIf(StartTime<9, 9 - StartTime, 0) + Iif(EndTime>15, EndTime-15)

You calculate them seperately and then add them. The only caveat is that the start time cannot be after the end time (e.g. start at 15:15) and that the end time cannot be before 9 (e.g. end at 8:50). If either of those is possible, then this needs to become a custom function to which you pass the start and end time values and the function does the calculation there.

 

[Gasman]

Hi

I run a children’s nursery. Parents have “Free” hours they can use between 9:00 and 15:00 outside of those hours they are charged.

So if someone starts at 8:00 and finishes at 16:00 they will be charged for 2 hours. I have the start and finish times but run into problems if a parent starts at say 14:00 and finishes at 18:00. In this example they would be charged for 3 hours.

I have spent days on this so far and reached the point of not being able to see the wood for the trees.

Any help would be appreciated.

Isn't 3 hours correct?

 

[LarryE]

Hi

I run a children’s nursery. Parents have “Free” hours they can use between 9:00 and 15:00 outside of those hours they are charged.

So if someone starts at 8:00 and finishes at 16:00 they will be charged for 2 hours. I have the start and finish times but run into problems if a parent starts at say 14:00 and finishes at 18:00. In this example they would be charged for 3 hours.

I have spent days on this so far and reached the point of not being able to see the wood for the trees.

Any help would be appreciated.

Help with what? 14:00-15:00 is free
15:00-16:00 is 1 chargable hour
16:00-17:00 is 1 chargable hour
17:00-18:00 is 1 chargable hour
There are 3 chargable hours

 

[June7]

I presume these times are for separate days and/or different clients. 800 to 1600 would be 2 extra hours and 1400 to 1800 would be 3 (short but late day).

This would be a fairly simple IIf() expression. Would be helpful to know your data. Are you saving a full date/time value for start and end? Again, post a sample of table structure and data. Can build a table in post or attach file.

Maybe like:
SELECT AttID, AcctID, TimeValue([Start]) AS StartT, TimeValue([End]) AS EndT,
IIf([StartT]<#12/30/1899 9:0:0#,DateDiff("n",[StartT],#12/30/1899 9:0:0#),0)/60+IIf([EndT]>#12/30/1899 15:0:0#,DateDiff("n",#12/30/1899 15:0:0#,[EndT]),0)/60 AS OT
FROM Attendance;

In Design View the expression looks like:
OT: IIf([StartT]<#9:00:00 AM#,DateDiff("n",[StartT],#9:00:00 AM#),0)/60+IIf([EndT]>#3:00:00 PM#,DateDiff("n",#3:00:00 PM#,[EndT]),0)/60

That will calculate decimal hour without rounding.

Do you want to round up to full hour or 1/2 hour or 1/4 hour? That gets a little complicated. Review http://www.allenbrowne.com/round.html
Rounds up to next 1/4 hour:
Int(-4*2.353)/-4