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

[nelsondavid]

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:)

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg (https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1 (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg (https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg)
https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg (https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9 (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I)
https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3 (https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
ttps://www.youtube.com/watch?v=LP9fz2DCMBE (ttps://www.youtube.com/watch?v=LP9fz2DCMBE)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8 (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8)
ttps://www.youtube.com/watch?v=bFxnXH4-L1A (ttps://www.youtube.com/watch?v=bFxnXH4-L1A)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg)
ttps://www.youtube.com/watch?v=GqzeFYWjTxI (ttps://www.youtube.com/watch?v=GqzeFYWjTxI)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

Hi Nelson
Welcome to ExcelFox :)


.....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. :confused:

Alan
:)





P.s. Did you try the code I gave you here:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page2#post10047
?


Here is the code again:

' 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

[nelsondavid]

Dear Alan,

As requested two files attached .

Thanks,

Nelson:)

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hWn2pGBe SS (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hWn2pGBe SS)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg.9hJRnEjxQrd9hJoCjomN I2 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzkRujoMw9PblmXDQ14AaABAg.9hJRnEjxQrd9hJoCjomN I2)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg.9hJOZEEZa6p9hJqLC7El-w (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgzPZbG7OvUkh35nXDd4AaABAg.9hJOZEEZa6p9hJqLC7El-w)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg.9hIlxxGY7t49hJsB2PWx C4 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwUcEpm8u6ZW3uOHXx4AaABAg.9hIlxxGY7t49hJsB2PWx C4)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg.9hIKlNPeqDn9hJskm92n p6 (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyvDj6NWT1Gxyy2JyR4AaABAg.9hIKlNPeqDn9hJskm92n p6)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg.9hI2IGUdmTW9hJuyaQaw qx (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugwy7qx_kG9iUmMVO_F4AaABAg.9hI2IGUdmTW9hJuyaQaw qx)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hJwTB9Jl ob (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgxesLhWNr_zNP0GUdh4AaABAg.9hI1CQJMLLo9hJwTB9Jl ob)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgyyQWYVP1OnCqavb-x4AaABAg)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=UgwJKKmExZ1FdZVDJf54AaABAg)
https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg (https://www.youtube.com/watch?v=pkhazgI3LAo&lc=Ugz_p0kVGrLntPtYzCt4AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9iHOYYpaA bC (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg.9h740K6COOA9iHOYYpaA bC)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgxuL6YCUckeUIh9hoh4AaABAg (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgxuL6YCUckeUIh9hoh4AaABAg)
https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7G-bVm8_- (https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwGTEyefOX7msIh1wZ4AaABAg.9h4sd6Vs4qE9h7G-bVm8_-)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://eileenslounge.com/viewtopic.php?p=244184#p244184 (https://eileenslounge.com/viewtopic.php?p=244184#p244184)
https://eileenslounge.com/viewtopic.php?p=246586#p246586 (https://eileenslounge.com/viewtopic.php?p=246586#p246586)
https://eileenslounge.com/viewtopic.php?p=246112#p246112 (https://eileenslounge.com/viewtopic.php?p=246112#p246112)
https://eileenslounge.com/viewtopic.php?p=246112#p246112 (https://eileenslounge.com/viewtopic.php?p=246112#p246112)
https://eileenslounge.com/viewtopic.php?p=245761#p245761 (https://eileenslounge.com/viewtopic.php?p=245761#p245761)
https://eileenslounge.com/viewtopic.php?p=245722#p245722 (https://eileenslounge.com/viewtopic.php?p=245722#p245722)
https://eileenslounge.com/viewtopic.php?p=245616#p245616 (https://eileenslounge.com/viewtopic.php?p=245616#p245616)
https://eileenslounge.com/viewtopic.php?p=247043#p247043 (https://eileenslounge.com/viewtopic.php?p=247043#p247043)
https://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use (https://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use)
https://eileenslounge.com/viewtopic.php?p=245238#p245238 (https://eileenslounge.com/viewtopic.php?p=245238#p245238)
https://eileenslounge.com/viewtopic.php?p=245131#p245131 (https://eileenslounge.com/viewtopic.php?p=245131#p245131)
https://eileenslounge.com/viewtopic.php?f=18&t=31638 (https://eileenslounge.com/viewtopic.php?f=18&t=31638)
https://eileenslounge.com/viewtopic.php?p=244579#p244579 (https://eileenslounge.com/viewtopic.php?p=244579#p244579)
https://eileenslounge.com/viewtopic.php?p=244648#p244648 (https://eileenslounge.com/viewtopic.php?p=244648#p244648)
https://eileenslounge.com/viewtopic.php?p=244647#p244647 (https://eileenslounge.com/viewtopic.php?p=244647#p244647)
https://eileenslounge.com/viewtopic.php?p=244577#p244577 (https://eileenslounge.com/viewtopic.php?p=244577#p244577)
https://eileenslounge.com/viewtopic.php?p=245201#p245201 (https://eileenslounge.com/viewtopic.php?p=245201#p245201)
https://eileenslounge.com/viewtopic.php?p=243975#p243975 (https://eileenslounge.com/viewtopic.php?p=243975#p243975)
https://eileenslounge.com/viewtopic.php?p=243884#p243884 (https://eileenslounge.com/viewtopic.php?p=243884#p243884)
https://eileenslounge.com/viewtopic.php?p=242439#p242439 (https://eileenslounge.com/viewtopic.php?p=242439#p242439)
https://eileenslounge.com/viewtopic.php?p=243595#p243595 (https://eileenslounge.com/viewtopic.php?p=243595#p243595)
https://eileenslounge.com/viewtopic.php?p=243589#p243589 (https://eileenslounge.com/viewtopic.php?p=243589#p243589)
https://eileenslounge.com/viewtopic.php?p=243589#p243589 (https://eileenslounge.com/viewtopic.php?p=243589#p243589)
https://eileenslounge.com/viewtopic.php?p=243002#p243002 (https://eileenslounge.com/viewtopic.php?p=243002#p243002)
https://www.eileenslounge.com/viewtopic.php?p=242761#p242761 (https://www.eileenslounge.com/viewtopic.php?p=242761#p242761)
https://eileenslounge.com/viewtopic.php?p=242459#p242459 (https://eileenslounge.com/viewtopic.php?p=242459#p242459)
https://eileenslounge.com/viewtopic.php?p=242054#p242054 (https://eileenslounge.com/viewtopic.php?p=242054#p242054)
https://eileenslounge.com/viewtopic.php?p=241404#p241404 (https://eileenslounge.com/viewtopic.php?p=241404#p241404)
https://eileenslounge.com/viewtopic.php?p=229145#p229145 (https://eileenslounge.com/viewtopic.php?p=229145#p229145)
https://eileenslounge.com/viewtopic.php?p=228710#p228710 (https://eileenslounge.com/viewtopic.php?p=228710#p228710)
https://eileenslounge.com/viewtopic.php?p=226938#p226938 (https://eileenslounge.com/viewtopic.php?p=226938#p226938)
https://eileenslounge.com/viewtopic.php?f=18&t=28885 (https://eileenslounge.com/viewtopic.php?f=18&t=28885)
https://eileenslounge.com/viewtopic.php?p=222689#p222689 (https://eileenslounge.com/viewtopic.php?p=222689#p222689)
https://eileenslounge.com/viewtopic.php?p=221622#p221622 (https://eileenslounge.com/viewtopic.php?p=221622#p221622)
https://eileenslounge.com/viewtopic.php?f=27&t=22512 (https://eileenslounge.com/viewtopic.php?f=27&t=22512)
https://eileenslounge.com/viewtopic.php?f=26&t=26183 (https://eileenslounge.com/viewtopic.php?f=26&t=26183)
https://eileenslounge.com/viewtopic.php?f=26&t=26030 (https://eileenslounge.com/viewtopic.php?f=26&t=26030)
https://eileenslounge.com/viewtopic.php?p=202322#p202322 (https://eileenslounge.com/viewtopic.php?p=202322#p202322)
https://www.excelforum.com/word-formatting-and-general/1174522-finding-a-particular-word-phrase-in-word.html#post4604396 (https://www.excelforum.com/word-formatting-and-general/1174522-finding-a-particular-word-phrase-in-word.html#post4604396)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

Hi Nelson,

..As requested two files attached .....
OK , I think I understand.
I will post you a solution later :)
Alan

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://eileenslounge.com/viewtopic.php?p=244184#p244184 (https://eileenslounge.com/viewtopic.php?p=244184#p244184)
https://eileenslounge.com/viewtopic.php?p=246586#p246586 (https://eileenslounge.com/viewtopic.php?p=246586#p246586)
https://eileenslounge.com/viewtopic.php?p=246112#p246112 (https://eileenslounge.com/viewtopic.php?p=246112#p246112)
https://eileenslounge.com/viewtopic.php?p=246112#p246112 (https://eileenslounge.com/viewtopic.php?p=246112#p246112)
https://eileenslounge.com/viewtopic.php?p=245761#p245761 (https://eileenslounge.com/viewtopic.php?p=245761#p245761)
https://eileenslounge.com/viewtopic.php?p=245722#p245722 (https://eileenslounge.com/viewtopic.php?p=245722#p245722)
https://eileenslounge.com/viewtopic.php?p=245616#p245616 (https://eileenslounge.com/viewtopic.php?p=245616#p245616)
https://eileenslounge.com/viewtopic.php?p=247043#p247043 (https://eileenslounge.com/viewtopic.php?p=247043#p247043)
https://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use (https://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use)
https://eileenslounge.com/viewtopic.php?p=245238#p245238 (https://eileenslounge.com/viewtopic.php?p=245238#p245238)
https://eileenslounge.com/viewtopic.php?p=245131#p245131 (https://eileenslounge.com/viewtopic.php?p=245131#p245131)
https://eileenslounge.com/viewtopic.php?f=18&t=31638 (https://eileenslounge.com/viewtopic.php?f=18&t=31638)
https://eileenslounge.com/viewtopic.php?p=244579#p244579 (https://eileenslounge.com/viewtopic.php?p=244579#p244579)
https://eileenslounge.com/viewtopic.php?p=244648#p244648 (https://eileenslounge.com/viewtopic.php?p=244648#p244648)
https://eileenslounge.com/viewtopic.php?p=244647#p244647 (https://eileenslounge.com/viewtopic.php?p=244647#p244647)
https://eileenslounge.com/viewtopic.php?p=244577#p244577 (https://eileenslounge.com/viewtopic.php?p=244577#p244577)
https://eileenslounge.com/viewtopic.php?p=245201#p245201 (https://eileenslounge.com/viewtopic.php?p=245201#p245201)
https://eileenslounge.com/viewtopic.php?p=243975#p243975 (https://eileenslounge.com/viewtopic.php?p=243975#p243975)
https://eileenslounge.com/viewtopic.php?p=243884#p243884 (https://eileenslounge.com/viewtopic.php?p=243884#p243884)
https://eileenslounge.com/viewtopic.php?p=242439#p242439 (https://eileenslounge.com/viewtopic.php?p=242439#p242439)
https://eileenslounge.com/viewtopic.php?p=243595#p243595 (https://eileenslounge.com/viewtopic.php?p=243595#p243595)
https://eileenslounge.com/viewtopic.php?p=243589#p243589 (https://eileenslounge.com/viewtopic.php?p=243589#p243589)
https://eileenslounge.com/viewtopic.php?p=243589#p243589 (https://eileenslounge.com/viewtopic.php?p=243589#p243589)
https://eileenslounge.com/viewtopic.php?p=243002#p243002 (https://eileenslounge.com/viewtopic.php?p=243002#p243002)
https://www.eileenslounge.com/viewtopic.php?p=242761#p242761 (https://www.eileenslounge.com/viewtopic.php?p=242761#p242761)
https://eileenslounge.com/viewtopic.php?p=242459#p242459 (https://eileenslounge.com/viewtopic.php?p=242459#p242459)
https://eileenslounge.com/viewtopic.php?p=242054#p242054 (https://eileenslounge.com/viewtopic.php?p=242054#p242054)
https://eileenslounge.com/viewtopic.php?p=241404#p241404 (https://eileenslounge.com/viewtopic.php?p=241404#p241404)
https://eileenslounge.com/viewtopic.php?p=229145#p229145 (https://eileenslounge.com/viewtopic.php?p=229145#p229145)
https://eileenslounge.com/viewtopic.php?p=228710#p228710 (https://eileenslounge.com/viewtopic.php?p=228710#p228710)
https://eileenslounge.com/viewtopic.php?p=226938#p226938 (https://eileenslounge.com/viewtopic.php?p=226938#p226938)
https://eileenslounge.com/viewtopic.php?f=18&t=28885 (https://eileenslounge.com/viewtopic.php?f=18&t=28885)
https://eileenslounge.com/viewtopic.php?p=222689#p222689 (https://eileenslounge.com/viewtopic.php?p=222689#p222689)
https://eileenslounge.com/viewtopic.php?p=221622#p221622 (https://eileenslounge.com/viewtopic.php?p=221622#p221622)
https://eileenslounge.com/viewtopic.php?f=27&t=22512 (https://eileenslounge.com/viewtopic.php?f=27&t=22512)
https://eileenslounge.com/viewtopic.php?f=26&t=26183 (https://eileenslounge.com/viewtopic.php?f=26&t=26183)
https://eileenslounge.com/viewtopic.php?f=26&t=26030 (https://eileenslounge.com/viewtopic.php?f=26&t=26030)
https://eileenslounge.com/viewtopic.php?p=202322#p202322 (https://eileenslounge.com/viewtopic.php?p=202322#p202322)
https://www.excelforum.com/word-formatting-and-general/1174522-finding-a-particular-word-phrase-in-word.html#post4604396 (https://www.excelforum.com/word-formatting-and-general/1174522-finding-a-particular-word-phrase-in-word.html#post4604396)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

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/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10066#post10066
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page2#post10066


