Page 1 of 3 123 LastLast
Results 1 to 10 of 29

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

  1. #1
    Junior Member
    Join Date
    Jan 2017
    Posts
    9
    Rep Power
    0

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

    Hi,

    I require a code which i can run though to calculate number of days worked, normal overtime and holiday overtime for all sheets in the workbook

    Sample file attached

    Thanks

    Nelson
    Attached Files Attached Files

  2. #2
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    280
    Rep Power
    3
    Hi Nelson
    Welcome to ExcelFox

    Quote Originally Posted by nelsondavid View Post
    .....I require a code which I can run though to calculate number of days worked, normal overtime and holiday overtime for all sheets in the workbook.....)

    You must try to give more detail
    You have not given us enough infomation.
    You must tell us more of exactly how your output should look.

    Give us two files
    File 1
    One should show exactly what you have Before the code is run

    File 2
    The second file, the After should be filled in manually by you. This should show exactly as it should after the code is run.


    Without this infomation, we cannot guess what you want.

    Alan






    P.s. Did you try the code I gave you here:
    http://www.excelfox.com/forum/showth...age2#post10047
    ?


    Here is the code again:
    Code:
    ' An Initial code for Nelson for Post in this Forum    http://www.excelfox.com/forum/forumdisplay.php/2-Excel-Help
    Sub SomeingSumTotals()          '  https://www.dropbox.com/s/u76eo5trrtppgoi/SAMPLE2.xlsx?dl=0
    Rem 1) Worksheets info.
    Dim WsStear As Worksheet        ' Dim: For Object variabls: Address location to a "pointer". That has all the actual memory locations (addresses) of the various property values , and it holds all the instructions what / how to change them , should that be wanted later. That helped explain what occurs when passing an Object to a Call ed Fucntion or Sub Routine By Val ue. In such an occurance, VBA actually  passes a copy of the pointer.  So that has the effect of when you change things like properties on the local variable , then the changes are reflected in changes in the original object. (The copy pointer instructs how to change those values, at the actual address held in that pointer). That would normally be the sort of thing you would expect from passing by Ref erence.  But as that copy pointer "dies" after the called routine ends, then any changes to the Addresses of the Object Properties in the local variable will not be reflected in the original pointer. So you cannot actually change the pointer.)
                                    ' Set: Fill or partially Fill: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. We will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance
    Rem 2) varables for some totals ;)
    Dim NOHrsV2 As Double, HOHrsV2 As Double, TDays As Long
     Let NOHrsV2 = 0: Let HOHrsV2 = 0: Let TDays = 0
    Rem 3) Loop through worksheets and give some Totals
    Dim Cnt As Long ' Loop Bound variable count for going through all worksheets
        For Cnt = 1 To ThisWorkbook.Worksheets.Count
         Set WsStear = ThisWorkbook.Worksheets.Item(Cnt)                   ' Set: Fill or partially Fill: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. We will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance
        Dim lr As Long                                                     ' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in.  '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. )
         Let lr = WsStear.Range("E" & Rows.Count & "").End(xlUp).Row       ' The Range Object ( cell ) that is the last cell in the column of interest (CHOOSE a column typically that will always have a last Entry in any Data) ,( Row Number given by .Count Property applied to ( any Worksheet would do, so leaving unqualified is OK here, ) Spreadsheet Range Rows Property)    has the Property .End ( argument "Looking back up" ) appled to it. This Returns a new Range ( cell ) object which is that of the first Range ( cell ) with something in it "looking back up" in the XL spreadsheet from that last Cell. Then the .Row Property is applied to return a long number equal to the Row number of that cell:     Rows.Count is the very last row number in your Worksheet. It is different for earlier versions of Excel.  The End(xlUp) is the same as pressing a Ctrl+UpArrow key combination. The final ".Row" returns the row where the cursor stops after moving up.
        Dim FstDtaCel As Range: Set FstDtaCel = WsStear.Range("A2")        ' Worksheets Range(" ") Property used to return Range object of first cell in second row
        Dim arrInNorm() As Variant, arrInOver() As Variant                 ' In the next lines the .Value2 Property is applied a Range object which presents the the Value2 value or values in a single variable of appropriate type or a field of member Elements of varaint types.We are expecting the latter, so declare ( Dim ) a dynamic Array variable appropriately. It must be dynamic as its size will be defined at that assignment
         Let arrInNorm() = FstDtaCel.Offset(0, 8).Resize(lr - 1, 1).Value2 ' One thing you pick up when learning VBA programming is that referring to cells from one to another via an offset is both fundamental and efficient. That makes sense as Excel is all about using the offsets mentioned above. So like if you use them you can “cut out the middle man”. ( The middle man here might be considered as, for example, in VBA, using extra variables for different Range objects: A fundamental thing to do with any cell ( or strictly speaking the Range object associated to a cell ) is the Range Item Property of any range Object, through which you can “get at” any other Range object. http://www.excelforum.com/showthread.php?t=1154829&page=13&p=4563838&highlight=#post4563838 ( It is often quicker than using a separate variable for each Range object – probably as all the variable does is hold the offset , so you might as well use the offset in the first place.. ) )
         Let arrInOver() = FstDtaCel.Offset(0, 9).Resize(lr - 1, 1).Value2 ' Similarly Another thing you pick up along the way is that the cells ( or strictly speaking the Range objects associated with it ) can be organised into groups of cells which then are also called Range objects and are organised in their constituent parts exactly the same as for the single cell Range object. Once again this is all an indication of organising so that we get at information by sliding along a specific amount ( offset value). The Offset and Resize properties therefore return a new range object. I use the .Value 2 here as i seemed to get it for .Value anyway, not sure why yet, - so i thought be on the safe side , get it always and work somehow with that for now and convert as necerssary.
        Dim ShtCnt As Long ' Loop Bound Variable Count for hours columns looping
            For ShtCnt = 1 To UBound(arrInNorm(), 1) Step 1
                If arrInNorm(ShtCnt, 1) <> 0 And arrInOver(ShtCnt, 1) <> 0 Then Let NOHrsV2 = NOHrsV2 + arrInOver(ShtCnt, 1)
                If arrInNorm(ShtCnt, 1) = 0 And arrInOver(ShtCnt, 1) <> 0 Then Let HOHrsV2 = HOHrsV2 + arrInOver(ShtCnt, 1)
            Next ShtCnt
        Next Cnt
    Rem 4) Output some totals ;)
     MsgBox prompt:="Normal Overtime is " & NOHrsV2 * 24 & vbCrLf & "Holiday Overtime is" & HOHrsV2 * 24 ' NelsonMessageBox.jpg     http://imgur.com/XSvQpQi
    '4b) Tell you Totals                                                   ' Sub Speach() '                                        Richard Buttrey   http://www.excelforum.com/showthread.php?t=1164765&p=4535112#post4535112
    Dim Saps As Object
     Set Saps = CreateObject("SAPI.SpVoice")
     Saps.Speak "Hello Nelson. These are Sum totals for Normal Overtime and Holiday Overtime for the two Worksheets you gave.. Normal Overtime is " & NOHrsV2 * 24 & "..  Holiday Overtime is " & HOHrsV2 * 24 & ".. I expect this is not yet quite what you want."
    '                                                                      ' End Sub
    End Sub
    Last edited by DocAElstein; 02-01-2017 at 10:59 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/

  3. #3
    Junior Member
    Join Date
    Jan 2017
    Posts
    9
    Rep Power
    0

    Code Required

    Dear Alan,

    As requested two files attached .

    Thanks,

    Nelson
    Attached Files Attached Files

  4. #4
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    280
    Rep Power
    3
    Hi Nelson,
    Quote Originally Posted by nelsondavid View Post
    ..As requested two files attached .....
    OK , I think I understand.
    I will post you a solution later
    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/

  5. #5
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    280
    Rep Power
    3
    Hi Nelson,

    I hope I have the correct Files and understand the Logic: I see with the last Files you sent that:
    Two things are happening,
    ___ Totals are made and pasted out
    And
    ___ Normal Hrs ( Column I ) and Overtime Hrs ( Column J ) are to be changed


    So I this is what I understand is the requirement and have done:

    Rem 1) The code is set to work on the Active Workbook. That is the one you “looking at”. ( Any Worksheet can be selected )



    Rem 2) Total No, of days is simply taken as a constant of 30. (Const TDays As Long = 30)

    '3b)
    _ Date ( Column E ) and Total hrs ( Column H ) are required to use in calculations

    _ Normal Hrs ( Column I ) are needed as they must be set to zero for Holy ?? Holidays ?? Friday ??
    _ Overtime ( Column J ) is needed as it will be changed and then used in calculations.

    '3d) We need to check for a Holy?? Holiday?? Friday??
    ___If so Then
    ____ Total Hrs ( Column H ) are added to Overtime Hrs ( Column J ) according to the following rules:
    ____ (i) If Total Hrs are less than or equal to 9 , Then all Total Hrs are Given To to Overtime Hrs
    ____ (ii) Else If Total Hrs are greater than 9 , Then ( Total Hrs – 1 ) are Given To to Overtime Hrs

    ____ (iii) Normal Hrs ( Column I ) are then Emptyed.

    _3e)
    _ As in all my codes, Normal Overtime is simply calculated from summing hours in column J only If there are Overtime hours in column J And there are Normal hours are in column I.
    ( The total number will be entered into cell G34 )

    _ As in all my codes, Holiday Overtime is simply calculated from summing hours in column J only If there are Overtime hours in column J And there are no Normal hours are in column I.
    ( The total number will entered into cell J34 )

    _3f)
    The total number Normal Overtime entered into cell G34
    The total number Holiday Overtime entered into cell G34
    The value of 30 for Total days is simply added to cell C34

    _ 3g Normal Hrs ( Column I ) and Overtime Hrs ( Column J ) are changed
    _...


    _._______________________________


    '_- ExPlanations are given in code ' Comments, ( especially for snb ; ) )

    Code is here: ( Post #19 )
    http://www.excelfox.com/forum/showth...0066#post10066
    http://www.excelfox.com/forum/showth...age2#post10066


    Typical Results for one Worksheet are given in next 2 Posts


    Alan
    Last edited by DocAElstein; 02-03-2017 at 09:47 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. #6
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    280
    Rep Power
    3

    Typical Before

    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
    32
    33
    34
    TOTAL NO. OF DAYS ----->
    Normal Overtime ----->
    Holiday Overtime ----->
    Worksheet: 121
    Last edited by DocAElstein; 02-03-2017 at 07:54 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/

  7. #7
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    280
    Rep Power
    3

    Typical After

    Typical After



    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
    8: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
    10: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
    10: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
    32
    33
    34
    TOTAL NO. OF DAYS -----> 30
    Normal Overtime ----->
    47
    Holiday Overtime ----->
    28
    Worksheet: 121
    Last edited by DocAElstein; 02-03-2017 at 07:55 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/

  8. #8
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,064
    Rep Power
    10
    Hi

    If I understood correctly, you can add one more column at the end to highlight which is the normal working day or holiday rather than coloring the cell.

    Please find attached.

    HTH
    Attached Files Attached Files
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2013 on Win 7 (64 bit)

  9. #9
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    280
    Rep Power
    3
    Quote Originally Posted by Admin View Post
    ..... you can add one more column at the end to highlight which is the normal working day or holiday rather than coloring the cell......
    Hi Admin
    I was a bit confused at first.
    I see now what you have done now.
    You have taken his AFTER and applied a SUMIF on the two columns I and J....

    So first , You added then the extra column K with H for Holiday and N for normal day,
    After Worksheet
    Row\Col
    E
    F
    G
    H
    I
    J
    K
    1
    21.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    2
    22.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    3
    23.Dec.16
    7:00
    15:00
    8:00
    8:00
    H
    4
    24.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    5
    25.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    N
    Worksheet: 121

    Then you have these formulas:
    Row\Col
    G
    H
    I
    J
    35
    =SUMIF(K1:K31,"N",J1:J31)
    =SUMIF(K1:K31,"H",J1:J31)



    If Nelson wanted to do that he would still need to use part of my code to get the AFTER worksheet in its final form, as his start point , the BEFORE File is a bit different and needs a bit of manipulation based on his various criteria. ( Columns I and J needed to be adjsued:
    BEFORE
    Row\Col
    E
    F
    G
    H
    I
    J
    1
    21.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    2
    22.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    3
    23.Dec.16
    7:00
    15:00
    8:00
    9:00
    0:00
    4
    24.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    5
    25.Dec.16
    7:00
    18:00
    11:00
    9:00
    2:00
    Worksheet: 121

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

    By the way, I originally assumed that Holiday was a “Holy Friday thing”, ( So I was checking in my code for Friday and not yellow background color )but I am not sure if that is correct.
    What I will do in the next posts to cover my back and to let your solution work is do another code, which checks based on the background colour being yellow for a holiday, then does the manipulation of the Worksheet, but also then after that includes your “H” or “N” in col.umn K, and while I am at it I will pastes in your formulas:

    I expect then Nelson will have more than enough to keep him busy or confuse him

    Alan
    Last edited by DocAElstein; 02-05-2017 at 07:51 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/

  10. #10
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    280
    Rep Power
    3

    Second Code for Nelson using Admin Formula Idea

    Hi Nelson,
    This is another solution separate to mine form posts 5 6 and 7

    Here is a new Full code: Sub IJAdjustKAddTotalAllWorksheet()
    Post #20
    http://www.excelfox.com/forum/showth...0072#post10072


    The check criteria is now the background color, ( as alternative to Friday as in the first code ) '3d) We need to check Interior color . I got it from this quick code
    Code:
    ' Rem Ref '_-   http://www.excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp?p=10062#post10062
    '_-             http://www.excelfox.com/forum/showthread.php/2138-Understanding-VBA-Range-Object-Properties-and-referring-to-ranges-and-spreadsheet-cells?p=10012#post10012
    Sub GetInteriorColor() '   https://msdn.microsoft.com/en-us/library/office/ff840499.aspx             http://www.excelfox.com/forum/showthread.php/2144-Code-Required-to-calculate-number-of-days-worked-normal-overtime-and-holiday-overtime?p=10070#post10070
    Dim ObjColor As Object
     Set ObjColor = Range("E3").Interior: Debug.Print ObjColor.Color ' Looks Yellow is Double 65535 in Immedite window ( Ctrl+g )
     Set ObjColor = Range("E4").Interior: Debug.Print ObjColor.Color ' Looks like no color is Double 16777215
    Dim RngDatesColors As Range ' Select RngDatesColors and hit Shift+F9 to get Range object in watch window. navigate by scrolling and opening with + signs the .Interor Object
     Set RngDatesColors = ThisWorkbook.Worksheets("121").Range("A1").Offset(0, 4).Resize(31, 1) ' .Interior shows only one value for the entire range which seems to be that for a cell with no background color as seen in the screenshot above  all the cells have a colour
    End Sub
    which, for the BEFORE worksheet “121” tells me that the yellow is an interior taken as a Double of 65535
    InteriorColor.JPG http://imgur.com/MavnwLi
    InteriorColor.JPG

    I make an extra array, arrK() to take the string values for the helper column K, to hold “H” for Holiday hrs or “N” for normal hrs


    I no longer take in a dates array to check for Friday. Instead, I take in an Array, arrDteClr(), of the dates interior color. This is a bit inefficient as there is no Interior colors array in the Excel Range object. ( .Interior property for a Range object shows only one value for the entire range which seems to be that as for no color as seen in the screenshot above unless all the cells have a colour, in which case it gives the color for that )
    So I have to take in each value in a new loop, 'InnerLoop for dates background colors ( so I could just as well have checked the worksheet, but I made an array for consistency as I am predominantly using arrays in my code).


    Code section '3e) from the first code is not now used to calculate totals. ( and neither are the variables NOHrsV2 and HOHrsV2 which were used for those totals

    Code section ‘3f) is modified to paste out the formulas from Admin instead of the totals values


    In section ‘3g) there is a line which pastes out that help column




    So in next post is just the BEFORE ( “File1 Before.xlsx” ) data range for the first worksheet “121”.
    (as in Post #6
    http://www.excelfox.com/forum/showth...0068#post10068
    )


    Then in the final 2 couple of post is the Output ( AFTER ) for the new code for one of the Worksheets ( “121” ). The code works on the same Input ( BEFORE ) as in Post #6
    http://www.excelfox.com/forum/showth...0068#post10068
    Last edited by DocAElstein; 02-06-2017 at 12:10 AM.
    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
  •