Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 29

Thread: Code Required to calculate number of days worked, normal overtime and holiday overtime

  1. #11
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    BEFORE ( “File1 Before.xlsx” ) data range for the first worksheet “121”.
    (as in Post #6
    http://www.excelfox.com/forum/showth...0068#post10068
    )


    Using Excel 2007 32 bit
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    SOVEL JOY
    121
    TEAM LEADER
    21.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    2
    SOVEL JOY
    121
    TEAM LEADER
    22.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    3
    SOVEL JOY
    121
    TEAM LEADER
    23.Dec.16
    7:00
    15:00
    8:00
    9:00
    0:00
    4
    SOVEL JOY
    121
    TEAM LEADER
    24.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    5
    SOVEL JOY
    121
    TEAM LEADER
    25.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    6
    SOVEL JOY
    121
    TEAM LEADER
    26.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    7
    SOVEL JOY
    121
    TEAM LEADER
    27.Dec.16
    7:00
    17:00
    10:00
    9:00
    1:00
    8
    SOVEL JOY
    121
    TEAM LEADER
    28.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    9
    SOVEL JOY
    121
    TEAM LEADER
    29.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    10
    SOVEL JOY
    121
    TEAM LEADER
    30.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    11
    SOVEL JOY
    121
    TEAM LEADER
    31.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    12
    SOVEL JOY
    121
    TEAM LEADER
    1.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    13
    SOVEL JOY
    121
    TEAM LEADER
    2.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    14
    SOVEL JOY
    121
    TEAM LEADER
    3.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    15
    SOVEL JOY
    121
    TEAM LEADER
    4.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    16
    SOVEL JOY
    121
    TEAM LEADER
    5.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    17
    SOVEL JOY
    121
    TEAM LEADER
    6.Jan.17
    18
    SOVEL JOY
    121
    TEAM LEADER
    7.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    19
    SOVEL JOY
    121
    TEAM LEADER
    8.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    20
    SOVEL JOY
    121
    TEAM LEADER
    9.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    21
    SOVEL JOY
    121
    TEAM LEADER
    10.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    22
    SOVEL JOY
    121
    TEAM LEADER
    11.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    23
    SOVEL JOY
    121
    TEAM LEADER
    12.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    24
    SOVEL JOY
    121
    TEAM LEADER
    13.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    25
    SOVEL JOY
    121
    TEAM LEADER
    14.Jan.17
    7:30
    17:30
    10:00
    9:00
    1:00
    26
    SOVEL JOY
    121
    TEAM LEADER
    15.Jan.17
    7:30
    17:30
    10:00
    9:00
    1:00
    27
    SOVEL JOY
    121
    TEAM LEADER
    16.Jan.17
    7:30
    17:30
    10:00
    9:00
    1:00
    28
    SOVEL JOY
    121
    TEAM LEADER
    17.Jan.17
    7:30
    17:30
    10:00
    9:00
    1:00
    29
    SOVEL JOY
    121
    TEAM LEADER
    18.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    30
    SOVEL JOY
    121
    TEAM LEADER
    19.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    31
    SOVEL JOY
    121
    TEAM LEADER
    20.Jan.17
    Worksheet: 121
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  2. #12
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Output data range ( AFTER ) for worksheet “121” after running either of the codes
    Sub IJAdjustTotalAllWorksheet()
    Or
    Sub IJAdjustKAddTotalAllWorksheet()




    Using Excel 2007 32 bit
    Row\Col
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    1
    SOVEL JOY
    121
    TEAM LEADER
    21.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    2
    SOVEL JOY
    121
    TEAM LEADER
    22.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    3
    SOVEL JOY
    121
    TEAM LEADER
    23.Dec.16
    7:00
    15:00
    8:00
    8:00
    H
    4
    SOVEL JOY
    121
    TEAM LEADER
    24.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    5
    SOVEL JOY
    121
    TEAM LEADER
    25.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    6
    SOVEL JOY
    121
    TEAM LEADER
    26.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    7
    SOVEL JOY
    121
    TEAM LEADER
    27.Dec.16
    7:00
    17:00
    10:00
    9:00
    1:00
    N
    8
    SOVEL JOY
    121
    TEAM LEADER
    28.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    9
    SOVEL JOY
    121
    TEAM LEADER
    29.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    10
    SOVEL JOY
    121
    TEAM LEADER
    30.Dec.16
    7:00
    18:00
    11:00
    10:00
    H
    11
    SOVEL JOY
    121
    TEAM LEADER
    31.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    12
    SOVEL JOY
    121
    TEAM LEADER
    1.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    13
    SOVEL JOY
    121
    TEAM LEADER
    2.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    14
    SOVEL JOY
    121
    TEAM LEADER
    3.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    15
    SOVEL JOY
    121
    TEAM LEADER
    4.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    16
    SOVEL JOY
    121
    TEAM LEADER
    5.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    17
    SOVEL JOY
    121
    TEAM LEADER
    6.Jan.17
    H
    18
    SOVEL JOY
    121
    TEAM LEADER
    7.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    19
    SOVEL JOY
    121
    TEAM LEADER
    8.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    20
    SOVEL JOY
    121
    TEAM LEADER
    9.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    21
    SOVEL JOY
    121
    TEAM LEADER
    10.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    22
    SOVEL JOY
    121
    TEAM LEADER
    11.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    23
    SOVEL JOY
    121
    TEAM LEADER
    12.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    24
    SOVEL JOY
    121
    TEAM LEADER
    13.Jan.17
    7:00
    18:00
    11:00
    10:00
    H
    25
    SOVEL JOY
    121
    TEAM LEADER
    14.Jan.17
    7:30
    17:30
    10:00
    9:00
    1:00
    N
    26
    SOVEL JOY
    121
    TEAM LEADER
    15.Jan.17
    7:30
    17:30
    10:00
    9:00
    1:00
    N
    27
    SOVEL JOY
    121
    TEAM LEADER
    16.Jan.17
    7:30
    17:30
    10:00
    9:00
    1:00
    N
    28
    SOVEL JOY
    121
    TEAM LEADER
    17.Jan.17
    7:30
    17:30
    10:00
    9:00
    1:00
    N
    29
    SOVEL JOY
    121
    TEAM LEADER
    18.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    30
    SOVEL JOY
    121
    TEAM LEADER
    19.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    31
    SOVEL JOY
    121
    TEAM LEADER
    20.Jan.17
    H
    Worksheet: 121
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  3. #13
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Output values and formulas for output range for Worksheet "121" after running second code
    Sub IJAdjustKAddTotalAllWorksheet()
    ( Second code is here: http://www.excelfox.com/forum/showth...0072#post10072




    Output values as seen in the spreadsheet:Using Excel 2007 32 bit
    Row\Col
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    33
    34
    TOTAL NO. OF DAYS -----> 30
    Normal Overtime ----->
    Holiday Overtime ----->
    35
    47
    28
    36
    37
    For all Month no. of days we take as 30 only
    Worksheet: 121





    Formula string values ( as seen in the Formula Bar ) :Using Excel 2007 32 bit
    Row\Col
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    33
    34
    TOTAL NO. OF DAYS -----> 30
    Normal Overtime ----->
    Holiday Overtime ----->
    35
    =SUMIF(K1:K31,"N",J1:J31)*24
    =SUMIF(K1:K31,"H",J1:J31)*24
    36
    37
    For all Month no. of days we take as 30 only
    Worksheet: 121




    There you go . lots for you to try

    before you tell us you wanted something different !

    वहाँ आप का प्रयास करने के लिए बहुत सारे हैं।

    संभवत: आप कुछ अलग चाहते हो सकता है



    Alan
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  4. #14
    Junior Member
    Join Date
    Jan 2017
    Posts
    9
    Rep Power
    0
    Dear Alan,

    The code seems to be working fine..i need normal OT and Holiday OT to appear exactly in cell G34 and J34
    Can i use the same code for every month and if yes what changes i need to do from my end
    I have around 800 sheets ...will the code work on all sheets in one go

    Thanks,

    Nelson

  5. #15
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10

    Further to the TWO codes you now have from US .......... :)

    Hi Nelson,
    Quote Originally Posted by nelsondavid View Post
    ..i need normal OT and Holiday OT to appear exactly in cell G34 and J34 ...
    I think you can see what part of the code determines where the output goes
    Code:
    '3f) Paste out final Totals and days to current Worksheet
         Let wsStear.Range("G34").Value = NOHrsV2 * 24 'Normal Overtime is held in Array as fraction of a day
         Let wsStear.Range("J34").Value = HOHrsV2 * 24 'Holiday Overtime is held in Array as fraction of a day
         Let wsStear.Range("C34").Value = TDays ' The constant value of Total days is simply added to cell C34
    '3g) Normal   Hrs  ( Column I ) and Overtime Hrs ( Column J ) are  changed
    Code:
    '3f) Paste out final Totals formulas and days to current Worksheet
         Let wsStear.Range("G35").Value = "=SUMIF(K1:K" & lr & ",""N"",J1:J" & lr & ")*24"
         Let wsStear.Range("J35").Value = "=SUMIF(K1:K" & lr & ",""H"",J1:J" & lr & ")*24"
         Let wsStear.Range("C34").Value = TDays ' The constant value of Total days is simply added to cell C34
    '3g) Normal   Hrs  ( Column I ) and Overtime Hrs ( Column J ) are  changed ' And can paste out help column if you like
    I am sure you can understand how to modify to change which cells are used for where the total appear
    _............................................


    Quote Originally Posted by nelsondavid View Post
    .... Can i use the same code for every month ...
    Yes..
    Quote Originally Posted by nelsondavid View Post
    . if yes what changes i need to do from my end..
    ..The code is not in any way dependant on any month information......There is no reason that I can see to need to change anything..
    Quote Originally Posted by nelsondavid View Post
    ....what changes i need to do from my end..
    You should not need to change your end, leave it alone, at least as far as any effect on the Months are concerned
    _................................................. .......


    Quote Originally Posted by nelsondavid View Post
    ...I have around 800 sheets ...will the code work on all sheets in one go...
    The code is designed to work on multiple worksheets.
    Obviously I only tested it with two.
    If I were you, I would test it first with a larger amount, 10, 30, 60, .100 .......etc.. then finally 800 etc.


    _................................................. ...

    In my opinion, it is very important that you try to understand the code...

    Alan.


    P.s.
    Quote Originally Posted by nelsondavid View Post
    ..The code seems to be working fine..
    We have given you 2 codes
    From Alan
    http://www.excelfox.com/forum/showth...age2#post10066

    From Alan and Admin
    http://www.excelfox.com/forum/showth...age2#post10072
    Last edited by DocAElstein; 02-06-2017 at 03:43 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  6. #16
    Junior Member
    Join Date
    Jan 2017
    Posts
    9
    Rep Power
    0
    Dear Nelson,

    The code working fine , but have two problems 1)It calculates the number of days worked as 30 even if the person absent for one or two days

    2)I use column K for remarks which you are currently using to identify the days

    Sample attached

    Thanks,

    Nelson
    Attached Files Attached Files

  7. #17
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Quote Originally Posted by nelsondavid View Post
    The code working fine , but have two problems 1)It calculates the number of days worked as 30 even if the person absent for one or two days...
    Hi Nelson
    I have 5 questions ( _1a) 1b) 2a) 2b) 3) ) for you:
    _1) you told me that days are always 30 ??
    _1a) How do you now want Days calculated? ?????

    ( This is a new requirement I expect? Your original Before had no entries in column
    _1b) Is the requirement for taking into account absence new? ( or did you forget it originally) ?? )



    _2a) Which code are you using ?????
    ( You have 2
    Quote Originally Posted by DocAElstein View Post
    .......
    P.s.
    We have given you 2 codes
    From Alan
    Post #19 here: http://www.excelfox.com/forum/showth...age2#post10066

    From Alan and Admin
    Post #20 here: http://www.excelfox.com/forum/showth...age2#post10072

    Which are you using? Which do you prefer?? ????
    _2b) Which do you prefer?? ????



    -3) Are you sure that you do not require a header row ???
    Do you require a header row as in your first post here: File Sample2.xlxs http://www.excelfox.com/forum/showth...0058#post10058

    Header.jpg http://imgur.com/1Dvfv6N
    Header.JPG




    Alan
    Last edited by DocAElstein; 02-06-2017 at 11:24 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  8. #18
    Junior Member
    Join Date
    Jan 2017
    Posts
    9
    Rep Power
    0
    Dear Alan,

    Yes i actually forget to tell you about the absent days.What i mean "Every month we consider of 30 days , if the month is of 30 or 31 or like 28 days Feb all these months we take it as 30 days only.
    Total no. of days is the no. of days the person worked plus the holidays(holiday is deducted if the person does not come the day before and after the holiday)

    Header row not required

    Have attached sample files and the code am using now

    thanks,

    Nelson
    Attached Files Attached Files
    Last edited by nelsondavid; 02-07-2017 at 07:59 AM.

  9. #19
    Fuhrer, Vierte Reich DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    9,270
    Rep Power
    10
    Hi Nelson,
    So I assume you want to use the code that puts formulas in and adds a help column??? ( The second code , Sub IJAdjustKAddTotalAllWorksheet() )
    ( I see you have changed the row of the cells used to display the output to 34 )


    So possibly we can use column L for the help column ?

    _....................


    In your after file, ( "File 2 After.xlsx" ) , you have given 26 Working days for Worksheet “121”:
    Using Excel 2007 32 bit
    Row\Col
    B
    C
    34
    TOTAL NO. OF DAYS -----> 26
    Worksheet: 121


    But this is your before, ( "File 1 Before.xlsx" ):
    Using Excel 2007 32 bit
    Row\Col
    E
    F
    G
    1
    21.Dec.16
    7:00
    1
    18:00
    2
    22.Dec.16
    7:00
    2
    18:00
    3
    23.Dec.16
    7:00
    3
    15:00
    4
    24.Dec.16
    7:00
    4
    18:00
    5
    25.Dec.16
    7:00
    5
    18:00
    6
    26.Dec.16
    7:00
    6
    18:00
    7
    27.Dec.16
    7:00
    7
    17:00
    8
    28.Dec.16
    7:00
    8
    18:00
    9
    29.Dec.16
    10
    30.Dec.16
    11
    31.Dec.16
    12
    1.Jan.17
    7:00
    9
    18:00
    13
    2.Jan.17
    7:00
    10
    18:00
    14
    3.Jan.17
    7:00
    11
    18:00
    15
    4.Jan.17
    7:00
    12
    18:00
    16
    5.Jan.17
    7:00
    13
    18:00
    17
    6.Jan.17
    18
    7.Jan.17
    7:00
    14
    18:00
    19
    8.Jan.17
    7:00
    15
    18:00
    20
    9.Jan.17
    21
    10.Jan.17
    7:00
    16
    18:00
    22
    11.Jan.17
    7:00
    17
    18:00
    23
    12.Jan.17
    7:00
    18
    18:00
    24
    13.Jan.17
    7:00
    19
    18:00
    25
    14.Jan.17
    7:30
    20
    17:30
    26
    15.Jan.17
    7:30
    21
    17:30
    27
    16.Jan.17
    7:30
    22
    17:30
    28
    17.Jan.17
    7:30
    23
    17:30
    29
    18.Jan.17
    7:00
    24
    18:00
    30
    19.Jan.17
    7:00
    25
    18:00
    31
    20.Jan.17
    32
    Worksheet: 121

    Question _1)
    ???? Please explain why you give 26 ??? . I see 25 ????

    _----____________________________________



    Questions 2)
    Quote Originally Posted by nelsondavid View Post
    ......(holiday is deducted if the person does not come the day before and after the holiday).....
    I do not understand
    _2a) Please give example to illustrate this

    _ 2b) (i) What if the above situation arises, and the Holiday is the first or last day of the month ??? - Would you check the month before and after to see if a day was not worked???
    _ 2b) (ii) You will need some external record set at the end of each month which is accessible in the next month to check each sheet for an absence on the last day of all Months.
    _.....



    You must make sure that your test data in Before and After illustrates all possible scenarios( and that it is correct ! )






    Alan
    Last edited by DocAElstein; 02-07-2017 at 10:32 PM.
    ….If you are my competitor, I will try all I can to beat you. But if I do, I will not belittle you. I will Salute you, because without you, I am nothing.
    If you are my enemy, we will try to kick the fucking shit out of you…..
    Winston Churchill, 1939
    Save your Forum..._
    _...KILL A MODERATOR!!

  10. #20
    Junior Member
    Join Date
    Jan 2017
    Posts
    9
    Rep Power
    0
    Dear Alan,

    25 days ABSENT ,i.e, Absent 4 days plus one holiday (4 days) -29,31,9,19 and 30 (the employee did not come on 29 and 31 so 30 holiday marked as absent)

    If Question 2 scenario arises holiday is considered present

    Sample attached

    Thanks,

    Nelson
    Attached Files Attached Files

Similar Threads

  1. Replies: 33
    Last Post: 01-12-2015, 10:08 AM
  2. Replies: 1
    Last Post: 07-23-2013, 10:43 PM
  3. Replies: 0
    Last Post: 09-17-2012, 02:05 PM
  4. The Number of Years, Months and Days Between Two Dates
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 7
    Last Post: 06-08-2012, 10:35 PM
  5. Number of Days In A Month
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 05-14-2011, 08:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •