extract different time intervals between to dates

[isabela]

Hello,
I've spent few days seaching and finding out how to do this in vba but with no satisfying result.
F. ex.:
Date period: 25-09-2007 13:00 to 26-09-2007 23:00

time intervals: 06:00-15:00, 15:00-18:00, 18:00-03:00 and 03:00-06:00

I need to know total of hours for each time interval within the date period.

Manually the result should be this:
06:00-15:00 = 11 h
15:00-18:00 = 6 h
18:00-03:00 = 14 h
03:00-06:00 = 3 h

Just to say that date period as well as start- and end times can vary in length.

I would very appreciate every help

Thanks

 

[MajP]

as long as the values are valid dates
DateDiff("h", startDate, stopDate)
If the values are not valid dates use dateSerial to turn integer values into dates and timeSerial to turn integers into time add together and then use dateDiff.

 

[isabela]

Hello and thanks for try.

That's not the problem.

I need to know how many hours from 06-15, from 15-18, from 18-03 and from 03-06 there are between these dates.

All four time intervals are bound to differnet prices.

Thanks

 

[MajP]

You will have to provide a little information on your table structure and relationship. You should be able to this easily in a query.

Assuming a date period is a record and each time interval is a related child record you can do an easy aggregate query to get this information. Basically group by date period and then sum (stop-start). Need to see the fields and data structure to provide more infomation.