Typical Results for one Worksheet are given in next 2 Posts


Alan

[DocAElstein]

Using Excel 2007 32 bit
Row\Col
A
B
C
D
E
F
G
H
I
J

1SOVELJOY
121TEAM LEADER
21.Dec.16
7:00
18:00
11:00
9:00
2:00


2SOVELJOY
121TEAM LEADER
22.Dec.16
7:00
18:00
11:00
9:00
2:00


3SOVELJOY
121TEAM LEADER
23.Dec.16
7:00
15:00
8:00
9:00
0:00


4SOVELJOY
121TEAM LEADER
24.Dec.16
7:00
18:00
11:00
9:00
2:00


5SOVELJOY
121TEAM LEADER
25.Dec.16
7:00
18:00
11:00
9:00
2:00


6SOVELJOY
121TEAM LEADER
26.Dec.16
7:00
18:00
11:00
9:00
2:00


7SOVELJOY
121TEAM LEADER
27.Dec.16
7:00
17:00
10:00
9:00
1:00


8SOVELJOY
121TEAM LEADER
28.Dec.16
7:00
18:00
11:00
9:00
2:00


9SOVELJOY
121TEAM LEADER
29.Dec.16
7:00
18:00
11:00
9:00
2:00


10SOVELJOY
121TEAM LEADER
30.Dec.16
7:00
18:00
11:00
9:00
2:00


11SOVELJOY
121TEAM LEADER
31.Dec.16
7:00
18:00
11:00
9:00
2:00


12SOVELJOY
121TEAM LEADER
1.Jan.17
7:00
18:00
11:00
9:00
2:00


13SOVELJOY
121TEAM LEADER
2.Jan.17
7:00
18:00
11:00
9:00
2:00


14SOVELJOY
121TEAM LEADER
3.Jan.17
7:00
18:00
11:00
9:00
2:00


15SOVELJOY
121TEAM LEADER
4.Jan.17
7:00
18:00
11:00
9:00
2:00


16SOVELJOY
121TEAM LEADER
5.Jan.17
7:00
18:00
11:00
9:00
2:00


17SOVELJOY
121TEAM LEADER
6.Jan.17


18SOVELJOY
121TEAM LEADER
7.Jan.17
7:00
18:00
11:00
9:00
2:00


19SOVELJOY
121TEAM LEADER
8.Jan.17
7:00
18:00
11:00
9:00
2:00


20SOVELJOY
121TEAM LEADER
9.Jan.17
7:00
18:00
11:00
9:00
2:00


21SOVELJOY
121TEAM LEADER
10.Jan.17
7:00
18:00
11:00
9:00
2:00


22SOVELJOY
121TEAM LEADER
11.Jan.17
7:00
18:00
11:00
9:00
2:00


