Setting up formula for after 40 hours count every additional hour as Overtime.

[tommygoesha]

So I've attached my timecard excel and I have it so that after 8 hours its counted as overtime. But if they do over 40 hours it doesnt count the additional hours as overtime. Is there a way to do this? Also is there anyway to set up the date so that it automatically changes per month?Timecard 2016.xlsx

[MarvinP]

Hi tommy,

I like Tesla so here is my advice. You've created your timesheet that keeps Excel from working at it's optimum . Your data looks to you like tables but in Excel it is NOT. You can't have blank cells or rows or columns in a "Table". Excel needs TABLES to do it's magic.

Read these sites:
http://contextures.com/xlExcelTable01.html
http://www.techrepublic.com/blog/10-...-table-object/

In your "very pretty" buy not very efficient Timecard 2016 example you have lots of problems.
1. On your Late sheet you need to make the time on the same row as the name. This would remove blanks between names and make the data a real table.
2. On your month sheets, don't use Outlining as it will hurt instead of help your calculations.
3. Throw out what you have and read the links above.
4. Look into the topic of Pivot Tables to do all the work your sheet do.

Keep this conversation going and we can help you do a MUCH BETTER timesheet than what you propose.

[tommygoesha]

Hi tommy,

I like Tesla so here is my advice. You've created your timesheet that keeps Excel from working at it's optimum . Your data looks to you like tables but in Excel it is NOT. You can't have blank cells or rows or columns in a "Table". Excel needs TABLES to do it's magic.

Read these sites:
http://contextures.com/xlExcelTable01.html
http://www.techrepublic.com/blog/10-...-table-object/

In your "very pretty" buy not very efficient Timecard 2016 example you have lots of problems.
1. On your Late sheet you need to make the time on the same row as the name. This would remove blanks between names and make the data a real table.
2. On your month sheets, don't use Outlining as it will hurt instead of help your calculations.
3. Throw out what you have and read the links above.
4. Look into the topic of Pivot Tables to do all the work your sheet do.

Keep this conversation going and we can help you do a MUCH BETTER timesheet than what you propose.

Hey MarviP,

Yeah unfortunately I don't work for Tesla, This is a demo sheet a friend sent me who worked there awhile back, I had never used Excel prior to this- 0 experience with it, haha however I've worked on the formulas for this so I know a little about formulas and thats about it. So one I had to make something up within 1-2 days of my job transfer to a small assistant job for a small group at a Future Electronics and two tables were far out of my area of expertise. But I'll definitely give it a look over! Are there any suggestions you specifically want to point out that may be of any help? I appreciate the quick reply!

- Tom

[scottiex]

Not sure exactly how you want to handle overtime
but try this in cell M20 and fill down

This counts any hours over 8 if you have done over 8 for that day and counts extra hours after 40 if you have done more than 40 for that week.

Please Login or Register  to view this content.

Should it always count Sundays as overtime? if so you can just make the Sunday cell equal the hours worked.

which date are you talking about changing?

[tommygoesha]

Well saturdays are usually overtime days. so if they do 40 hours mon-fri then sunday if we work then it calculates their overtime if they've done over 40.

[scottiex]

BTW I'm 100% in agreement with Marvin, of course.

Probably some boss insisted that a sheet looked pretty, and the excel guy jumped through lots of hoops to do exactly what was asked as opposed to a pretty but also efficient format.

[MarvinP]

Hey Tom,

I'd suggest you look at a few easy Pivot Table tutorials. After seeing how easy they are you will realize why your data is structured wrong. You will also see how easy it is to do filters for months, weeks, employees and other stuff.

Look at http://www.excel-easy.com/data-analy...ot-tables.html for a start and then look at some of the examples at:
http://www.dummies.com/how-to/conten...-and-pivo.html which shows Slicers and other tools build onto 2010 Pivots.

After learning a bit more Pivots design your timecard data collection in a real table and see if you can pivot some of it.

Hi Tom - I've created an example table of 1000 timecards. I use random formulas for doing this so I don't need to do manual entry. Use this to practice on to see why Tables and Pivots do lots of work without needing a formula. You might need to Copy over the formulas in columns A to C with "values only" or learn to Refresh All in the Pivot table in your learning.

[tommygoesha]

So this works well for a temporary solution. I have one person done, is there anyway to copy\paste others so I dont have to keep changing the formula?

[tommygoesha]

Hey Tom,

I'd suggest you look at a few easy Pivot Table tutorials. After seeing how easy they are you will realize why your data is structured wrong. You will also see how easy it is to do filters for months, weeks, employees and other stuff.

Look at http://www.excel-easy.com/data-analy...ot-tables.html for a start and then look at some of the examples at:
http://www.dummies.com/how-to/conten...-and-pivo.html which shows Slicers and other tools build onto 2010 Pivots.

After learning a bit more Pivots design your timecard data collection in a real table and see if you can pivot some of it.

Hi Tom - I've created an example table of 1000 timecards. I use random formulas for doing this so I don't need to do manual entry. Use this to practice on to see why Tables and Pivots do lots of work without needing a formula. You might need to Copy over the formulas in columns A to C with "values only" or learn to Refresh All in the Pivot table in your learning.

Wow that looks amazing. Yeah in my spare time I'll look into how to do this . Right now I work with Electricians and so I have to find out the sum of hours for each journeyman and how much their overtime was + Sum of hours for each apprentice and their overtime and etc. That way say there are 5 Journeymen and 5 Apprentice, then itll show me how much their group overtime and group normal hours are. Would this be difficult to create? Also is there a way to add a quick button Entry button? Say 2/5/2016 everyone is there and everyone does 9 hours. Instead of manually doing each of them I can enter 1 thing and give it to them all?

[MarvinP]

Hi Tom,

See the attached that has some extra features where I've added a pay scale and team to the table and lookup for each name. See if this is closer to what you need. If this makes more sense for you and you are starting to see the benefits of tables, adding a method to group the inputs could be added using some VBA. First you need to buy into and make the table work for you. See the attached.