Create Mark Formula with airSlate SignNow

Mark weekly timesheet template

[Music] hello friends this is an fully automated employee a tenon shape where you can select the months when you select a different month they start date and in dates automatically changes and the Sundays color and holidays scholar also change automatically when you select a different month and whenever you completed your attendance you can see the total results here and as you completed you attendance for the month of January you can copy the shades month of January that is here it's January and you can clear this data from here with a square button so to know all about this stay tuned with us [Music] 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 shape so it's eat one arrayname eat as helper this helper sheet will help us to make automatic attention and here we will make a list of months and holidays so in cell one type months after that type here January and drag the fill handle up to December so here select this range of months and define a name for this so to do this go to formula tab and click on define name on the name box type months and get ok now we will make a list of holiday here so type here holidays and you 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 let's copy with control C and then go to my new shade and paste it here now select this data range and again define this range as name holiday cake ok now we are done with helper shape we have credit months and holiday list now we will create the attention so in - we will rename it as attendance and this free we have no use of it so let's delete it here on sale a one type attendance for Iman off and then select up to L and Margie then select M to Q and then again Margit and here we were 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 the devaluation this little validation will help us to make a list if you want to learn about data validation you can click on the I button here and get the link in the description below so in allow box select list after that in the source type equal to once forget okay see we have got the list of months and from r2t will again merge it and here we will we put a formula where we will see the year now in situ here we will type start date and and on each two we will type in debt here select this and Marge it then again select all these three and Margot here we will get this start date of the month selected here and in day or the one selected here so first let's select a month the January and now we will put a formula here to get the first date of January so TIFR equal to date value double inverted comma 1 the water close and select the Marcel that is m1 then close it press Enter 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 and why why why so we got the star debt now to get the end date we have to give another formula that is equal to end of month this yo monk and click this third date and then month 0 then close we have got another serial so again convert it as state with former sale custom DD mmm why why why why so we have got the first state and will last it when we change the mark see it changed automatically we selected February now its first familiar to her 19 to 28 February 2019 when you select March see first March 2019 to 31st March 2019 now in a four we will type the employee code and the employee names so I will copy this from my previous seat to 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 read another space so I have to keep two rows blank here and now we will create do it one by one here so here I will give the first date of the month so if I want to be the first month press equal to and then select this one will get the first date of the month so we cannot keep here before date we have two in the first two digit so format 8 from the font cell and go to custom and keep only D D and then k ok we got 0 1 on the next cell we have to be another formularies if these cells less than this cell so we have to keep this cell as absolute so press f4 to make it absolute then comma D cell plus 1 else it will be blank bracket close enter you can see this is a date with serial now format the sale with custom TD and press ok now drag this sale up to date 31st March now select all the columns and then drag it drag the column width to fit we got all the days from date first date to 31st date now we will get all the days or beds here so in this cell put a formula that is equal to text value we will get from C 6 comma format text will be inverted comma D D D and double inverted comma close bracket close and so we got Friday now drag this up to the end so we got all the days of all deaths now type here present days absent days have days work days and present a present percent or absent percent here but we will not do this light we will copy this from here copy and paste it here we got them we will now slightly reduce the column width we have done with it now we will set a helper row here which will help us to make the sheet automatic so here we will we put a formula which will give a result here with numbers that is it will give one if there is no debts and it reveal any numbers that is equal to the dates here it will be 1 2 3 4 or anything and or if other cells will give 0 so how could you do this first click here then press equal to in this case match function will help us first we have to put if bracket open C 6 equal to blank then it will give 1 otherwise it will will give a match formula match C 5 drama these dates that is this formula match will find if this C 6 date is given in holidays that is we put here arrange holidays comma if it found that it will give a number else it will give 0 so it gives us any that is not available that is a wrong formula so let us drag this we can see here 6 & 7 that Dec 20 and 21 so let us go to helper C 20 and 21 this one is 1 2 3 4 5 6 7 so this one is 6 7 and all others are hash tag so that means we do not have any other holidays here so to remove this error we have to put here if error formula if error then equal to 0 then try again track this then we can see these states contain 6 and say all others are 0 if we change the man we can see here this is one this is a holiday because 1st January is in a holiday and they - this is also holiday and all others are 0 and we select February we can see the dates are not here so it gives us a result wrong and here only one to two holidays so we have created this helper row so now I'm going to format these two rules with this formatting so first select this rows then here I will take this color after that I will give a border there is all border and holder now I will keep 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 one and that is true so there is two holidays and for that I am selecting holidays with control then I'm giving green after that I select Sundays with contra pi pressing control and give it a red color but unfortunately when we change the month the day is changed now see here is Awareness Day bursty it's rate and there is no holiday but still is green so for this we have to do a dynamic formatting so with the help of conditional formatting you can do this so first let's remove this formatting by pressing undo and now at first I select this row and go to conditional formatting then press manager tool 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 say it this one that is c5c dollar five equal to sunday and 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 color and border with outline and font color with black and bold then click OK ok then again press ok now we can see the Sundays has been colored now we will do for holidays so we have already selected then manage rule new rule use formula here we'll give another formula equal to count s bracket open we will search these dates with our holiday list so write holidays comma this debt is C 6 so type here C dollar 6 then close the bracket now in the formatting it will be green outline border font with black and bold then press ok ok ok now we can see is green and Sundays are red when we change the month it automatically changes now we have to do this for a rest of the arrange it now select this 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 closed in format we will give only fill color red and border we don't will not give font color or bold ok so it has been done then again new here we will give equal to count if bracket open holidays kkoma c6 bracket close here formatting will be cream and border will be outlined then click ok ok ok now we can see the complete column is colored so again let's check this we take February the column has been changed but the problem is that Sunday's red and here Sunday is green this is because we in conditional formatting we have given all the conditions but here Green is M of the red here is Sunday and a holiday both so the both conditions are yet and the first condition is green so is showing the green so if we put this one averse the green then this problem will be solved say so now we will be border to rest of the ml with hair all borders so now it's all done so now we will keep 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 equal to count if bracket open arrange select the range from here to date first date and then press comma and give P then bracket close enter is showing zero 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 P so now it's showing 31 but here two problems you can see first problem is that these three P's are not allowed because there is no dates but still we can enter value here we have to protect these cells from inputting any value if there is no debts and the second problem is that whether it's 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 debt so to do this first select this and then go to data and data validation here select custom and put a formula that is equal to this cell that means C 6 C dollar 6 less than equal to black then press ok and now see I delete this value and try to put B again it's showing a error that is a value is the value you entered is not a silent here you can also put your custom message by going to data validation and here in ER alert title as warning and here you can write entry is not allowed okay now I press P and is showing warming and entry is not allowed so the first problem is solved and whenever I change this month to January I can put here zolly but if it's February and notice here we cannot put value to it so now we have to solve this counting problem so it's 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 count if there is 0 no Sunday and P so 3 conditions should be made first here should be 0 here should not be Sunday and here should be P so here I will give another formula that is count if s count if s so here count if s comma first criteria and first French I have given then I will keep second criteria second criteria range will be this one and this will be not equal to Sunday then third criteria ranges this 1 comma 0 then press Enter now see the value is 23 is now perfect in this way we have to do absent yet so to do this we also do with count if s formula is equal to count if s first criteria is this comma it will be a comma second criteria range will be this comma less than equal to Sunday OMA third criteria ranges this and criteria will be 0 then bracket close enter there is no absent if I put here absent it will count as 1 but here in Sunday if we put a it will not count here you can see has to a but this count is only 1 so now in the same way we will do how to present respectfully to count if s first range comma first criteria that is H comma second criteria range for ma that is not equal to Sunday comma third criteria range comma 0 bracket those entered here is 0 so I will give here H and H I put to H but here is counting 1 because 1 ish isn't sended it will be not count it and it's on working day of so it will be counted now we will count total working this so how 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 enter so total working day is 23 now we will calculate the present per say and absent pursue to do present percent type the formula equal to bracket opens full present a plus half present in 23.5 bracket closed / total world peace and or absent percent equal to full full day absent / little workdays and now select both of this and give it a percent eighty for Mac so it's showing 93 percent and four percent so it's done here now still we can see some problem that is on sending Pease here and here and holiday also is showing P so the main problem is that we have to hide this so to hide this we have to go to conditional formatting and manage rule we can see this cell text color is black and here also back we have to make it red and green so any formula format font it should be red okay okay so it's not showing and now here font color will be cream and you can see it's still not showing here for the dream so now it's heading so and we delete this all and now I will show you that whenever I put here P the value is not reflected here but if I press P here it get reflected however here if we put H it is not reflected on a half day and if we put here H is reflected and on a for absent we put here a is not reflected and if you put here a is reflected so our problem is solved now here is a here we will do some little formatting here is some mistake let's complete this so move this one here select this boat and merge them select this and March and could this here then more this too and Mars is three soaking up and we will do 4matic jahir we will put the year so the date is here and we will do here formatting of the year a format sale custom why four times and is showing 2019 then select this and we will take auntie pants color is sky-blue and font size little bigger than ah is 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 well present 11 full absent 0 half days or thankful to connect three four days and present is 52% absent is 48 percent now here if we drag this number we 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 rule number 6 so rule number 4 and a row number sorry rule number 5 row 4 and 5 should be locked so here is Row 5 to lock this place f4 and now drag is all ok now here again we have to do this for 5 press f4 for four press f4 is OK and here again how our hub this f4 and for Sunday's is F for now it's ok for working days also or 5 press f4 press ENTER and this will work fine no problem so this has been completed and now the last word is when the attention is completed for the month then we'll have to copy the Safeway come on the name here and after that Athenian shape should be cleared so to do this I will meet to button here insert take the shape right this way give it a color and then copy this with right click and edit here copy and in the home select Angela's it will be bold a little bigger 14 size here and it takes clear shape select this one again we will take the same font same size and bold and now for this two button we will create a Revere macros to do this I have already kept the code so I will just open the codes i Visual Basic to do this we have to go to developer and press a visual basic else if you do not have this developed app you can go to excel option and press here show developer and then okay there is another way you can press 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 then module here I will put the code for copying the seat with the Magnum so here is the code I will select this then copy then go to visual basic and paste it so this is the same name from which a sheet name will be taken so this is M 1 and in M 1 say Sale 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 Baystate here is given c7 to a g7 that means here is c7 from c7 to a g7 say it will clear all the value of this row as much a row as you have you have to do like this as you have completed close this now our work is to assign those macros to this button and right click on this and assign macro now for copying the cell we have to select this one and press ok and after that then select assign macro for clear and clear cells okay now our macro has been assigned to this two buttons one last check copy click I see here is a February created with all the attendance now I again go to a tenant sheet with not for the need any of this ekiden so we will clean this sit now is all cleared we can change the month and start new attendees here so thank you for watching this video till end please subscribe our channel and press the like button and also share this video with your friends who need this thank you very much you