Thanks for the good video. Regarding your example, I have placed the days of the month vertically and I use not the column function but the line and it works perfectly. If we have 10 employees, the working days are placed vertically at the beginning of the work type and concern all 10 employees. But in the calculation for working hours and overtime pay, For example we have payments in the first 8 hours of work ($1.00) X 1, in the 9th hour ($1.00) X 1.20, in the 10th hour ($1.00) X 1.40(only 150 hours per year allowed), 11th hour ($1.00) X 1.60, more than 11th hour worked ($1.00) X 2.20, also night hours get an extra ( $1.00) X 0.25, also for offsite work (work in another city) they get an additional 10% or 15%, so I have them placed in different columns. The first question is what formula should I use? and the next is the following: on Saturday the first 8 hours of work ($1.00) X 1.30 and overtime on Saturday ($1.00) X 2.30 and on Sunday or a public holiday the first 8 hours ($1.00) X 1.75 and overtime ($1.00) X 2.75 but in Timesheet Automation we have the days of the week in some cells and Saturday and Sunday in some others, next month they change position, so a formula e.g. (if(Sunday=(calculation of hours or overtime ); if(not Sunday); "blank cell" same for Saturday. Also for days of the week e.g. (if(Monday, Tuesday, Wednesday, Thursday , Friday =(calculate hours or overtime ); if(not Monday, Tuesday, Wednesday, Thursday, Friday); "empty cell") Also, how will the formulas contain if(official holiday =(calculation of hours or overtime); if(not official holiday);"empty cell" Thanks for the help and I apologize if I bore you with the long comment and I look forward to your help ps : all the above are for example for the production of the formulas and do not really exist!
Interesting, the way to go about this is to calculate the combined factor (multiplier) that the hourly rate should be multiplied by. since we have few of those, the best approach is to test each condition (you get a TRUE or FALSE result) and then multiply it by the factor you are testing for and multiply it by the hourly rate. do this for the conditions and add them up. the formula is something like this: 8 x StdRate + [is total hrs more than 8] x (StdRate x 1.2) + [is total hrs more than 9] x (StdRate x 1.4) + [is total hrs more than 10] x (StdRate x 1.6) + [is total hrs more than 11] x (#Hrs - 11) x (StdRate x 2.2) + ........etc. you are not going to be using IF statements for checking the hours you just use a logical statement that result in TRUE or FALSE like (#Hrs > 9). the formula can get bigger with all the conditions you have but a good practice is to use Alt+Enter to add new line in the formula bar and it the formula would still work. this is good to having an easy-to-read formula and limit errors.
Very use full ur video but if i am enter one monh time sheet completely then i will go next month my previous month record will not come as weel ass i will record when previous month details also .any idea?
Yes, because the template is designed to handle one month at a time, so each month will have its own file. if you want to keep a history in the same sheet for all the months, design changes need to be made, first data entry will be in a separate table and the timesheet report will read from the data entry table and display it accordingly. Actually, when done this way you can also include employee names as well. However, it is not a simple project, but I agree it is very useful, thanks for sharing your thoughts. I have done something simiar on the channel, check the VertiCAL strategic calendar video, there should be a link to download the file.
I see, open the template in Excel use Ctrl+G to open the go to window, go to special and select formulas, this should select all cells with formulas, once selected hit the Delete key. also open conditional formatting and click manage rules and delete all conditional formatting.
![Create a TIMESHEET Template using an Excel Spreadsheet - [ 15 Minute Tutorial ]](/img/n.gif)
Awesome tutorial, clearly explained, thanks.
Thank you for your feedback, glad you found it clear and helpful, this feedback helps me a lot 🙏😊
Thanks for the good video. Regarding your example, I have placed the days of the month vertically and I use not the column function but the line and it works perfectly. If we have 10 employees, the working days are placed vertically at the beginning of the work type and concern all 10 employees. But in the calculation for working hours and overtime pay, For example we have payments in the first 8 hours of work ($1.00) X 1, in the 9th hour ($1.00) X 1.20, in the 10th hour ($1.00) X 1.40(only 150 hours per year allowed), 11th hour ($1.00) X 1.60, more than 11th hour worked ($1.00) X 2.20, also night hours get an extra ( $1.00) X 0.25, also for offsite work (work in another city) they get an additional 10% or 15%, so I have them placed in different columns. The first question is what formula should I use? and the next is the following: on Saturday the first 8 hours of work ($1.00) X 1.30 and overtime on Saturday ($1.00) X 2.30 and on Sunday or a public holiday the first 8 hours ($1.00) X 1.75 and overtime ($1.00) X 2.75 but in Timesheet Automation we have the days of the week in some cells and Saturday and Sunday in some others, next month they change position, so a formula e.g. (if(Sunday=(calculation of hours or overtime ); if(not Sunday); "blank cell" same for Saturday. Also for days of the week e.g. (if(Monday, Tuesday, Wednesday, Thursday , Friday =(calculate hours or overtime ); if(not Monday, Tuesday, Wednesday, Thursday, Friday); "empty cell")
Also, how will the formulas contain if(official holiday =(calculation of hours or overtime); if(not official holiday);"empty cell"
Thanks for the help and I apologize if I bore you with the long comment and I look forward to your help
ps : all the above are for example for the production of the formulas and do not really exist!
Interesting, the way to go about this is to calculate the combined factor (multiplier) that the hourly rate should be multiplied by. since we have few of those, the best approach is to test each condition (you get a TRUE or FALSE result) and then multiply it by the factor you are testing for and multiply it by the hourly rate. do this for the conditions and add them up. the formula is something like this: 8 x StdRate + [is total hrs more than 8] x (StdRate x 1.2) + [is total hrs more than 9] x (StdRate x 1.4) + [is total hrs more than 10] x (StdRate x 1.6) + [is total hrs more than 11] x (#Hrs - 11) x (StdRate x 2.2) + ........etc. you are not going to be using IF statements for checking the hours you just use a logical statement that result in TRUE or FALSE like (#Hrs > 9). the formula can get bigger with all the conditions you have but a good practice is to use Alt+Enter to add new line in the formula bar and it the formula would still work. this is good to having an easy-to-read formula and limit errors.
Good teaching video, you are a very helpful
Thank you 🙏
Many thanks, I have succeed on this. From Mozambique
Hooray! 🎉 Good for you! Good job 😊
Thank you. Excellent video.
You're welcome! glad you found it useful :)
My head hurts but it works! Mahalo nui loa!!!
'A' ole pilikia 😉 glad you made it work
Great, fantastic... Thank you very much.
You're most welcome 🙏
do you have a tutorial how to put computation based on that format? ty
Can you elaborate? I am sure I am following.
hello! when I tried to use the link to try to create a copy and follow along, the template was not empty. how do I fix this?
Very use full ur video but if i am enter one monh time sheet completely then i will go next month my previous month record will not come as weel ass i will record when previous month details also .any idea?
Yes, because the template is designed to handle one month at a time, so each month will have its own file. if you want to keep a history in the same sheet for all the months, design changes need to be made, first data entry will be in a separate table and the timesheet report will read from the data entry table and display it accordingly. Actually, when done this way you can also include employee names as well. However, it is not a simple project, but I agree it is very useful, thanks for sharing your thoughts. I have done something simiar on the channel, check the VertiCAL strategic calendar video, there should be a link to download the file.
How to download this sheet
File > Save As > Download a Copy
Hi, Your download link is not working.
Thanks Amit for pointing this out, link is fixed now
فيديو ممتاز, شكرا لك
العفو، سعيد انه أعجبك 👍🏻
hello! when I click on the link the template it's not empty, how do I fix this?
I see, open the template in Excel use Ctrl+G to open the go to window, go to special and select formulas, this should select all cells with formulas, once selected hit the Delete key. also open conditional formatting and click manage rules and delete all conditional formatting.