23SOVELJOY
121TEAM LEADER
12.Jan.17
7:00
18:00
11:00
9:00
2:00


24SOVELJOY
121TEAM LEADER
13.Jan.17
7:00
18:00
11:00
9:00
2:00


25SOVELJOY
121TEAM LEADER
14.Jan.17
7:30
17:30
10:00
9:00
1:00


26SOVELJOY
121TEAM LEADER
15.Jan.17
7:30
17:30
10:00
9:00
1:00


27SOVELJOY
121TEAM LEADER
16.Jan.17
7:30
17:30
10:00
9:00
1:00


28SOVELJOY
121TEAM LEADER
17.Jan.17
7:30
17:30
10:00
9:00
1:00


29SOVELJOY
121TEAM LEADER
18.Jan.17
7:00
18:00
11:00
9:00
2:00


30SOVELJOY
121TEAM LEADER
19.Jan.17
7:00
18:00
11:00
9:00
2:00


31SOVELJOY
121TEAM LEADER
20.Jan.17


32


33


34TOTAL NO. OF DAYS ----->

Normal Overtime ----->
Holiday Overtime ----->
Worksheet: 121

[DocAElstein]

Typical After



Using Excel 2007 32 bit
Row\Col
A
B
C
D
E
F
G
H
I
J

1SOVELJOY
121TEAM LEADER
21.Dec.16
7:00
18:00
11:00
9:00
2:00


2SOVELJOY
121TEAM LEADER
22.Dec.16
7:00
18:00
11:00
9:00
2:00


3SOVELJOY
121TEAM LEADER
23.Dec.16
7:00
15:00
8:00
8:00


4SOVELJOY
121TEAM LEADER
24.Dec.16
7:00
18:00
11:00
9:00
2:00


5SOVELJOY
121TEAM LEADER
25.Dec.16
7:00
18:00
11:00
9:00
2:00


6SOVELJOY
121TEAM LEADER
26.Dec.16
7:00
18:00
11:00
9:00
2:00


7SOVELJOY
121TEAM LEADER
27.Dec.16
7:00
17:00
10:00
9:00
1:00


8SOVELJOY
121TEAM LEADER
28.Dec.16
7:00
18:00
11:00
9:00
2:00


9SOVELJOY
121TEAM LEADER
29.Dec.16
7:00
18:00
11:00
9:00
2:00


10SOVELJOY
121TEAM LEADER
30.Dec.16
7:00
18:00
11:00
10:00


11SOVELJOY
121TEAM LEADER
31.Dec.16
7:00
18:00
11:00
9:00
2:00


12SOVELJOY
121TEAM LEADER
1.Jan.17
7:00
18:00
11:00
9:00
2:00


13SOVELJOY
121TEAM LEADER
2.Jan.17
7:00
18:00
11:00
9:00
2:00


14SOVELJOY
121TEAM LEADER
3.Jan.17
7:00
18:00
11:00
9:00
2:00


15SOVELJOY
121TEAM LEADER
4.Jan.17
7:00
18:00
11:00
9:00
2:00


16SOVELJOY
121TEAM LEADER
5.Jan.17
7:00
18:00
11:00
9:00
2:00


17SOVELJOY
121TEAM LEADER
6.Jan.17


18SOVELJOY
121TEAM LEADER
7.Jan.17
7:00
18:00
11:00
9:00
2:00


19SOVELJOY
121TEAM LEADER
8.Jan.17
7:00
18:00
11:00
9:00
2:00


20SOVELJOY
121TEAM LEADER
9.Jan.17
7:00
18:00
11:00
9:00
2:00


21SOVELJOY
121TEAM LEADER
10.Jan.17
7:00
18:00
11:00
9:00
2:00


22SOVELJOY
121TEAM LEADER
11.Jan.17
7:00
18:00
11:00
9:00
2:00


23SOVELJOY
121TEAM LEADER
12.Jan.17
7:00
18:00
11:00
9:00
2:00


24SOVELJOY
121TEAM LEADER
13.Jan.17
7:00
18:00
11:00
10:00


25SOVELJOY
121TEAM LEADER
14.Jan.17
7:30
17:30
10:00
9:00
1:00


26SOVELJOY
121TEAM LEADER
15.Jan.17
7:30
17:30
10:00
9:00
1:00


27SOVELJOY
121TEAM LEADER
16.Jan.17
7:30
17:30
10:00
9:00
1:00


28SOVELJOY
121TEAM LEADER
17.Jan.17
7:30
17:30
10:00
9:00
1:00


29SOVELJOY
121TEAM LEADER
18.Jan.17
7:00
18:00
11:00
9:00
2:00


30SOVELJOY
121TEAM LEADER
19.Jan.17
7:00
18:00
11:00
9:00
2:00


31SOVELJOY
121TEAM LEADER
20.Jan.17


32


33


34TOTAL NO. OF DAYS ----->30

Normal Overtime ----->47
Holiday Overtime ----->28
Worksheet: 121

[Admin]

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

[DocAElstein]

..... 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 WorksheetRow\Col
E
F
G
H
I
J
K

1
21.Dec.16
7:00
18:00
11:00
9:00
2:00N


2
22.Dec.16
7:00
18:00
11:00
9:00
2:00N


3
23.Dec.16
7:00
15:00
8:00
8:00H


4
24.Dec.16
7:00
18:00
11:00
9:00
2:00N


5
25.Dec.16
7:00
18:00
11:00
9:00
2:00N
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:
BEFORERow\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

[DocAElstein]

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/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10072#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
' 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
1870

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/showthread.php/2144-Code-Required-to-calculate-number-of-days-worked-normal-overtime-and-holiday-overtime?p=10068#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/showthread.php/2144-Code-Required-to-calculate-number-of-days-worked-normal-overtime-and-holiday-overtime?p=10068#post10068

[DocAElstein]

