Calculating Dates

E

Eric

Hello everyone.

How would I go about calculating dates between two dates
if 1 day = 8 hours? Here's my senario.

I have been using this formula to calculate days between
two dates (timestamped):

Function Work_Days(BegDate As Date, EndDate As Date) As
Integer
' Note that this function does not account for holidays.
' From http://mvps.org/access/
Dim WholeWeeks As Variant
Dim DateCnt As Variant
Dim EndDays As Integer

'Added 7/7/2004 per Sharon Larsen
'Adds the Beginning Date and the End Date on the
Report but omits weekends. (Cycle Time)

BegDate = DateValue(Reports!rptPrintCycleTime!
StartDate)
EndDate = DateValue(Reports!rptPrintCycleTime!
EndDate)
WholeWeeks = DateDiff("w", BegDate, EndDate)
DateCnt = DateAdd("ww", WholeWeeks, BegDate)
EndDays = 0
Do While DateCnt < EndDate
If Format(DateCnt, "ddd") <> "Sun" And _
Format(DateCnt, "ddd") <> "Sat"
Then
EndDays = EndDays + 1
End If
DateCnt = DateAdd("d", 1, DateCnt)
Loop
Work_Days = WholeWeeks * 5 + EndDays
End Function

The code above works great. But now I need it to calculate
hours instead of days. Some of my parameters are that 8am
to 5pm are our working times and 8 hours = 1 day. I would
want to be able to calculate how many hours a user as done
his/her work, based on a 8am to 5pm workday. So for
example, if someone started on 9/15/2004 8:00:00 AM and
finished on 8/16/2004 2:00:00 PM, I would want to show 12
hours.
 
K

Ken Snell [MVP]

You can use DateDiff to return the elapsed hours:
DateDiff("h", StartDateTime, EndDateTime)

I'm not sure how you want to handle all the other actions that your code
currently does for days, so I won't advise about any changes in that code.
Perhaps also you might be able to combine the two concepts of an 8-hour day
being one day, and more hours than that being additional fraction of a day:

NumberOf8HourDays = DateDiff("h", StartDateTime, EndDateTime) / 8
 
G

Guest

Here's the query I am running. this may make more sense to
where I am getting my data from for the report:

SELECT Plant.Name, User.Name, AnalysisTestGroup.StartDate,
AnalysisTestGroup.EndDate, AnalysisTestGroup.ID,
qryAnalysisCount.Sets
FROM qryAnalysisCount INNER JOIN (User INNER JOIN (Plant
RIGHT JOIN AnalysisTestGroup ON Plant.Code =
AnalysisTestGroup.PlantCode) ON User.ID =
AnalysisTestGroup.TestPerson) ON
qryAnalysisCount.TestGroupID = AnalysisTestGroup.ID
WHERE (((AnalysisTestGroup.EndDate) Is Not Null))
GROUP BY Plant.Name, User.Name,
AnalysisTestGroup.StartDate, AnalysisTestGroup.EndDate,
AnalysisTestGroup.ID, qryAnalysisCount.Sets
HAVING (((AnalysisTestGroup.StartDate) Between [Forms]!
[frmReports]![txtStartDate] And [Forms]![frmReports]!
[txtEndDate]+1))
ORDER BY AnalysisTestGroup.StartDate;
 
F

Fons Ponsioen

