Home |
Search |
Today's Posts |
#1
|
|||
|
|||
calculate hours just can't figure it out
my excell spreadsheet looks like this
colum A 20 peoples names colum B-H days of week colum I will calculate total hours worked for employees with a grand total at the bottom of I need to calculate hours worked totals without 30 min lunch breaks if hours worked is over 7.5 hours so cell B2 would say 9-5:30 need cell B3 to say 8 (as in 8 hours worked with 30 min break) but part of the problem is that a person can work 7 hours without a lunch and get paid so we cant take the 30 min break out unless empleyee is over 7.5 hours the real answer I need in the end is total hours without breaks for full time and part time people just cant figure out the formula that eliminates that half hour and a formula that reads 2 times in 1 cell (9-5:30) if I have to break that part into 2 cells ....so be it... IF ANYONE CAN HELP that would be great ....been trying to work on it for 3 days |
#2
|
|||
|
|||
Hi!
While it is possible to calculate if you have a cell entry like: 9-5:30, it is really complicated and not recommended. You would be better served by splitting the times into 2 cells: Time In, Time Out. Here's one way to calculate total time and account for a 30 min break if hours are over 7.5: B1 - Time In = 9:00 AM C1 - Time Out = 5:30 PM =IF(C1-B17.5/24,C1-B1-30/1440,C1-B1) How do you want these results to be displayed? As a decimal - 8 As a time - 8:00 If you want a decimal value result, use this formula: =IF(C1-B17.5/24,C1-B1-30/1440,C1-B1)*24 Format the cell as GENERAL. If you want a time: 8:00, just use the first formula and format the cell as h:mm. One other thing to consider is, will times worked span past midnight? Will someone work from, say, 11:00 PM to 7:00 AM? Biff -----Original Message----- my excell spreadsheet looks like this colum A 20 peoples names colum B-H days of week colum I will calculate total hours worked for employees with a grand total at the bottom of I need to calculate hours worked totals without 30 min lunch breaks if hours worked is over 7.5 hours so cell B2 would say 9-5:30 need cell B3 to say 8 (as in 8 hours worked with 30 min break) but part of the problem is that a person can work 7 hours without a lunch and get paid so we cant take the 30 min break out unless empleyee is over 7.5 hours the real answer I need in the end is total hours without breaks for full time and part time people just cant figure out the formula that eliminates that half hour and a formula that reads 2 times in 1 cell (9-5:30) if I have to break that part into 2 cells ....so be it... IF ANYONE CAN HELP that would be great ....been trying to work on it for 3 days . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculate hours on work sheet | Excel Worksheet Functions | |||
calculate difference in time to hours | Excel Worksheet Functions | |||
calculate average hours and minutes | Excel Worksheet Functions | |||
calculate average hours worked | Excel Worksheet Functions | |||
How do I calculate the hours spent on a project by entering a sta. | Excel Worksheet Functions |