BEFORE ( “File1 Before.xlsx” ) data range for the first worksheet “121”.
(as in Post #6
http://www.excelfox.com/forum/showthread.php/2144-Code-Required-to-calculate-number-of-days-worked-normal-overtime-and-holiday-overtime?p=10068#post10068 )


Using Excel 2007 32 bit
Row\Col
A
B
C
D
E
F
G
H
I
J

1SOVELJOY
121TEAM LEADER
21.Dec.16
7:00
18:00
11:00
9:00
2:00


2SOVELJOY
121TEAM LEADER
22.Dec.16
7:00
18:00
11:00
9:00
2:00


3SOVELJOY
121TEAM LEADER
23.Dec.16
7:00
15:00
8:00
9:00
0:00


4SOVELJOY
121TEAM LEADER
24.Dec.16
7:00
18:00
11:00
9:00
2:00


5SOVELJOY
121TEAM LEADER
25.Dec.16
7:00
18:00
11:00
9:00
2:00


6SOVELJOY
121TEAM LEADER
26.Dec.16
7:00
18:00
11:00
9:00
2:00


7SOVELJOY
121TEAM LEADER
27.Dec.16
7:00
17:00
10:00
9:00
1:00


8SOVELJOY
121TEAM LEADER
28.Dec.16
7:00
18:00
11:00
9:00
2:00


9SOVELJOY
121TEAM LEADER
29.Dec.16
7:00
18:00
11:00
9:00
2:00


10SOVELJOY
121TEAM LEADER
30.Dec.16
7:00
18:00
11:00
9:00
2:00


11SOVELJOY
121TEAM LEADER
31.Dec.16
7:00
18:00
11:00
9:00
2:00


12SOVELJOY
121TEAM LEADER
1.Jan.17
7:00
18:00
11:00
9:00
2:00


13SOVELJOY
121TEAM LEADER
2.Jan.17
7:00
18:00
11:00
9:00
2:00


14SOVELJOY
121TEAM LEADER
3.Jan.17
7:00
18:00
11:00
9:00
2:00


15SOVELJOY
121TEAM LEADER
4.Jan.17
7:00
18:00
11:00
9:00
2:00


16SOVELJOY
121TEAM LEADER
5.Jan.17
7:00
18:00
11:00
9:00
2:00


17SOVELJOY
121TEAM LEADER
6.Jan.17


18SOVELJOY
121TEAM LEADER
7.Jan.17
7:00
18:00
11:00
9:00
2:00


19SOVELJOY
121TEAM LEADER
8.Jan.17
7:00
18:00
11:00
9:00
2:00


20SOVELJOY
121TEAM LEADER
9.Jan.17
7:00
18:00
11:00
9:00
2:00


21SOVELJOY
121TEAM LEADER
10.Jan.17
7:00
18:00
11:00
9:00
2:00


22SOVELJOY
121TEAM LEADER
11.Jan.17
7:00
18:00
11:00
9:00
2:00


23SOVELJOY
121TEAM LEADER
12.Jan.17
7:00
18:00
11:00
9:00
2:00


24SOVELJOY
121TEAM LEADER
13.Jan.17
7:00
18:00
11:00
9:00
2:00


25SOVELJOY
121TEAM LEADER
14.Jan.17
7:30
17:30
10:00
9:00
1:00


26SOVELJOY
121TEAM LEADER
15.Jan.17
7:30
17:30
10:00
9:00
1:00


27SOVELJOY
121TEAM LEADER
16.Jan.17
7:30
17:30
10:00
9:00
1:00


28SOVELJOY
121TEAM LEADER
17.Jan.17
7:30
17:30
10:00
9:00
1:00


29SOVELJOY
121TEAM LEADER
18.Jan.17
7:00
18:00
11:00
9:00
2:00


30SOVELJOY
121TEAM LEADER
19.Jan.17
7:00
18:00
11:00
9:00
2:00


31SOVELJOY
121TEAM LEADER
20.Jan.17
Worksheet: 121

[DocAElstein]

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

1SOVELJOY
121TEAM LEADER
21.Dec.16
7:00
18:00
11:00
9:00
2:00N


2SOVELJOY
121TEAM LEADER
22.Dec.16
7:00
18:00
11:00
9:00
2:00N


3SOVELJOY
121TEAM LEADER
23.Dec.16
7:00
15:00
8:00
8:00H


4SOVELJOY
121TEAM LEADER
24.Dec.16
7:00
18:00
11:00
9:00
2:00N


5SOVELJOY
121TEAM LEADER
25.Dec.16
7:00
18:00
11:00
9:00
2:00N


6SOVELJOY
121TEAM LEADER
26.Dec.16
7:00
18:00
11:00
9:00
2:00N


7SOVELJOY
121TEAM LEADER
27.Dec.16
7:00
17:00
10:00
9:00
1:00N


8SOVELJOY
121TEAM LEADER
28.Dec.16
7:00
18:00
11:00
9:00
2:00N


9SOVELJOY
121TEAM LEADER
29.Dec.16
7:00
18:00
11:00
9:00
2:00N


10SOVELJOY
121TEAM LEADER
30.Dec.16
7:00
18:00
11:00
10:00H


11SOVELJOY
121TEAM LEADER
31.Dec.16
7:00
18:00
11:00
9:00
2:00N


12SOVELJOY
121TEAM LEADER
1.Jan.17
7:00
18:00
11:00
9:00
2:00N


13SOVELJOY
121TEAM LEADER
2.Jan.17
7:00
18:00
11:00
9:00
2:00N


14SOVELJOY
121TEAM LEADER
3.Jan.17
7:00
18:00
11:00
9:00
2:00N


15SOVELJOY
121TEAM LEADER
4.Jan.17
7:00
18:00
11:00
9:00
2:00N


16SOVELJOY
121TEAM LEADER
5.Jan.17
7:00
18:00
11:00
9:00
2:00N


17SOVELJOY
121TEAM LEADER
6.Jan.17H


18SOVELJOY
121TEAM LEADER
7.Jan.17
7:00
18:00
11:00
9:00
2:00N


19SOVELJOY
121TEAM LEADER
8.Jan.17
7:00
18:00
11:00
9:00
2:00N


20SOVELJOY
121TEAM LEADER
9.Jan.17
7:00
18:00
11:00
9:00
2:00N


21SOVELJOY
121TEAM LEADER
10.Jan.17
7:00
18:00
11:00
9:00
2:00N


22SOVELJOY
121TEAM LEADER
11.Jan.17
7:00
18:00
11:00
9:00
2:00N


23SOVELJOY
121TEAM LEADER
12.Jan.17
7:00
18:00
11:00
9:00
2:00N


24SOVELJOY
121TEAM LEADER
13.Jan.17
7:00
18:00
11:00
10:00H


25SOVELJOY
121TEAM LEADER
14.Jan.17
7:30
17:30
10:00
9:00
1:00N


26SOVELJOY
121TEAM LEADER
15.Jan.17
7:30
17:30
10:00
9:00
1:00N


27SOVELJOY
121TEAM LEADER
16.Jan.17
7:30
17:30
10:00
9:00
1:00N


28SOVELJOY
121TEAM LEADER
17.Jan.17
7:30
17:30
10:00
9:00
1:00N


29SOVELJOY
121TEAM LEADER
18.Jan.17
7:00
18:00
11:00
9:00
2:00N


30SOVELJOY
121TEAM LEADER
19.Jan.17
7:00
18:00
11:00
9:00
2:00N


31SOVELJOY
121TEAM LEADER
20.Jan.17H
Worksheet: 121

[DocAElstein]

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/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10072#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


34TOTAL NO. OF DAYS ----->30

Normal Overtime ----->
Holiday Overtime ----->


35
47
28


36


37For 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


34TOTAL NO. OF DAYS ----->30

Normal Overtime ----->
Holiday Overtime ----->


35
=SUMIF(K1:K31,"N",J1:J31)*24
=SUMIF(K1:K31,"H",J1:J31)*24


36


37For 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 :)

