date time calculations

[omegads]

Good day to all hope this can go here.. and if not could you please redirect me to were i need to put it. thanks, i'm new in access and i have little experience on it, but i can manage to do simple tasks so i don't get the tipicall bullying from users that are "GODS" of access.. is pretty lame from their sides.

Ok here's my situation. i assign a project to my personnel, he inputs START date and time and then when he finishes it he inputs END date and time, after this i need to calculate how many hours it took to accomplish the task.

i'm using queries for the calculations and right now i have this.

Total_Time: Round(([Date finished]-[Date Started])*24,2) once i run the query it works and returns me this





if the assignment was finished the same day i have no problem, i just rest 1 hour from lunch break but the problems comes when it's finished one or more days ahead like in the second, third and fourth row from the picture above... hours you get an exaggerated amount of time.

can someone help me out to see how i can remove the excessive time from non working hours (remove from 5.00pm to 7.00am of the next day and saturday/sunday) in the calculation so i can have a more specific time

[aytee111]

You are going to need a VBA function for these calculations.

Try this: http://access.mvps.org/access/datetime/index.html

[omegads]

thank you, will look into it.

Regards

[ssanfu]

FYI, you should never use spaces in object names, only letters and numbers (exception is the underscore).


I found a routine at this site:
http://visualbasic.ittoolbox.com/gro...-dates-3300699

I modified the code to return hours worked between 7am and 5pm, not including Sat or Sun. It does not check for holidays.
Paste the following into a module (do not name the module "HCalc"!).
in the query you would have a column:

Total_Time: HCalc([Date Started],[Date finished])

Public Function HCalc(DateST As Date, DateEN As Date) As Double
    Const StartTime As Date = #7:00:00 AM#  'start time of work day
    Const EndTime As Date = #5:00:00 PM#   ' end time of work day

    Dim StDate As Date
    Dim StDateD As Date
    Dim StDateT As Date
    Dim EnDate As Date
    Dim EnDateD As Date
    Dim EnDateT As Date
    Dim Result As Double
    Dim ResultS As Double
    Dim ResultE As Double
    Dim MinDay As Double
    Dim RegCh As Boolean

    If IsNull(DateST) Or IsNull(DateEN) or Format(DateST, "Short Time") < StartTime Or Format(DateEN, "Short Time") > EndTimeThen
        HCalc = 0
    Else
        StDate = CDate(DateST)
        EnDate = CDate(DateEN)
        '
        ' Extract the date portion of the two timestamps
        '
        StDateD = CDate(Format(StDate, "Short Date"))
        EnDateD = CDate(Format(EnDate, "Short Date"))
        '
        ' If start and end are the same date, the result is the difference in minutes
        '
        If StDateD = EnDateD Then
            Result = DateDiff("n", StDate, EnDate)
        Else
            '
            MinDay = DateDiff("n", StartTime, EndTime)
            '
            ' Extract the time portion of the two timestamps
            '
            StDateT = Format(StDate, "Short Time")
            EnDateT = Format(EnDate, "Short Time")
            '
            ' Next, we calculate the minutes for the first (partial day)
            ResultS = DateDiff("n", StDateT, EndTime)
            Debug.Print Weekday(StDateD), StDateD, ResultS, ResultS, "Start day"
            Result = ResultS    '+ ResultE

            ' Adjust the starting date forward by 1. If the difference between the two dates
            ' was only 1 day, we have already calculated the contributions of the first and
            ' last dates, and we won't execute the following loops.
            StDateD = DateAdd("d", 1, StDateD)
            '                EnDateD = DateAdd("d", -1, EnDateD)

            ' We walk through the dates in between
            Do Until StDateD = EnDateD
                '
                ' We want to skip Saturdays (7) and Sundays (1)
                If (Weekday(StDateD) > 1) And (Weekday(StDateD) < 7) Then
                    '
                    ' We always add the value for a day
                    Result = Result + MinDay
                    Debug.Print Weekday(StDateD), StDateD, MinDay, Result
                    '
                Else
                    Debug.Print Weekday(StDateD), StDateD, 0, Result
                End If
                '
                ' Increment the date - we do this AFTER the rest of the loop because we already
                ' did the first increment outside the loop. Because we will fall out of the outer
                ' loop after the last increment, we won't add anything for the last day (which
                ' was already handled before the loop).
                '
                StDateD = DateAdd("d", 1, StDateD)
            Loop

        End If
        '
        ' Convert the total minutes to hours and round to the nearest hundredth
        '
        ' Next, we calculate the minutes for the last (partial day)
        ResultE = DateDiff("n", StartTime, EnDateT)
        Result = Result + ResultE
        Debug.Print Weekday(EnDateD), EnDateD, ResultE, Result, "End Day"

        HCalc = Round(Result / 60, 2)
    End If
End Function

[omegads]

Oh my goooddd D:...... thank you very much, i will look into this code and reply back if it worked. another question, i have been told that not to use spaces before, i missed theses two on that rule(don't know why, all other have underscore), could you explain me why?

Thanks again.

Bestr regards

[ssanfu]

1) See http://www.utteraccess.com/wiki/inde...ng_Conventions
Scroll down to the heading/section "Spaces & Special Characters"

2) If you might ever want to upsize to SQL/Oracle/MySQL/etc (aka the big boys), the spaces in object names will require you to do a re-write because the "big boys" do not allow spaces in their names.

