Post reply

Calculate working hours on shift timings

  • May 12, 2008 at 4:06 am

    #70364

    dear Gurus / Masters

    All I need is to calculate working hrs based on the in and out timings of the employees.

    I am using MS Access - VBA for the application

    my table is having following fields

    1) mydate - date/time - working day

    2) empcd - text(6) - employee ID

    3) shift_no - text(3) - "1ST", "2ND", "3RD" etc the start time and end times are different and kept in different table ie. COM_SHIFTM

    4) in_time - date/time field - inputing in time of the employee

    5) out_time - date/time field - inputing out time of the employee

    Now from the above tables, I wanted to calculate following

    a) calculate total hrs worked by employee for the day for e.g. data as follows

    1/5/2008,E00001,1ST,7:00 AM,15:30 PM

    1/5/2008,E00002,1ST,7:15 AM,15:25 PM

    1/5/2008,E00003,1ST,7:45 AM,15:45 PM

    1/5/2008,E00004,2ST,12:30 PM,20:30 PM

    1/5/2008,E00005,2ST,12:30 PM,20:45 PM

    1/5/2008,E00006,3RD,22:30 PM,7:30 AM

    1/5/2008,E00007,3RD,22:45 PM,7:00 AM

    Please note the above data for example, problem is 3rd shift it starts at midnight and ends next day morning.. I can not calculate the total working hours for all the cases....

    please help me in this regard

    b rgds

    sunil joshi

    Mumbai, India

  • May 12, 2008 at 4:28 am

    #813678

    You can if the in and out times are truly date times...

    Please post some of the input data and the real table schema. Please see the URL in my signature for how to do that.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 12, 2008 at 4:34 am

    #813679

    Here is one example:

    DECLARE @Date1 DATETIME, @Date2 DATETIME

    SET @Date1 = '2008-05-09 15:59'

    SET @Date2 = '2008-05-11 16:32'

    SELECT convert(varchar(5),DATEDIFF(minute, @Date1, @Date2)/60) + ':' + convert(varchar(2),datediff(minute, @Date1, @Date2)%60)

    Hope this will help you to solve your problem.

  • May 13, 2008 at 4:26 am

    #814321

    Having a true datetime column would indeed be much easier. However assuming that shifts don't last more than 24 hours, you could calculate the working minutes using the following expression:

    WorkingMinutes: DateDiff("n",[Table1]![in_time],[Table1]![out_time])

    + IIf(DateDiff("n",[Table1]![in_time],[Table1]![out_time])<0,24*60,0)

  • May 13, 2008 at 8:19 am

    #814503

    Just DATEADD the two columns together and then do the simple math.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 14, 2008 at 4:32 am

    #815026

    Thanks everybody for the prompt help. it will definatly helped me a lot.

    However, Please note that the text file format is having separate columns for "attendance date", "In Time" and "Out Time"

    I have to have club these fields and then calculate the working hours.

    I am trying the suggessitions you all have given.

    Please see the table schema as under

    Table name : "ATT_IMPMST" (without quotes)

    ATT_DATE - date/time field (time factor is omitted)

    ATT_EMP_CODE : text (6)

    ATT_SHIFT_NO : text (3)

    ATT_IN_TIME : date/time (only time factor)

    ATT_OUT_TIME : date/time (only time factor)

    CAL_HRS : text (to be calculated as working hrs for the day)

    I am importing "TimeSheet.txt" file into the above table.

    Plesae help me more if possible.

    b rgds

    sunil joshi (9930580082) (Mumbai, India)

  • May 14, 2008 at 4:36 am

    #815028

    I wanted to calrify more that

    the "TimeSheet.txt" file is "Comma" delimited file and having following data for e.g.

    Text File : TimeSheet.txt (first row are the field names I have taken in my ATT_IMPMST table)

    ATT_DATE,ATT_EMP_CODE,ATT_SHIF_NO,IN_TIME,OUT_TIME

    1/5/2008,E00001,1ST,7:00,15:00

    1/5/2008,E00002,1ST,7:05,15:30

    1/5/2008,E00003,1ST,7:10,15:35

    B rgds

    Sunil Joshi (9930580082) (Mumbai, India)

  • May 14, 2008 at 5:10 am

    #815043

    the following executed perfectly....! thanks everybody helped me in the context....thanks once again.. expecially xnthos...

    DateDiff("n",[Table1]![in_time],[Table1]![out_time])

    + IIf(DateDiff("n",[Table1]![in_time],[Table1]![out_time])<0,24*60,0)

    b rgds

    sunil joshi (9930580082) (Mumbai, India)

  • May 14, 2008 at 5:14 am

    #815047

    You're welcome

  • December 17, 2008 at 9:16 pm

    #914284

    This worked for me, I was looking to calculate total flight time.

    Thank you, Thank you, Thank you

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply