How to make an automated attendance sheet in excel with formula(2019) (V2.0)

Hello friends this is an fully automated employee  attendance sheet where you can select the months   when you select a different month the start  date and end date automatically changes and   the Sundays colour and holidays colour also  changed automatically when you select a   different month and whenever you completed your  attendance you can see the total results here   and as you completed your attendance for the  month of January you can copy the sheets month   of January that is here is a January and you can  clear this data from here with this clear button   so to know all about this stay tuned with us if you want to learn how to make a automatic   attendance sheet then see this video  very carefully do not miss any part   of it and make sure you subscribe our  channel to get more videos like this  so let's start the video first we will take a  new workbook here we will first make a sheet   that is helper sheet so sheet 1 rename it as  helper this helper sheet will help us to make   automatic attendance sheet and here we will make  a list of months and holidays so in cell A1 type   months after that type here January and drag the  fill handle upto December so here select this   range of months and define a name for this to do  this go to formula tab and click on define name on   the name box type months and hit ok now will make  a list of holiday here type here holidays and we   will type a list of holiday names and dates here  but I am not going to type here and I will copy   from another sheet so here is my list is already  selected lets copy with Ctrl+C and then go to my   new sheet and paste it here now select this date  of rangeage and again defined this range as name   Holidays now we are done with helper sheet we  have created months and holidays list now we   will create the attendance sheet so in sheet 2  we will rename it as attendance and this sheet   3 we have no use of it so let's delete it here on  cell A1 type attendance for the month of and then   select up to L and merge it then select M to Q and  then again merge it and here we are going to build   a list of month in this case data validation will  help us to get this we have to go to Data tab and   click on Data Validation and click Data Validation  this Data Validation will help us to make a list   if you want to learn about validation you can  click on the i button here and get the link in the   description below show in allow box select list  after that the source type equal to months then   click OK see you got the list of months and from  R to T we will again merge it and here we will we   put a formula where we will see Year the now in C2  here we will type Start Date and and on H2 we will   typeEnd Date here select this and merge it then  again select all these three and merge it here we   will get the start date of the month selected  here and end date of the month selected here   so first let's select a month January and now  we will put a formula to get the first date of   January so type here equal to datevalue double  inverted comma 1 double inverted comma close   and select the month cell that is M1 then close  it make it centre you will get the serial value   of the date now we have to format it as date so  to do this we have to right click and click on   format cells here we have to go to custom and  replace the general with dd mm yyyy so we got   the start that now to get the end date we have  to create another formula that is equal to end   of month that EOMONTH and click the start date and  then month 0 then close we have got another serial   so again convert it as date format cell custom  dd mm yyyy so we have got the first date and the   last date when we change the month see it change  automatically so we selected february now 1st   february 2019 to 28th february 2019 and when we  select march see 1st march 2019 to 31st march 2019  Now in A4 we will type the employee code and  the employee names so I will copy this from my   previous sheet so here I select this and copy it  and then paste it here so I got it wrap text I got   all the names sorry I have to another space so I  have to keep two rows blank here and now we will   create date one by one here I here I will get the  first date of the month so if want get the first   date press equal to and then select this one we  will get the first date of the month so we cannot   keep the full date we have to keep first two digit  so format it from Format cell and then goto custom   and keep only dd and then click ok we got zero  one on the next cell we have to give another   formula that is if this cell is less than this  cell so we have to keep this cell as absolute so   press F4 to make it absolute then comma this cell  plus one else it will be blank bracket close enter   you can see this is a date with serial now format  the cell with custom DD and press ok now drag the   cell up to date 31st March now select all the  columns and then drag it till the column width   fit we got on the days from date first date to  31st date now we will get all the days of dates   here so this cell put a formula that is equal to  text value we will get from C6 comma format text   will be double inverted comma DDD double inverted  comma close bracket close enter so we got Friday   now drag this upto the end so we got all the days  of all the dates now type here present Days Absent   days half days work days and present percent or  absent percent here but we will not do this like   we will copy this from here copy and paste it  here we got them we will now slightly reduced   the column width done with it now we will set our  helper row here which will help us to make the   sheet automatic so here put a formula which will  give a result here with numbers that is it will   give one if there is no dates and it will give a  number that is equal to the dates here it will be   1 2 3 4 or anything and other cells will give zero  so how could we do this first click here and then   press equal in this case match function will  help us first we have to put if bracket open   C6 equal to blank then it will give one otherwise  it will give a match formula match C5 comma this   date that is this formula match will find if the  C6 day is given in holidays that is we put here   range holidays comma if it found it will give a  number else it will be zero so it gives us NA is   not available it is wrong formula let drag this we  can see there 6 and 7 that date 20 and 21 so let's   go to helper see 20 and 21 this one is 1 2 3 4 5  6 7 so this one is 6 7 and all others are # tag   so that means we do not have any other holidays  here so to to remove this error we have to put   here iferror formula iferror then equal to zero  then again drag this then we can see these days   contains 6 and 7 all others are zero if you change  the month we can see here this is 1 this is a   holiday we got 1st January is in holiday and day 2  this is also holiday and all others are 0 and will   select February we can see date are not here so it  gives us result 1 and here only 1 2 two holidays   we have created this helper row so now I am going  to format these two rows with this formatting so   first select these rows then here I will take  this color after that I will give a border that   is all border and bold now I will give another  formatting here that is days which have holidays   will be green and the days which will be Sunday  that will be red so first I'm taking January is   a month and I can see there is 1 and there is 2 so  there is two holidays and for that I am selecting   holidays with control then I am giving green after  that I select Sundays with control by pressing   control and give it a red colour but unfortunately  when we change the months the days changes now see   here this is Wednesday but still its red and  there is no holiday but still it is green so   for this we have to do a dynamic formatting so  with the help of conditional formatting we can   do this so first let's remove this formatting by  pressing undo and now at first I select this row   and photo conditional formatting then press manage  rule and new rule after that I Press use a formula   to determine which cell to format here I have to  give a formula that is equal to or select this one   that is C5 C Dollar 5 equal to Sunday then bracket  close after that what will be the formatting the   formatting will be is for Sunday so the formatting  will be red so go to fill and give it a red colour   and border with outline and font colour with black  and bold then click ok ok then again press ok now   we can see the Sundays has been coloured now we  will do for holidays so we are already selected   then manage rule new rule use formula here will  give another formula Equal to countif bracket open   we will search this dates with our holiday list so  write holidays comma this date is C6 so that type   here C dollar 6 then close the bracket now in  the formatting it will be green outline border   font with black and bold and press ok ok ok now we  can see this is green and Sundays are red when we   change the month it automatically changes now we  have to do this for rest of the attendance sheet   now select these and we have to fill the same  formula here again use a formula to determine   which cell to format then press here equal to  or bracket open C5 equal to Sunday bracket close   in format we will give only feel colour red and  border we will not give font colour or Gold ok so   it has been done then again I'm new here we will  give equal to countif bracket open holidays comma   C6 bracket close here formatting will be green  and border will be outline then click ok ok ok   now we can see the complete column is coloured so  again let's check this we take February the column   has been changed but the problem is that sunday  is red and here Sunday is green this is because   we in conditional formatting we have given all  the conditions but here green is above the red   here is Sunday and holiday both so the both  conditions are met and the first condition is   green so it's showing the green if you put this  one above the green then this problem we will be   solved so now we will give border to rest of the  table with here all borders so now it's all done  So now we will give formula to present days so  how we will give the formula for present days   for that first we have to begin with a simple  formula simple formula equal to countif bracket   open range select the range from here to the  first date and then press comma and give p   then bracket close enter its showing 0 so now I  will put present value to all of the cells here   and we can see the present day is increasing day  by day when I put it so now it's showing 31 but   here two problem we can see first problem is that  this three p’s are not allowed because here is no   dates but still we can enter value here we have  to protect themselves from in putting any value if   there is no dates and the second problem is that  Sunday or holiday is also counts the days so first   we will solve the problem so that we cannot put  any value in this cells if there is no dates so   to do this first select these and then go to data  and data validation here select custom and put a   formula that is equal to this cell that means C6  C dollar 6 less than equal to blank and press ok   and now see I delete this value and try to put P  again its showing error that is the value entered   is not a valid here you can also put your custom  message by going to data validation and here error   alert title as warning and here we can write entry  is not allowed ok Now I press p and its showing   warning and entries not allowed so the first  problem is solved and whenever I change to this   month to January I can put here but if it's  February and no dates here we cannot put value to   it so now we have to solve this counting problem  if already showing 28 but it will be 2 then this   and this this this one this one so counting will  be 23 so how would we count we should count the   P if there is no Sunday and no holiday that means  we’ll count if there is 0 no Sunday and P so three   condition should be mate first here should be  zero here should not be Sunday and here should   P so here I will give another formula that is  countifs countifs so here countifs bracket our   first criteria and first range I have given then  I will give second criteria, second criteria range   will be this one and this will be not equal to  Sunday then 3rd criteria range is this one comma   zero then press enter now see the value is 23 its  now perfect in this way we have to do absent here   so to do this we also do with count if s formula  price equal to count If s first criteria is this   coma it will be A criteria range will be thsh  comma less than equal to Sunday comma third   criteria range is this and criteria will be zero  then bracket close enter there is no absent if   I put their absent it will count as one but here  is Sunday if we put a it will not count here you   can see two A but this count is only one so now in  the same way we will do half the present is equal   to count If s first range comma first criteria  that is H coma second criteria range comma that   is not equal to Sunday comma third criteria  range comma 0 bracket close enter here it is   zero so I will give here H and H I put two H but  here count is one because one H is in sunday it   will not be counted as one on working days so it  will be counted now we will count total working   days so how we will we count total working days  so here we will leave this criteria so equal to   count if S first criteria range is this one  not equal to Sunday and second criteria range   is 0 and enter so total working day is 23 now we  will calculate the present percent absent percent   so to do present percent type the formula equal  to bracket open full present a plus half present   into 0.5 bracket close divided by total workdays  and for absent percent equal to full day absent   divided by total work days and now select both of  these and give it as a percentage for its showing   93% and 4% so it's done here still we can see  some problem that is on sunday p is here and   here and on holiday also showing P so the main  problem is that we have to hide this so to hide   this may have to go to conditional formatting and  manage rule we can see this cell text colour is   black and here also black we have to make it red  and green so edit formula format font it should   be read ok ok so it's not showing and here font  colour will be green and you can see is still not   going here for the green and now its hidden so I  will delete this all and now I will show you that   wheneverI put here P the value is not reflected  here but if I press the here it gets reflected   however here if we put H it is not reflected on  half-day and if you put here H it is reflected   and for absent we put A is not reflected and if  we put here A reflected so our problem is solved   now here we will do some little formatting here is  some mistake let's complete this so move this one   here select this both and merge them select this  and merge and put this here than merge these two   and merge these three so ok now and we will do  formatting, her we will put the year so the date   is here and we will do here formatting of the Year  format sale custom Y 4 times and its showing 2019   then select this and we will take Antsypants  colour is sky blue and font size little bigger   no s looking good now here I have entered the  attendance for all those employees now we will   see the result of this attendance here I can see  12 present 11 full absent 0 half days 23 workdays   and present is 52% absent is 48% now here if you  drag is number you are not getting the result why   because here we have not locked this one and this  one so this one is Row Number 4 and this one is   row number 6 so row number 4 and row number sorry  row number 5 row 4 and 5 should be locked so here   is row 5 to lock this press F4 and now drag it's  all ok now here again we have to do this for five   press F4 for 4 press F4 ok and here again for  half day F4 and for Sunday's is F4 now it's ok   for working days also for 5 press F4 press enter  and this will work fine no problem so this has   been completed and now the last work is when the  attendance sheet is completed for the month then   we have to copy this sheet with the month name  here and after that attendance sheet should be   cleared to do this I will make two button here  insert take the shape right this way give it a   colour and then copy this with the right click and  edit here copy sheet and in home select Andulas   bold and little bigger in size edit text clear  sheet select this one and again we will take   the same font same size and bold and now for this  two button create a VBA macro to do these I have   already kept the code so I will just open the  visual basic application to do this we have to   go to developer and press visual basic else If you  do not have this developer tab you can go to excel   option and press here show developer and then ok  another way you can press the right click on the   sheet name and click on view code then here I will  create a code to do this right click on insert   module here I will put the code for copying the  sheet with the month name so here is the code I   will select this then copy then go to Visual Basic  and paste it so this is the cell name from which   the sheet name will be taken so this is M1 and in  M1 see my cell has written February so my sheet   name will be February now it's done close it then  click another module here I will put clearing the   cells code so here paste it here it is given C7  to AG7 that means here its C7 from C7 to AG7 see   it will clear all the value of this row as much  as you have you have to do like this as you’re   completed close this now our work is to assigned  those macros to this button and right click on   this and assign macro now for copying the cells  sheet we have to select this one and press ok   and after that then select assign macro for clear  sheet then clear cells ok now our macro has been   assigned to this two buttons now let's check check  copy sheet click I see here is a February sheet   created with all the attendance now I again go  to attendance sheet with not for the need any of   this attendance we will clear this sheet now its  all cleared we can change the month and start new   attendance here so thank you for watching this  video till end please subscribe my channel and   press the like button and also share it with  your friends who need this thank you very much

Channel: Saha Computer Education Centre

Views: 800,480

Rating: 4.8526673 out of 5

Keywords: how to make automated attendance sheet in excel, attendance, attendance sheet in excel with formula, how to create attendance sheet in excel, saha computer, attendance sheet format, fully automated attendance sheet in excel, automated attendance sheet, automated attendance sheet in excel, automatic attendance sheet in excel, attendance sheet in excel, labour attendance sheet in excel format, attendance sheet, attendance register format, labour attendance sheet in excel, How to

Id: D4oqM2tOvTI

Length: 32min 47sec (1967 seconds)

Published: Tue Aug 06 2019