harveya915
Board Regular
- Joined
- Sep 4, 2015
- Messages
- 141
So I have a spreadsheet to keep track of my time at work. It goes In - Out, In - Out, Total (time I clock-in in the morning then out at lunch then back in after lunch and out for the day).
My total time formula for the day is represented as =SUM(B1-A1)+(D1-C1). My cell format is [h]:mm and so on goes down the sheet for the rest of the pay period which is 2 weeks (total of 14 days).
My total time formula for the weeks is represented as =SUM(E1:E14). My cell format is [h]:mm (Cell E15)
Then I have my hourly wage cell format as "General" (Cell E16)
I have the format of cells set up with [h]:mm because I do not want a decimal returned as the value. I want the full time displayed. In the overall total hours it will give me the exact hours with the exact minutes, not a decimal.
I am using an "IF" function to figure out my total wages for the week (Cell E17), but for some reason I can't get it to work. This is what I have so far:
=IF(E15,<=80,(E15*E16*24),((E16*80)+((E15-80)*(E16*1.5)))
The "True" value breaks down: (E15)Total Hours * (E16)Wage * 24
The "False" Value breaks down: (E16)Wage * 80 hours "base pay" + (E15)Total Hours - 80 hours "calculates hours that qualify for overtime because obviously the false value would mean that Total Hours worked is greater than 80" * (E16)Wage * 1.5 "calculates overtime rate".
Any suggestions? Thanks!
My total time formula for the day is represented as =SUM(B1-A1)+(D1-C1). My cell format is [h]:mm and so on goes down the sheet for the rest of the pay period which is 2 weeks (total of 14 days).
My total time formula for the weeks is represented as =SUM(E1:E14). My cell format is [h]:mm (Cell E15)
Then I have my hourly wage cell format as "General" (Cell E16)
I have the format of cells set up with [h]:mm because I do not want a decimal returned as the value. I want the full time displayed. In the overall total hours it will give me the exact hours with the exact minutes, not a decimal.
I am using an "IF" function to figure out my total wages for the week (Cell E17), but for some reason I can't get it to work. This is what I have so far:
=IF(E15,<=80,(E15*E16*24),((E16*80)+((E15-80)*(E16*1.5)))
The "True" value breaks down: (E15)Total Hours * (E16)Wage * 24
The "False" Value breaks down: (E16)Wage * 80 hours "base pay" + (E15)Total Hours - 80 hours "calculates hours that qualify for overtime because obviously the false value would mean that Total Hours worked is greater than 80" * (E16)Wage * 1.5 "calculates overtime rate".
Any suggestions? Thanks!
Last edited: