Search results

Hi , Can you upload your workbook with data in it ? Narayan

Hi , See the attached file. Narayan

Hi , The revised code works because of this additional line of code : If Target.Column = 8 Then This checks that the code after this line will be executed only if the column where a change was made was column H ; Starting from column A as #1 , column H is #8. However , the better way to...

Hi , Why do you have two different formulas ? =IF($D7="","",WORKDAY.INTL($E$2-D7,0,HOLIDAYS)) =IF(D16="","",WORKDAY.INTL($E$2+D16,1,1,HOLIDAYS)) Narayan

Hi , No problems. Any doubts / modifications / extensions , please post here in this same thread. Narayan

Hi , It does work. However , the code does not unhide already hidden columns , that is the problem. Try this revised code : Sub hidecol() If Range("D4").Value = 1 Then Range("K:AO").EntireColumn.Hidden = True ElseIf Range("D4").Value = 2 Then...

Hi , You asked one question , and a solution was provided. Without commenting anything on that solution , you are asking another question. Sorry , but this is not how a forum works. When a solution is provided , you have to comment ; either say that the solution works , or say that it does...

Hi , This formula does the job. I do not know any easier way. =("20" &LEFT(SUBSTITUTE(A1,".","-"),8)) + 0 Adding the 0 converts the text string to a numeric value , which is then interpreted by Excel as a valid date. Once this happens , you can format the output cells in dd.mm.yyyy format...

Hi , Can you mention what is your Windows date format ? This is the default date format that Excel will use. Any date formatting that you do within Excel is only if the original data was in your Windows date format so that Excel could recognize the data as valid dates. If your Windows date...

Hi , See the attached file.

Hi , No. I have mentioned the two items of data that are needed. 1. The date and time stamp - that is just one item of data , since a time stamp is of no use without the date also in it. The second item of data which is required is whether a date and time stamp data is an IN punch or an OUT...

Hi , For a normal non-array formula , you type in the formula and press the ENTER key. For an array formula , after typing in , you don't press only the ENTER key ; instead you press the 3 key combination of CTRL SHIFT ENTER ; when you do this , Excel automatically inserts the curly brackets {...

Hi , See the attached file. Note that the formula using the SMALL function is an array formula , to be entered using CTRL SHIFT ENTER. Narayan

Hi , I am not able to understand how a formula can be added in cell B4 , which already has data in it. Can you please mention which worksheet is involved ? Narayan

Hi , Excel versions after Excel 2010 have the CONCAT and TEXTJOIN functions which are more powerful than the CONCATENATE function. Using them will simplify such formulas. Narayan

Hi , The SMALL function or the LARGE function has the following syntax : =SMALL(range , k) where range is a single or multi-row , single or multi-column reference , and k is an integer taking the values 1 , 2 , 3 ,... Depending on the value of k , the SMALL function will return the smallest...

Hi , If your attendance data contains just two items : 1. Date and time stamp 2. Whether it is an IN punch or an OUT punch a formula can be given which will be 100% accurate and reliable. However many times an employee punches IN or OUT on the same date , and even at the same time. In the...

Hi , The formula in column L is also a calculation ; in the absence of confirmatory data , you will never know whether the calculation is correct or not. In the uploaded file , if we look at rows 69 downwards , there is no clarity on whether someone has done 2 shifts or is a time punch...

Hi , I am sorry , but based only on the timing data , it is impossible to be 100% correct reliably. There has to be additional data on whether a time is an IN time or an OUT time ; this is all the more necessary because the shift times overlap. Narayan

Hi , See the attached file. Narayan

Hi , See if this is OK. Narayan

Hi , Try this : =SUMIFS($B$6:$B$292, $A$6:$A$292,">=" & C5, $A$6:$A$292,"<" & (C5 + "00:30:00")) Enter this in cell D5 and copy down. Narayan

Hi , Why are you using a WindowChange event when earlier you were using a SheetChange event ? Narayan

Hi , Can you explain the following : 1. I would like to start with only the input data , and as far as I can see , only column D fits this definition. 2. What is the relevance of the data in column E , and why are there so many consecutive Clock ins ? Do we ignore this column ? 3. If we...

Hi , Blinking is not easily done in VBA. It is easier to change the colour of the cell where a data validation selection has been done. Is this acceptable ? Narayan