3) See http://stackoverflow.com/questions/5...-control-names

[rpeare]

I don't think you need a function to do this, can't you just leave your hour calculation as is, then multiply the difference in days but the number of non-working hours (14 per day) to get the correct amount of hours?

i.e.

(([Date finished]-[Date Started])*24) - (datediff("d", [Date Started], [Date Ended]) * 14)

so for your row 2

This part: (([Date finished]-[Date Started])*24)
Gives you 20.33

This part: (datediff("d", [Date Started], [Date Ended]) * 14)
Should give you 14

The final value should be 6.33 hours

If the start date and end date are the same value the second half of the calculation would return 0 and therefore no time would be subtracted.

From there it may get more tricky if you have to take out weekends etc. that's when you'd want to use the function I think

[ssanfu]

That is slick!
Learn new things every day.

But the OP did have the "No weekends" requirement.

can someone help me out to see how i can remove the excessive time from non working hours (remove from 5.00pm to 7.00am of the next day and saturday/sunday) in the calculation so i can have a more specific time

Still, it is a slick way of calculating elapsed time. (added to my library)

[rpeare]

You're right of course, and I missed it but I was wondering about it in the last couple of days and you could try this:

this was my test table:

tblTest
DateStarted  DateEnded

I filled it with the dates in your example (the first five rows)

This was my query:

SELECT tblTest.DateStarted, tblTest.DateEnded, (([dateended]-[datestarted])*24)-(DateDiff("d",[datestarted],[dateended])*14)-((Int(DateDiff("d",[datestarted],[dateended])/7))*48)-((IIf([weekportion]>0 And Weekday([datestarted])>Weekday([dateended]),1,0))*48) AS CalculatedTime, ([dateended]-[datestarted])*24 AS RawTime, DateDiff("d",[datestarted],[dateended])*14 AS DeductNonWorking, [weekdiff]*48 AS DeductWeekend1, [crossesweekend]*48 AS DeductWeekend2, DateDiff("d",[datestarted],[dateended]) AS DayDiff, Int(DateDiff("d",[datestarted],[dateended])/7) AS WeekDiff, [daydiff] Mod 7 AS WeekPortion, IIf([weekportion]>0 And Weekday([datestarted])>Weekday([dateended]),1,0) AS CrossesWeekendFROM tblTest;

I left all the 'sub' calculations in there so you could see how I put the final formula together. I'm curious if it works, nothing more. Though it still would not account for holidays.