Page 3 of 3 FirstFirst 123
Results 21 to 29 of 29

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

  1. #21
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    321
    Rep Power
    3
    Hi Nelson
    _1)
    Quote Originally Posted by nelsondavid View Post
    .....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).......
    This makes no sense ??? I have no idea what you are saying ???


    _2)
    I think you have wrong data again ! ! !

    Your after “File 4 After.xlsx”
    Row\Col
    B
    C
    34
    TOTAL NO. OF DAYS -----> 25
    Worksheet: 121

    Your Before File “File 3 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
    31
    20.Jan.17
    Worksheet: 121


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


    _2)
    The test data that you have chosen does not test this scenario “...holiday is deducted if the person does not come the day before and after the holiday...

    Possibly you mean something like this ???
    29.Dec.16
    ABSENT
    30.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    31.Dec.16
    ABSENT
    Worksheet: 121
    ????

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

    Try again !!!

    Quote Originally Posted by DocAElstein View Post
    .....
    You must make sure that your test data in Before and After illustrates all possible scenarios( and that it is correct ! )
    ....
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot you’re looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

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

    Files attached again and also in column K absent days should be marked as ABSENT

    Rest all seems to be fine

    Thanks,

    Nelson
    Attached Files Attached Files

  3. #23
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    321
    Rep Power
    3
    Hi Nelson
    Quote Originally Posted by nelsondavid View Post
    Files attached again and also in column K absent days should be marked as ABSENT..
    This is a new requirement ?

    But I understand and can do it

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

    _1)
    Quote Originally Posted by nelsondavid View Post
    ....Rest all seems to be fine...


    No rest is still NOT fine

    I count 24 days in Worksheet "121" ( "File 3 Before.xlsx" = 24 days worked)

    You give 25 ????

    Please explain why you give 25
    ????



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

    _2)
    The test data that you have chosen does not test this scenario “...holiday is deducted if the person does not come the day before and after the holiday...


    Let me try to explain to you what you possibly want ( = ..holiday is deducted if the person does not come the day before and after the holiday... )

    If Before =
    Row\Col
    E
    F
    G
    H
    I
    J
    9
    29.Dec.16
    10
    30.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    11
    31.Dec.16
    Worksheet: 121Before

    Then After =
    Row\Col
    E
    F
    G
    H
    I
    J
    9
    29.Dec.16
    10
    30.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    11
    31.Dec.16
    Worksheet: 121After

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

    Else IF Before =
    Row\Col
    E
    F
    G
    H
    I
    J
    9
    29.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    10
    30.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    11
    31.Dec.16
    Worksheet: 121Before

    Then After =
    Row\Col
    E
    F
    G
    H
    I
    J
    9
    29.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    10
    30.Dec.16
    7:00
    18:00
    11:00
    10:00
    11
    31.Dec.16
    Worksheet: 121After

    Is this correct?





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

    Try again !!!

    Quote Originally Posted by DocAElstein View Post
    .....
    You must make sure that your test data in Before and After illustrates all possible scenarios( and that it is correct ! )
    ...

    Alan

    P.s. I have had a code for you finished now for many days: But I cannot test it until you give me correct info and data !!!!!!
    I am very confused, why you keep giving me wrong and inappropriate data ????



    P.P.s. You are using the second code. It uses the formulas suggested by Admin. It need a help column which the code puts in. It did put those in column K. But you want that for the ABSENT indication. So I suggested that could be in column L. I had no response and I do not know why am writing this as I expect you have no idea what I am saying. Never mind. I expect we will get there in the end.
    Last edited by DocAElstein; 02-08-2017 at 06:14 PM.
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot you’re looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

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

    TOTAL NO. OF DAYS ----->25 ,i.e, Absent 4 days plus one holiday (5 days) - Absent days are 29,31,9,19 and 30 (the employee did not come on 29 and 31 so 30 holiday marked as absent).......

    Rest explained in attached file

    Thanks,

    Nelson
    Attached Files Attached Files

  5. #25
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    321
    Rep Power
    3
    Hi Nelson,
    Quote Originally Posted by nelsondavid View Post
    ...(the employee did not come on 29 and 31 so 30 holiday marked as absent)......
    This I now understand ( It was impossible to know this as the code was not putting ABSENT in. I can do this and understand now
    Code:
    Using Excel 2007 32 bit
    
    BEFORE
    29.Dec.16
    30.Dec.16
    31.Dec.16
    AFTER
    29.Dec.16
    ABSENT
    30.Dec.16
    ABSENT
    31.Dec.16
    ABSENT
    Worksheet: Sheet1
    _................................................. .........


    Quote Originally Posted by nelsondavid View Post
    ...
    TOTAL NO. OF DAYS ----->25 ,i.e, Absent 4 days plus one holiday (5 days) - Absent days are 29,31,9,19 and 30 (the employee did not come on 29 and 31 so 30 holiday marked as absent)....
    OK This makes sense now
    ( You forgot to put ABSENT in 30. December , ( "File 4 After.xlsx" )
    This was your After ( "File 4 After.xlsx" )
    29.Dec.16
    ABSENT
    30.Dec.16
    31.Dec.16
    ABSENT
    Worksheet: 121

    It should have been this !!!
    29.Dec.16
    ABSENT
    30.Dec.16
    ABSENT
    31.Dec.16
    ABSENT
    Worksheet: 121
    _............................

    I think I understand now
    I will have time to look again at this tomorrow



    Alan
    Last edited by DocAElstein; 02-08-2017 at 11:02 PM.
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot you’re looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  6. #26
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    321
    Rep Power
    3

    I think we may almost know what is to be done !

    These are the some of the criteria my next code will work on: ( These are those criteria which were unclear until now ! )

    Holidays are shown in yellow background color

    Normal working days have no background color

    The data rows to be considered are those filled with dates in column E

    “TOTAL NO. OF DAYS” ( to be placed by the code in cell C34 )
    For all Months, the “TOTAL NO. OF DAYS” ( to be placed in cell C34 ) is not necessarily the number of days worked.
    The formula for calculating this is:
    _Assuming the employee is not Absent for any day, then the “TOTAL NO. OF DAYS” is always taken as 30
    _ If the employee has one or more normal days of absence, ( normal days with no total working hours ), then the formula for calculating “TOTAL NO. OF DAYS” is as follows:
    TOTAL NO. OF DAYS = 30 – ( Count of “ABSENT” )

    “ABSENT” ( to be written in some rows in Column K by the program )
    Count of “ABSENT” is the number of occurrences of ABSENT in column K in the final ( After ) Worksheet “ABSENT” is to be written in some rows of column K by the code under certain criteria.

    ( “ABSENT” is not necessarily the normal working days in which an employee is absent and / or has no total working hours. )

    “ABSENT” is to be written in column K by the code under the following criteria:
    _ For the rows of all normal days when the employee has no working hours, ( days when the employee is absent ), “ABSENT” is to be written in column K.
    _ In addition , should it occur that an employee is absent for both the days before and after a holiday, then for the ( Holiday ) row in between those two days, “ABSENT” is to be written in column K.
    ( No consideration of this ““ABSENT” criteria thereof” is made for the case of a Holiday at the first or last “Entries” )

    ( The number of “Entries” is taken as the filled dates in column E )

    Column L ( help column )
    Nelson has chosen the second code. It puts formulas in cells C34, G34, and J34.
    This requires “H” or “N” to indicate Holiday or Normal working day. This will be written by the code in column L


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

    Nelson:
    Please examine the above criteria , and tell me if this is correct ?
    Alan

    Google first, like this site:ExcelFox.com "Short Title or Theme of wot you’re looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  7. #27
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    321
    Rep Power
    3
    Hi Nelson.

    Here is the next code version:
    Here: ( Post 27 )
    http://www.excelfox.com/forum/showth...0101#post10101

    and here: ( Post 28 )
    http://www.excelfox.com/forum/showth...0102#post10102

    It is all one code ( I had to split it due to post character size restrictions )
    You need to copy the second ´part directly under the first part in the same code module

    _.........

    The code takes your Before like these:
    http://www.excelfox.com/forum/showth...0096#post10096
    http://www.excelfox.com/forum/showth...0097#post10097

    After running_..
    Sub IJAdjust_LAdd_AbsentKAdd_TotalsFormulas_AllWorkshe etsCode4()
    _.. you should get like:
    http://www.excelfox.com/forum/showth...0098#post10098
    http://www.excelfox.com/forum/showth...0099#post10099
    http://www.excelfox.com/forum/showth...0100#post10100


    _.....


    Here is just a small sample of what the code does:
    Before:
    Using Excel 2007 32 bit
    28.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    29.Dec.16
    30.Dec.16
    31.Dec.16
    1.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    12.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    13.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    14.Jan.17
    7:30
    17:30
    10:00
    9:00
    1:00
    Worksheet: Post22Before121


    After:
    28.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    29.Dec.16
    ABSENT N
    30.Dec.16
    ABSENT
    31.Dec.16
    ABSENT N
    1.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    9.Jan.17
    ABSENT N
    10.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    11.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    12.Jan.17
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    13.Jan.17
    7:00
    18:00
    11:00
    10:00
    H
    14.Jan.17
    7:30
    17:30
    10:00
    9:00
    1:00
    N
    Row\Col
    B
    C
    D
    E
    F
    G
    H
    I
    J
    34
    TOTAL NO. OF DAYS -----> 25
    Normal Overtime ----->
    39
    Holiday Overtime ----->
    18
    Row\Col
    B
    C
    D
    E
    F
    G
    H
    I
    J
    34
    TOTAL NO. OF DAYS -----> =30-COUNTIF(K1:K31,"ABSENT")
    Normal Overtime ----->
    =SUMIF(L1:L31,"N",J1:J31)*24
    Holiday Overtime ----->
    =SUMIF(L1:L31,"H",J1:J31)*24



    Alan
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot you’re looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

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

    Thanks a Lot , it seems to be working fine..if find any problem will get in touch with you

    Thanks and appreciate your patience to solve the problem

    Nelson

  9. #29
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    321
    Rep Power
    3
    Quote Originally Posted by nelsondavid View Post
    ... it seems to be working fine..if find any problem will get in touch with you...)
    OK
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot you’re looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

Similar Threads

  1. Replies: 33
    Last Post: 01-12-2015, 10:38 AM
  2. Replies: 1
    Last Post: 07-23-2013, 11:13 PM
  3. Replies: 0
    Last Post: 09-17-2012, 02:35 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, 11:05 PM
  5. Number of Days In A Month
    By Excel Fox in forum Excel Tips and Tricks
    Replies: 0
    Last Post: 05-14-2011, 08:30 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
  •