The sheet of the accounting of working hours in Excel ATTENTION! Long (35 min.). Tiresomely. Perhaps too long and too tiresomely. If you hurry, then do not waste time for viewing of this roller. In vain you will lose BP...  See details »

 
Video subtitles:
  • 00:00: in this video I will show both can excel create timesheets working time thus we will not use macros to build this report cards will use only excel functions first I'll show you what we will reach the end roller in se2 cell They will be stored per month Kiddie things and their 2 year It may be administered directly into cell and can take advantage of relevant counters times note that when we
  • 00:31: me a month will the game automatically will bear dates this month, for example, in April and in 30 days March 31 February 29 or 28, if the leap year for example in 2016 under it is 29 and if the year is not leap like In 2015 it will be 28 days then selected
  • 01:02: month our report cards will be output Saturday and Sunday the letter and in the same letter v will be in a holidays in as a holiday days I took those days that are holiday The Republic of Belarus eg January this is a new year January 1 Christmas January 7 February holiday
  • 01:31: days there in March this Women's Day March 8 April holiday days did not seem in May This May 1st is a day victory on May 9 and it Radunitsa May 10, 2016 In addition, the will scale calculate the total number of hours which has worked if people in the past month
  • 02:03: person was on vacation it will be necessary enter Russian or English on and excel considers amount of days leave for the person if the person ill and was the hospital must enter the letter b excel count how many people sick days In addition, we
  • 02:30: We count the number working days ie how many days he was not I have been ill on holiday and these days do not fell to or output robust day except that we enumerate our line thus that if used as sorting the will be automatically trained right line in this example sailor is not the case It is number one ball two kinds Fedor 3 if we go on
  • 03:01: tab data to sort e.g. surnames we see what number one is food Fedor if we for example sort the Descending delivery we see that the number one has a ball for it uses the function of the neck as well but mani and that our case weekend automatically stand pouring using conditional formatting If you find that
  • 03:31: this information It is for you interest you can listen clip on if it not what you expect the same time interrupt viewing and look for something more and so I I turn to how create a report card I'll start with the fact that I did not I want to do it with blank sheet I prepared in advance some stuff Now I want add counters that will change the month and year it does not
  • 04:00: It needs to tab Developer to display then click on the file Options here configure and tape the right to select check box to the left of the tab then Developer click on the ok button feeds Add Developer tab go to the tab developers here group elements management has Insert, and we
  • 04:30: We can insert or control form an element their technics but an element of active x require a book support macros so we will use it elements forms of governance select the counter and put it to the right cell C2 then take and copy inserted counter
  • 05:01: dec2 cell for this drag counter holding down dissent is now key you have two iconic counter customize their fit a way for this to The first counter which are located 2 things brush by him Right-click and in the context menu select object in the window format point out the current a value of 1 because
  • 05:30: HIV eyki se2 we have kept the number of the minimum unit value is also 1 maximum value of 12 clear all have 12 months step 1 and change the most important relationship Click cell on xi2 cell now move on Properties tab, uncheck print to the object does not Print and was
  • 06:00: visible only to screen, press ok similarly Right-click click on the second counters and choose format command object take off output to Printing on the move tab element control current the value of 2016 minimum value 2000 Max 2050
  • 06:32: step-change unit communication cell scholknem di2 on cell then click the button Now is ok scholknem counter and check how the counters pressed triangles up we will increase the number of novice reaching 12 then the value is not go and can only similarly reduce year can be increased or n6
  • 07:00: So give to the year current 2016 a month made equal unity that is, January is now sixth row Parting days of January 2016 actually we will put the wrong of this year who choose to Go cells se2 2 for that we use function that is ours and
  • 07:30: so the same date means Now I'm there form a date 1 January 2016 We proceed as call column function column without arguments are Returns the number of column in which function is called in our case the function and a column called this 5 column and I want
  • 08:02: that there was number 1 so that you can but do minus 4 better to do less column, and specify cell reference D6 and the link do exactly the is actually that it says you need of the column number and subtract the number of the column cell D6 that is from 5 we subtract 4 we get unit when the formula
  • 08:30: copied to the right there will be a need of column number f you honest column These checks deep6 ie from six subtract 4 to 2 g column, we have seven under 4 will be deducted three, and so on then we can have get number one two three four and so on until 31 after that use text operator string concatenation
  • 09:00: ampersand that is, we add to the number of unit string constant. actually share components so you then through point add the month and reference is made absolute beginning click f4 key then again add a point and
  • 09:34: add a reference year made again absolutely and so It happened function date means is converted into a string which stores data Date number format to close the bracket VAL function and date press the kontrol's how We see all wells
  • 10:00: range introduced formula that correctly arranges dates the specified month on Actually not quite right and we see if the change the best month of the year eg do February since the year the leap of 29 days and two cells return an error if the year is not leap mistake 3 cells will be if month will be the mat
  • 10:31: there are no errors 31 One day in April error, and so on then is what happened to fix it mistake we just use wrapper function if an error scholknem after sign equal and introduce a keyboard if the error is put semicolon and we point out that this case in this case,
  • 11:00: we want to display an empty string again the control information here power in all cells Now the formula works correctly highlighting the information we need such as the month of May lead 31 April 30th missing day It will be displayed in empty line Now back back for a month January and not helpful
  • 11:32: I would see the day for this week Select the cell in sixth row on Primary tabs call the command number format Select here all and changed formats format format favor of custom for add this in front three letters etc. the gap we will see the day of the week if I can fix
  • 12:00: alignment middle, so for example the first number is Monday 2 January this Saturday and so Further below, we are now we want to place letter at the weekend and holidays in the remaining working days we want to put 8 hours multiplied by rate at the beginning decide simpler task We will not be taken into account holidays but only as a weekend This we shall do
  • 12:30: write case if the same formula the logical no expression of the day function protects Day weeks from the cell and 6 is applicable link mixed fixing sixth row and leaving relative column. point
  • 13:00: 22 means that we Week numbered with Monday and Monday corresponds to the number 1 So if the day of the week will we have more then 5 there are six Saturday or 7 Sunday in this case we will Russian return A capital letter in pay attention that
  • 13:30: not the English letter b Russian and here I do then it is important to be when to use conditional formatting otherwise ie if day is not weekends we will a rate that is d7 where fixed d column multiplied by 8 hours to close the bracket functions if and press the n kontrol we will see that xr
  • 14:02: Weekend example Saturday and Sunday melted in a letter on weekdays, he pointed number of hours which has worked man while formula does not work absolutely correct why if we change the month the euro is the last two days whether error message we already know how it need to fix wrap formula function if an error if an error if
  • 14:32: an error in these formulas we just going return empty row now now our formula works correctly then I I want to output days allocated automatically casting for this can take advantage of conditional formatting on the main tab
  • 15:00: Group scholknem styles conditional command formatting and choose the Right then select cells still here introduce Russian letter in if you make a mistake and gli English letter is used you have here English input letter b then you can specify the type perm you want
  • 15:31: I'll use format custom Click the fill will show such that the output days, I want to fill in yellow itself font do red devour Okay so let's see how the information at
  • 16:00: change has for example in February weekend is the sixth and the 7th number in March fifth weekend the 6th and so on really released only days that are output Now we see that January January 1 day holiday and wanted It falls on a Friday we would like to the letter see here similar to January 7
  • 16:31: this Christmas too good day we I would like to 50 letter g for that we sheet is required in which will lists all for the holidays period that you You will use to form dates in this case, Between 2000 and 2005, 5 I had prepared such sheet I lists all holidays that the republic has Belarus for the period from 2000 to 2010
  • 17:03: Of course, I did not introduce them but manual mode In order for this lesson It was shorter than I want yet not to tell how I I create a list These events will consider that this one thing I found in Internet and so Now I have this list I will copy and pasting your document
  • 17:31: report card is now in my report card came Sheet the holidays list I will highlight all a cell in which kept holiday start and barely ending a52 unit then this I have a range of cells APPROPRIATE name for the holidays which account but in the field behalf and write the holidays
  • 18:04: then press Now enter key From any I could list address range holidays then we can fix our formula thus to the letter v It stands out not only weekend Well, for the holidays This again select wherein the range stored weekend
  • 18:32: any quantity who worked hours people that day active cell leaving remains Box and 7 press the f2 key and fix the cell shape and 7 that we will do, we change the condition at which inspection I put the letter in to after this scholknem parenthesis function if
  • 19:00: and write a new or his or day of the week more than 5 semicolon or we point Now write feature that check whether this day weekends will be the next function and 6 wherein in this formula
  • 19:35: we fix six row anyway holidays
  • 20:02: 6 is equal to the comparison and holidays it will do it will compare 1 January 2016 in each cell and the range of holidays we actually get logical array who will stand mostly from value lies and only to the cell which houses the January 1 2016 will stand and brush on so Now this check
  • 20:30: we surround the sum function works sung product and the idea that we can poidee would have to receive what we need if pixel is made implicit conversion types Unfortunately this is not doing so we you need to specify that we We want to have the logical instant and there is a lie explicitly convert number
  • 21:00: of the unit was able to spoon zero so I will use the than the number and will transform logical constants zero or one, and so make The SUM function she works check whether the same contents of the cell and 6 with cells in range of holidays get the logical there is an array of false dreams
  • 21:30: converts the function h this array in the toe units and amounts works just about sums up all elements of this array now we need to check out what we We received more than zero if the day is holiday here there be at least 1 units and may and be more in the case when e.g. Radunitsa will different from the first May or May 9, and so
  • 22:02: close round bracket for functions If you now click kontron enter, we changed our formula and formulas It takes into account not only weekends but eg holidays we see that on 1 January marked with the letter b although this Friday but 1 January is the new we This year the holiday day
  • 22:30: similar to January 7 stands by the letter v although this is Thursday but January 7 we wait for the CTBA of Christ at last residence permit Similarly, in March month we see what stands out March 8 really March 8 is the day women, etc. for example if we May we look at We see that on 9 and 10 May
  • 23:03: are holidays look really May 9 Victory Day May 10 I Radunitsa I missed the May 1 really May 1st We had Sunday and so the holiday coinciding with the output day was not seen and so
  • 23:30: there are very slightly left calculate the total number of hours I worked every man for this, select appropriate range, conduct the same formula All amounts range cell is Now press the left kontrol we will see number of hours who worked Each man now We count the number vacation days for
  • 24:00: this select three cell in the column and Kay and we see the same formula if we indicate by wherein the range we We count the number vacation days; and as a criterion introduce in double quoted Russian letter by the correct formula However, when working user can not be administered Russian letter o English letter and at
  • 24:30: visually they are not differ but our the formula will not considered these days leave so it makes sense Add another account if you specify the same most range and as a criterion has not entered Russian and the English letter and at uppercase and lowercase xr letters do not distinguish between
  • 25:00: by function if However, the English and Russian distinguishes it com to conduct similar We count the number sick days select the range column hey hey and see formula if the same account Specify a range of in which we believe hospital. point and as a criterion entered the Russian letter b counter but for
  • 25:35: to count the number of workers days use function by function account says a number of range equal score and specify the range where we want find the number working days now create a Do our header table for this purpose in fourth row
  • 26:00: select cells which will use as a title or these cells united Babkin take cell formula equal and we refer to cell and 6 Contra year we will see in this date the format that cell was 6 and now I I want to correct look this cell for this click the button
  • 26:32: dialog box choose the number of all Formats and indicate this format We start with a double quotes Russian word timesheets
  • 27:01: time for space and close double quote Now we write 4 uppercase letters m space 4, uppercase letter r
  • 27:30: We put double quote gap year close double quote Click ok now visible that when we change the month we changed name in cell A4 when changing year similarly e4 cell will change year not only that
  • 28:00: I like that name of the month It is written with a capital letters is also possible correct and fairly easy, we need again go to Dialogue at the window number and you began to put code page Russian so way we point mark f 419 dollar hyphen
  • 28:31: in the sample is already visible that the word January It is written with lowercase letter with no capital as before scholknem okay and We shall see that yes really when It is changing the whole month It is written with a lowercase letters recently left to number our employees such way to the numbering remained at sorting at hide some lines for this
  • 29:00: Select the range and 7 and 9 and conduct formula in the same unit as a function we will use by function ie We count the number values ​​in the range as parameters Specify options 5, that is, we will pass only hidden rows and
  • 29:30: reference 3 argument we give hey range 6: 6 and wherein the left upper angle will fix make it is now the absolute you can complete the form pressing the control center really shall see that the formula It is working correctly isolate any cell big column and move on
  • 30:02: on the data tab, sort the on alphabetically from A to Z now seen Matroskin was the first after sorting The first issue will be Harry Fedor if we isolate any cell column and de sort the on Descending we rate we see that the number 1 will have the ball so manner really when
  • 30:30: We produce sorting functions unit renames order line try and hide eg first line 3 Fedor see that Matroskin rooms on verbatim If we map this line we will see we Uncle Fedor It is number one similarly, we can sing any row in the middle of the end etc., in principle, we have achieved that that We wanted to do at the beginning of this movie
  • 31:00: however I recommend to create this the document template that it would be more convenient for use it for This will enter the menu Choose file save as then click the button review here point out file type that we want to do not excel book and we want to create excel template template automatically will remain in special folder custom
  • 31:30: Office templates us left to document gimi Table of expansion heal the x ie template excel add automatically click the button retain xr retain our template in excuse in the book folder Office templates now I I close this template then counting the menu Choose file
  • 32:00: create here Select Personal Templates in this group we one pattern is click on the table report card and we will create New report cards in this case since January 2012 and I want to like do well for February I change month dvoechku to say in Uncle Fedor in February us sick since February 8,
  • 32:30: to 23 February We put the letter b press contra combination of keys Now excel considers that It was 16 days disease and let the ball was on vacation 1 by February 17 Goodwin introduce cell Well, in my case, I I drive letter of the Russian
  • 33:01: we got 17 days leave now we can keep our document save file Select a folder to storage world Documents give them document the name of the table for February 2016 save
  • 33:32: if we need the new report cards I close this document we menu go again file scholknem team choose to create a personal templates scholknem on the template we will create a report card new document template based timecard we indicate we need For example, this month It will be the month of March year 2016 if people were
  • 34:01: eg hospital ill Matroskin starting with 7 mark March 11, we Highlight and because he letter g sorry letter b sick to control so we We see five days disease all paper You can save give the name of the swamp in March
  • 34:34: 2013 2016 Sorry, the in this way It announced in early roller goal reached I hope it was good for you