[nelsondavid]

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:)

[DocAElstein]

Hi Nelson,

..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

'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
'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 likeI am sure you can understand how to modify to change which cells are used for where the total appear
_............................................



.... Can i use the same code for every month ...Yes..
. 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..
....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
_................................................. .......



...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.

..The code seems to be working fine..We have given you 2 codes
From Alan
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page2#post10066

From Alan and Admin
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page2#post10072

[nelsondavid]

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:)

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://eileenslounge.com/viewtopic.php?p=244184#p244184 (https://eileenslounge.com/viewtopic.php?p=244184#p244184)
https://eileenslounge.com/viewtopic.php?p=246586#p246586 (https://eileenslounge.com/viewtopic.php?p=246586#p246586)
https://eileenslounge.com/viewtopic.php?p=246112#p246112 (https://eileenslounge.com/viewtopic.php?p=246112#p246112)
https://eileenslounge.com/viewtopic.php?p=246112#p246112 (https://eileenslounge.com/viewtopic.php?p=246112#p246112)
https://eileenslounge.com/viewtopic.php?p=245761#p245761 (https://eileenslounge.com/viewtopic.php?p=245761#p245761)
https://eileenslounge.com/viewtopic.php?p=245722#p245722 (https://eileenslounge.com/viewtopic.php?p=245722#p245722)
https://eileenslounge.com/viewtopic.php?p=245616#p245616 (https://eileenslounge.com/viewtopic.php?p=245616#p245616)
https://eileenslounge.com/viewtopic.php?p=247043#p247043 (https://eileenslounge.com/viewtopic.php?p=247043#p247043)
https://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use (https://www.excelfox.com/forum/showthread.php/2307-VBA-Range-Sort-with-arrays-Alternative-for-simple-use)
https://eileenslounge.com/viewtopic.php?p=245238#p245238 (https://eileenslounge.com/viewtopic.php?p=245238#p245238)
https://eileenslounge.com/viewtopic.php?p=245131#p245131 (https://eileenslounge.com/viewtopic.php?p=245131#p245131)
https://eileenslounge.com/viewtopic.php?f=18&t=31638 (https://eileenslounge.com/viewtopic.php?f=18&t=31638)
https://eileenslounge.com/viewtopic.php?p=244579#p244579 (https://eileenslounge.com/viewtopic.php?p=244579#p244579)
https://eileenslounge.com/viewtopic.php?p=244648#p244648 (https://eileenslounge.com/viewtopic.php?p=244648#p244648)
https://eileenslounge.com/viewtopic.php?p=244647#p244647 (https://eileenslounge.com/viewtopic.php?p=244647#p244647)
https://eileenslounge.com/viewtopic.php?p=244577#p244577 (https://eileenslounge.com/viewtopic.php?p=244577#p244577)
https://eileenslounge.com/viewtopic.php?p=245201#p245201 (https://eileenslounge.com/viewtopic.php?p=245201#p245201)
https://eileenslounge.com/viewtopic.php?p=243975#p243975 (https://eileenslounge.com/viewtopic.php?p=243975#p243975)
https://eileenslounge.com/viewtopic.php?p=243884#p243884 (https://eileenslounge.com/viewtopic.php?p=243884#p243884)
https://eileenslounge.com/viewtopic.php?p=242439#p242439 (https://eileenslounge.com/viewtopic.php?p=242439#p242439)
https://eileenslounge.com/viewtopic.php?p=243595#p243595 (https://eileenslounge.com/viewtopic.php?p=243595#p243595)
https://eileenslounge.com/viewtopic.php?p=243589#p243589 (https://eileenslounge.com/viewtopic.php?p=243589#p243589)
https://eileenslounge.com/viewtopic.php?p=243589#p243589 (https://eileenslounge.com/viewtopic.php?p=243589#p243589)
https://eileenslounge.com/viewtopic.php?p=243002#p243002 (https://eileenslounge.com/viewtopic.php?p=243002#p243002)
https://www.eileenslounge.com/viewtopic.php?p=242761#p242761 (https://www.eileenslounge.com/viewtopic.php?p=242761#p242761)
https://eileenslounge.com/viewtopic.php?p=242459#p242459 (https://eileenslounge.com/viewtopic.php?p=242459#p242459)
https://eileenslounge.com/viewtopic.php?p=242054#p242054 (https://eileenslounge.com/viewtopic.php?p=242054#p242054)
https://eileenslounge.com/viewtopic.php?p=241404#p241404 (https://eileenslounge.com/viewtopic.php?p=241404#p241404)
https://eileenslounge.com/viewtopic.php?p=229145#p229145 (https://eileenslounge.com/viewtopic.php?p=229145#p229145)
https://eileenslounge.com/viewtopic.php?p=228710#p228710 (https://eileenslounge.com/viewtopic.php?p=228710#p228710)
https://eileenslounge.com/viewtopic.php?p=226938#p226938 (https://eileenslounge.com/viewtopic.php?p=226938#p226938)
https://eileenslounge.com/viewtopic.php?f=18&t=28885 (https://eileenslounge.com/viewtopic.php?f=18&t=28885)
https://eileenslounge.com/viewtopic.php?p=222689#p222689 (https://eileenslounge.com/viewtopic.php?p=222689#p222689)
https://eileenslounge.com/viewtopic.php?p=221622#p221622 (https://eileenslounge.com/viewtopic.php?p=221622#p221622)
https://eileenslounge.com/viewtopic.php?f=27&t=22512 (https://eileenslounge.com/viewtopic.php?f=27&t=22512)
https://eileenslounge.com/viewtopic.php?f=26&t=26183 (https://eileenslounge.com/viewtopic.php?f=26&t=26183)
https://eileenslounge.com/viewtopic.php?f=26&t=26030 (https://eileenslounge.com/viewtopic.php?f=26&t=26030)
https://eileenslounge.com/viewtopic.php?p=202322#p202322 (https://eileenslounge.com/viewtopic.php?p=202322#p202322)
https://www.excelforum.com/word-formatting-and-general/1174522-finding-a-particular-word-phrase-in-word.html#post4604396 (https://www.excelforum.com/word-formatting-and-general/1174522-finding-a-particular-word-phrase-in-word.html#post4604396)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

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