A possible approach is to consider that;
1. 168 raw hrs = 1 week = 40 hrs
2. 24 raw hrs = 1 day = 8hrs
Therefore you can use DateDiff to return the elapsed hours:
RawHours=DateDiff("h", StartDateTime, EndDateTime)
now you can use:
WeeklyHours=Int(RawHours/168)*40
DailyHours=Int((RawHours-(WeeklyHours*168)/24)*8
WorkHours=RawHours-(WeeklyHours*169)-(DailyHours*24)
Now TotalHours=WeeklyHours+DailyHours+WorkHours
Ofcourse this does not account for any holidays, and you
may need to make adjustments if there is other than a 5
day work week.
Hope this helps.
Fons
 
G

Guest

Your code does not work as intended. I get negative signs
on some of the calculations.
 
F

Fons Ponsioen

Yes I have reviewed it, here is an sql for a query:
SELECT DateHours.*, DateDiff("h",[DateStart],[DateFinish])
AS RawHours, Int([RawHours]/168)*40 AS WeeklyHours, Int
(([RawHours]-([WeeklyHours]/40)*168)/24)*8 AS DailyHours,
[RawHours]-([WeeklyHours]/40*168)-([DailyHours]/8*24) AS
WorkHours, [WeeklyHours]+[DailyHours]+[WorkHours] AS
TotalHours
FROM DateHours;
All on one line.
DateHours is the table I used with the variables DateStart
and DateFinish. So:
RawHours = DateDiff("h",[DateStart],[DateFinish])
WeeklyHours = Int([RawHours]/168)*40
DailyHours = Int(([RawHours]-([WeeklyHours]/40)*168)/24)*8
WorkHours = [RawHours]-([WeeklyHours]/40*168)-
([DailyHours]/8*24)
and
TotalHours = [WeeklyHours]+[DailyHours]+[WorkHours]
sorry I did not do it quite right from scratch.
Hope this helps.
Fons
 
K

Ken Snell [MVP]

I'm sorry, but I don't see any connection between this query and the
function that you posted. This query doesn't call that function.

Where is the function being used?
 
F

Fons Ponsioen

As I thought about it last night I made one more
correction:
SELECT DateHours.*, DateDiff("h",[DateStart],[DateFinish])
AS RawHours, Int([RawHours]/168)*40 AS WeeklyHours, Int
(([RawHours]-([WeeklyHours]/40)*168)/24)*8 AS DailyHours,
[RawHours]-([WeeklyHours]/40*168)-([DailyHours]/8*24) AS
WorkHours, [WeeklyHours]+[DailyHours]+[WorkHours]-24+8 AS
TotalHours
FROM DateHours;
All on one line from Select DateHours through DateHours;.
Click to expand...
DateHours is the table I used with the variables DateStart
and DateFinish. So:
RawHours = DateDiff("h",[DateStart],[DateFinish])
WeeklyHours = Int([RawHours]/168)*40
DailyHours = Int(([RawHours]-([WeeklyHours]/40)*168)/24)*8
WorkHours = [RawHours]-([WeeklyHours]/40*168)-
([DailyHours]/8*24)-24+8
and
TotalHours = [WeeklyHours]+[DailyHours]+[WorkHours]
sorry I did not do it quite right but I had to adjust for
the possible hour start being later than hour finish.
Hope this helps.
Fons
-----Original Message-----
Yes I have reviewed it, here is an sql for a query:
SELECT DateHours.*, DateDiff("h",[DateStart], [DateFinish])
AS RawHours, Int([RawHours]/168)*40 AS WeeklyHours, Int
(([RawHours]-([WeeklyHours]/40)*168)/24)*8 AS DailyHours,
[RawHours]-([WeeklyHours]/40*168)-([DailyHours]/8*24) AS
WorkHours, [WeeklyHours]+[DailyHours]+[WorkHours] AS
TotalHours
FROM DateHours;
All on one line.
DateHours is the table I used with the variables DateStart
and DateFinish. So:
RawHours = DateDiff("h",[DateStart],[DateFinish])
WeeklyHours = Int([RawHours]/168)*40
DailyHours = Int(([RawHours]-([WeeklyHours]/40)*168)/24)*8
WorkHours = [RawHours]-([WeeklyHours]/40*168)-
([DailyHours]/8*24)
and
TotalHours = [WeeklyHours]+[DailyHours]+[WorkHours]
sorry I did not do it quite right from scratch.
Hope this helps.
Fons
-----Original Message-----
Can you show some sample data and some sample results?
Fons
.
Click to expand...
.
Click to expand...
 
F

Fons Ponsioen

Last correction, I hope:
Query:
SELECT DateHours.*, DateDiff("h",[DateStart],[DateFinish])
AS RawHours, Int([RawHours]/168)*40 AS WeeklyHours, Int
(([RawHours]-([WeeklyHours]/40)*168)/24)*8 AS DailyHours,
IIf([RawHours]-([WeeklyHours]/40*168)-([DailyHours]/8*24)
8,[RawHours]-([WeeklyHours]/40*168)-([DailyHours]/8*24)-
Click to expand...
24+8,[RawHours]-([WeeklyHours]/40*168)-
([DailyHours]/8*24)) AS WorkHours, [WeeklyHours]+
[DailyHours]+[WorkHours] AS TotalHours
FROM DateHours;
all on one line.
The change is the:
WorkHours: IIf([RawHours]-([WeeklyHours]/40*168)-
([DailyHours]/8*24)>8,[RawHours]-([WeeklyHours]/40*168)-
([DailyHours]/8*24)-24+8,[RawHours]-([WeeklyHours]/40*168)-
([DailyHours]/8*24))
Take care.
Fons
-----Original Message-----
As I thought about it last night I made one more
correction:
SELECT DateHours.*, DateDiff("h",[DateStart], [DateFinish])
AS RawHours, Int([RawHours]/168)*40 AS WeeklyHours, Int
(([RawHours]-([WeeklyHours]/40)*168)/24)*8 AS DailyHours,
[RawHours]-([WeeklyHours]/40*168)-([DailyHours]/8*24) AS
WorkHours, [WeeklyHours]+[DailyHours]+[WorkHours]-24+8 AS
TotalHours
FROM DateHours;
All on one line from Select DateHours through DateHours;.
Click to expand...
DateHours is the table I used with the variables DateStart
and DateFinish. So:
RawHours = DateDiff("h",[DateStart],[DateFinish])
WeeklyHours = Int([RawHours]/168)*40
DailyHours = Int(([RawHours]-([WeeklyHours]/40)*168)/24)*8
WorkHours = [RawHours]-([WeeklyHours]/40*168)-
([DailyHours]/8*24)-24+8
and
TotalHours = [WeeklyHours]+[DailyHours]+[WorkHours]
sorry I did not do it quite right but I had to adjust for
the possible hour start being later than hour finish.
Hope this helps.
Fons
-----Original Message-----
Yes I have reviewed it, here is an sql for a query:
SELECT DateHours.*, DateDiff("h",[DateStart], [DateFinish])
AS RawHours, Int([RawHours]/168)*40 AS WeeklyHours, Int
(([RawHours]-([WeeklyHours]/40)*168)/24)*8 AS DailyHours,
[RawHours]-([WeeklyHours]/40*168)-([DailyHours]/8*24) AS
WorkHours, [WeeklyHours]+[DailyHours]+[WorkHours] AS
TotalHours
FROM DateHours;
All on one line.
DateHours is the table I used with the variables DateStart
and DateFinish. So:
RawHours = DateDiff("h",[DateStart],[DateFinish])
WeeklyHours = Int([RawHours]/168)*40
DailyHours = Int(([RawHours]-([WeeklyHours]/40)*168)/24) *8
WorkHours = [RawHours]-([WeeklyHours]/40*168)-
([DailyHours]/8*24)
and
TotalHours = [WeeklyHours]+[DailyHours]+[WorkHours]
sorry I did not do it quite right from scratch.
Hope this helps.
Fons
-----Original Message-----
Can you show some sample data and some sample results?
Fons
-----Original Message-----
Your code does not work as intended. I get negative signs
on some of the calculations.
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating working days between dates 1
Calculating # of working days between dates 2
business days 6
Passing Fields to Module for Calculation in Query 2
Working Days 4
adding work days to a begin date to derive the end date 1
Calculate # of Business Days 1
Using a module in a query 4

Top