Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Count Working hour between time

Discussion in 'Information Technology' started by haviv, Jul 28, 2006.

  1. haviv

    haviv
    Expand Collapse
    Guest

    Hai,

    I am in the middle creating report in access to count working turn time. I
    have a series raw data consist of three coloum

    1. User
    2. Time and Date of Start working project
    3. Time and Date of finished working project
    4. Working hour from 08.00 - 17.00

    The issue here the user can start at anytime he like and finish at any time
    he like. But i need to count how many hours have he use to finish the
    project. I can create a logic to count. Issue here i have to be able create a
    logic or function than can cater below possible event


    Event one
    If project starts at 07.45 and finish at 08.45 then the turn time will be 45
    minute
    (08.45 minus 08.00)

    Event two
    If the projects starts at 08.45 and finish at 09.45 the turn time will be 1
    hour
    (09.45 minus 08.45)

    Event three
    If the project starts at 16:00 and finish at 18:00 the turn time will be 2
    hour
    (18.00 minus 16.00)

    Event four
    If the project starts at 16:00 today and finish at 09:00 the next day the
    turn time will be 2 hour as
    (17.00 minus 16.00 plus 09.00 minus 08.00)

    Event five
    If the project starts at 16:00 today and finish at 09:00 the next 2 day the
    turn time will be 10 hour
    (17.00 minus 16.00 plus 17.00 minus 08.00 plus 09.00 minus 08.00)

    You see i need a logic can cater all event the tricky part is in the even
    three and four which hard to be diffrentiate

    Please advise if any of you have a sample of project of a sample of logic
    that can cater all above event

    Sincerely Yours
    Haviv
     
  2. Loading...

    Similar Threads Forum Date
    Ashdoc's Movie Review---The Accountant Theatre, Movies & Cinema Oct 21, 2016
    Gurmat Vichaar | Futility Of Counting Paaths | April 12, 2016 Gurmat Vichaar Apr 23, 2016
    wolf count Blogs Oct 17, 2015
    recount Blogs Oct 17, 2015
    Events Banda Singh Bahadur's Martyrdom - An Eyewitness Account History of Sikhism Mar 26, 2015

  3. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    haviv wrote:
    > Hai,
    >
    > I am in the middle creating report in access to count working turn time. I
    > have a series raw data consist of three coloum
    >
    > 1. User
    > 2. Time and Date of Start working project
    > 3. Time and Date of finished working project
    > 4. Working hour from 08.00 - 17.00
    >
    > The issue here the user can start at anytime he like and finish at any time
    > he like. But i need to count how many hours have he use to finish the
    > project. I can create a logic to count. Issue here i have to be able create a
    > logic or function than can cater below possible event
    >
    >
    > Event one
    > If project starts at 07.45 and finish at 08.45 then the turn time will be 45
    > minute
    > (08.45 minus 08.00)
    >
    > Event two
    > If the projects starts at 08.45 and finish at 09.45 the turn time will be 1
    > hour
    > (09.45 minus 08.45)
    >
    > Event three
    > If the project starts at 16:00 and finish at 18:00 the turn time will be 2
    > hour
    > (18.00 minus 16.00)
    >
    > Event four
    > If the project starts at 16:00 today and finish at 09:00 the next day the
    > turn time will be 2 hour as
    > (17.00 minus 16.00 plus 09.00 minus 08.00)
    >
    > Event five
    > If the project starts at 16:00 today and finish at 09:00 the next 2 day the
    > turn time will be 10 hour
    > (17.00 minus 16.00 plus 17.00 minus 08.00 plus 09.00 minus 08.00)
    >
    > You see i need a logic can cater all event the tricky part is in the even
    > three and four which hard to be diffrentiate
    >
    > Please advise if any of you have a sample of project of a sample of logic
    > that can cater all above event
    >
    > Sincerely Yours
    > Haviv


    Try the information in the following thread:

    http://groups.google.com/group/comp...556dde02b88/44ed54b162a1aa3c#44ed54b162a1aa3c

    James A. Fortune
    MPAPoster@FortuneJames.com
     
  4. Ron2006

    Ron2006
    Expand Collapse
    Guest

    If you starting time can NEVER be after 17:00 nor before 8:00
    AND ending has the same constraint
    AND the field 2 can never be greater than field 3.

    Then if the start date = end date (just the date part) then the
    subtract field 2 from field 3

    ELSE

    workdays = DateDiff("d",[StartDate],[EndDate]) - 1 (you want to use
    ONLY the date part of the fields)
    hours = workdays * 9
    hours2 = #17:00# - datepart ("h",field2)
    Hours2 = hours2 + (datepart ("h",field3) - #8:00#
    Hours = hours + hours2

    The above is basically true IF you work on weekends and holidays.

    =============================
    If you do not work on weekends then

    WorkDays=DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",[StartDate],[EndDate
    ],7)+DateDiff("ww",[StartDate],[EndDate],1))


    First datediff is total number of days between dates.
    Second datediff is number of Saturdays between dates
    Third datediff is number of Sundays between dates.

    I believe you will also have to subtract 1 from workdays here to get
    the proper count of full days used.

    ============================
    If you do not work on Holidays
    then you will need a table of holidays (the workday that would be taken
    off inplace of the holiday - Monday if holiday is on Sunday)

    Then use dCount on that table counting the number of dates in the table
    between date part of field2 and field3 and subtract that from the
    above computed workdays.

    ===========================

    Ron


    James A. Fortune wrote:
    > haviv wrote:
    > > Hai,
    > >
    > > I am in the middle creating report in access to count working turn time. I
    > > have a series raw data consist of three coloum
    > >
    > > 1. User
    > > 2. Time and Date of Start working project
    > > 3. Time and Date of finished working project
    > > 4. Working hour from 08.00 - 17.00
    > >
    > > The issue here the user can start at anytime he like and finish at any time
    > > he like. But i need to count how many hours have he use to finish the
    > > project. I can create a logic to count. Issue here i have to be able create a
    > > logic or function than can cater below possible event
    > >
    > >
    > > Event one
    > > If project starts at 07.45 and finish at 08.45 then the turn time will be 45
    > > minute
    > > (08.45 minus 08.00)
    > >
    > > Event two
    > > If the projects starts at 08.45 and finish at 09.45 the turn time will be 1
    > > hour
    > > (09.45 minus 08.45)
    > >
    > > Event three
    > > If the project starts at 16:00 and finish at 18:00 the turn time will be 2
    > > hour
    > > (18.00 minus 16.00)
    > >
    > > Event four
    > > If the project starts at 16:00 today and finish at 09:00 the next day the
    > > turn time will be 2 hour as
    > > (17.00 minus 16.00 plus 09.00 minus 08.00)
    > >
    > > Event five
    > > If the project starts at 16:00 today and finish at 09:00 the next 2 day the
    > > turn time will be 10 hour
    > > (17.00 minus 16.00 plus 17.00 minus 08.00 plus 09.00 minus 08.00)
    > >
    > > You see i need a logic can cater all event the tricky part is in the even
    > > three and four which hard to be diffrentiate
    > >
    > > Please advise if any of you have a sample of project of a sample of logic
    > > that can cater all above event
    > >
    > > Sincerely Yours
    > > Haviv

    >
    > Try the information in the following thread:
    >
    > http://groups.google.com/group/comp...556dde02b88/44ed54b162a1aa3c#44ed54b162a1aa3c
    >
    > James A. Fortune
    > MPAPoster@FortuneJames.com
     
  5. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    Ron2006 wrote:
    > If you starting time can NEVER be after 17:00 nor before 8:00
    > AND ending has the same constraint
    > AND the field 2 can never be greater than field 3.
    >
    > Then if the start date = end date (just the date part) then the
    > subtract field 2 from field 3
    >
    > ELSE
    >
    > workdays = DateDiff("d",[StartDate],[EndDate]) - 1 (you want to use
    > ONLY the date part of the fields)
    > hours = workdays * 9
    > hours2 = #17:00# - datepart ("h",field2)
    > Hours2 = hours2 + (datepart ("h",field3) - #8:00#
    > Hours = hours + hours2
    >
    > The above is basically true IF you work on weekends and holidays.
    >
    > =============================
    > If you do not work on weekends then
    >
    > WorkDays=DateDiff("d",[StartDate],[EndDate])-(DateDiff("ww",[StartDate],[EndDate
    > ],7)+DateDiff("ww",[StartDate],[EndDate],1))
    >
    >
    > First datediff is total number of days between dates.
    > Second datediff is number of Saturdays between dates
    > Third datediff is number of Sundays between dates.
    >
    > I believe you will also have to subtract 1 from workdays here to get
    > the proper count of full days used.
    >
    > ============================
    > If you do not work on Holidays
    > then you will need a table of holidays (the workday that would be taken
    > off inplace of the holiday - Monday if holiday is on Sunday)
    >
    > Then use dCount on that table counting the number of dates in the table
    > between date part of field2 and field3 and subtract that from the
    > above computed workdays.
    >
    > ===========================
    >
    > Ron
    >
    >
    > James A. Fortune wrote:
    >
    >>haviv wrote:
    >>
    >>>Hai,
    >>>
    >>>I am in the middle creating report in access to count working turn time. I
    >>>have a series raw data consist of three coloum
    >>>
    >>>1. User
    >>>2. Time and Date of Start working project
    >>>3. Time and Date of finished working project
    >>>4. Working hour from 08.00 - 17.00
    >>>
    >>>The issue here the user can start at anytime he like and finish at any time
    >>>he like. But i need to count how many hours have he use to finish the
    >>>project. I can create a logic to count. Issue here i have to be able create a
    >>>logic or function than can cater below possible event
    >>>
    >>>
    >>>Event one
    >>>If project starts at 07.45 and finish at 08.45 then the turn time will be 45
    >>>minute
    >>>(08.45 minus 08.00)
    >>>
    >>>Event two
    >>>If the projects starts at 08.45 and finish at 09.45 the turn time will be 1
    >>>hour
    >>>(09.45 minus 08.45)
    >>>
    >>>Event three
    >>>If the project starts at 16:00 and finish at 18:00 the turn time will be 2
    >>>hour
    >>>(18.00 minus 16.00)
    >>>
    >>>Event four
    >>>If the project starts at 16:00 today and finish at 09:00 the next day the
    >>>turn time will be 2 hour as
    >>>(17.00 minus 16.00 plus 09.00 minus 08.00)
    >>>
    >>>Event five
    >>>If the project starts at 16:00 today and finish at 09:00 the next 2 day the
    >>>turn time will be 10 hour
    >>>(17.00 minus 16.00 plus 17.00 minus 08.00 plus 09.00 minus 08.00)
    >>>
    >>>You see i need a logic can cater all event the tricky part is in the even
    >>>three and four which hard to be diffrentiate
    >>>
    >>>Please advise if any of you have a sample of project of a sample of logic
    >>>that can cater all above event
    >>>
    >>>Sincerely Yours
    >>>Haviv

    >>
    >>Try the information in the following thread:
    >>
    >>http://groups.google.com/group/comp...556dde02b88/44ed54b162a1aa3c#44ed54b162a1aa3c
    >>
    >>James A. Fortune
    >>MPAPoster@FortuneJames.com

    >
    >


    I see that I read the post too quickly. Let me know if Ron's advice is
    not enough.

    James A. Fortune
    MPAPoster@FortuneJames.com
     
  6. Guest

    Guest
    Expand Collapse
    Guest

    Here is a function that does what you want:

    '***********************************************************
    Function computeHoursWorked(dtStart As Date, dtStop As Date) As Double
    '***********************************************************
    Const tStart = #8:00:00 AM#
    Const tStop = #5:00:00 PM#

    ' reset times to valid start or stop time, use minutes to allow for
    partial hours
    ' must be between 0800 and 1700 inclusive (uses minutes to allow for
    part hours)
    If TimeValue(dtStart) < tStart Then dtStart = DateAdd("n", Hour(tStart)
    * 60 + Minute(tStart), DateValue(dtStart))
    If TimeValue(dtStart) > tStop Then dtStart = DateAdd("n", Hour(tStop) *
    60 + Minute(tStop), DateValue(dtStart))
    If TimeValue(dtStop) < tStart Then dtStop = DateAdd("n", Hour(tStart) *
    60 + Minute(tStart), DateValue(dtStop))
    If TimeValue(dtStop) > tStop Then dtStop = DateAdd("n", Hour(tStop) *
    60 + Minute(tStop), DateValue(dtStop))

    'compute difference and subtract extra invalid hours (eg. hours between
    1700 and 0800)
    ' = overall hours difference between start and stop - invalid hours for
    each complete day spanned (uses minutes to allow for part hours)
    computeHoursWorked = DateDiff("n", dtStart, dtStop) / 60 - ((24 -
    (DateDiff("n", tStart, tStop) / 60)) * DateDiff("d", dtStart, dtStop))

    End Function

    It can be stored in a module and referenced as part of a query:

    e.g: SELECT User, dtStart, dtStop, ComputeHoursWorked([dStart],[dStop]) AS
    HrsWorked FROM tbl_TimeCards;

    You couild also reference it directly in a report:

    e.g. = ComputeHoursWorked([dStart],[dStop]) ....... (where dStart
    and dStop are valid fields in the report)

    You would have to modify it to take into consideration Weekends or give
    results in minutes instead of hours.


    Brian

    "haviv" <haviv@discussions.microsoft.com> wrote in message
    news:A42D4520-6C08-4F3A-95F6-2D32D21A75DB@microsoft.com...
    > Hai,
    >
    > I am in the middle creating report in access to count working turn time. I
    > have a series raw data consist of three coloum
    >
    > 1. User
    > 2. Time and Date of Start working project
    > 3. Time and Date of finished working project
    > 4. Working hour from 08.00 - 17.00
    >
    > The issue here the user can start at anytime he like and finish at any
    > time
    > he like. But i need to count how many hours have he use to finish the
    > project. I can create a logic to count. Issue here i have to be able
    > create a
    > logic or function than can cater below possible event
    >
    >
    > Event one
    > If project starts at 07.45 and finish at 08.45 then the turn time will be
    > 45
    > minute
    > (08.45 minus 08.00)
    >
    > Event two
    > If the projects starts at 08.45 and finish at 09.45 the turn time will be
    > 1
    > hour
    > (09.45 minus 08.45)
    >
    > Event three
    > If the project starts at 16:00 and finish at 18:00 the turn time will be 2
    > hour
    > (18.00 minus 16.00)
    >
    > Event four
    > If the project starts at 16:00 today and finish at 09:00 the next day the
    > turn time will be 2 hour as
    > (17.00 minus 16.00 plus 09.00 minus 08.00)
    >
    > Event five
    > If the project starts at 16:00 today and finish at 09:00 the next 2 day
    > the
    > turn time will be 10 hour
    > (17.00 minus 16.00 plus 17.00 minus 08.00 plus 09.00 minus 08.00)
    >
    > You see i need a logic can cater all event the tricky part is in the even
    > three and four which hard to be diffrentiate
    >
    > Please advise if any of you have a sample of project of a sample of logic
    > that can cater all above event
    >
    > Sincerely Yours
    > Haviv
     

Share This Page