.......
P.s.
We have given you 2 codes
From Alan
Post #19 here: http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page2#post10066

From Alan and Admin
Post #20 here: http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)/page2#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/showthread.php/2144-Code-Required-to-calculate-number-of-days-worked-normal-overtime-and-holiday-overtime?p=10058#post10058

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




Alan

[nelsondavid]

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:)

[DocAElstein]

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

34TOTAL 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)

......(holiday is deducted if the person does not come the day before and after the holiday).....:confused: 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

[nelsondavid]

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:)

[DocAElstein]

Hi Nelson
_1)
:confused:
.....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 :confused: ??? 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

34TOTAL 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.16ABSENT


30.Dec.16
7:00
18:00
11:00
9:00
2:00


31.Dec.16ABSENT
Worksheet: 121
????

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

Try again !!!


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

[nelsondavid]

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

[DocAElstein]

Hi Nelson

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)

....Rest all seems to be fine...:confused::mad:

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 ???? :confused:



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

_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 !!!


.....
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.

[nelsondavid]

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:)

[DocAElstein]

Hi Nelson,

...(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 :)
Using Excel 2007 32 bit

BEFORE


29.Dec.16


30.Dec.16


31.Dec.16







AFTER


29.Dec.16ABSENT


30.Dec.16ABSENT


31.Dec.16ABSENT
Worksheet: Sheet1

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



...
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.16ABSENT


30.Dec.16


31.Dec.16ABSENT
Worksheet: 121

It should have been this !!!


29.Dec.16ABSENT


30.Dec.16ABSENT


31.Dec.16ABSENT
Worksheet: 121
_............................

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



Alan

[DocAElstein]

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
:)

[DocAElstein]

Hi Nelson.

Here is the next code version:
Here: ( Post 27 )
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10101#post10101

and here: ( Post 28 )
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10102#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/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10096#post10096
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10097#post10097

After running_..
Sub IJAdjust_LAdd_AbsentKAdd_TotalsFormulas_AllWorkshe etsCode4()
_.. you should get like:
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10098#post10098
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10099#post10099
http://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=10100#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:00N


29.Dec.16ABSENTN


30.Dec.16ABSENT


31.Dec.16ABSENTN


1.Jan.17
7:00
18:00
11:00
9:00
2:00N


9.Jan.17ABSENTN


10.Jan.17
7:00
18:00
11:00
9:00
2:00N


11.Jan.17
7:00
18:00
11:00
9:00
2:00N


12.Jan.17
7:00
18:00
11:00
9:00
2:00N


13.Jan.17
7:00
18:00
11:00
10:00H


14.Jan.17
7:30
17:30
10:00
9:00
1:00N
Row\Col
B
C
D
E
F
G
H
I
J

34TOTAL NO. OF DAYS ----->25

Normal Overtime ----->39
Holiday Overtime ----->18
Row\Col
B
C
D
E
F
G
H
I
J

34TOTAL 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 :)

[nelsondavid]

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:)

[DocAElstein]

... it seems to be working fine..if find any problem will get in touch with you...)
OK :)