I am building a timesheet in Excel and a daily total for a staff member should be 7 hours. Any hours >7 needs to be moved to a new cell. I'm currently using this formula: =IF(SUM(H8)>7,SUM(H8)-7,""), and it does work. However, I'd like to have the regular hours worked (which is less than or equal to 7) appear in H8 and any hours >7 appear in the overtime column. The formula that I am currently using places hours >7 in the overtime column but they are also appearing in regular time column. Basically, I'm trying to account for regular hours worked and overtime hours earned. Does anyone have any ideas for this? Thanks in advance!

✔ Best Answer

So your data looks like this, I have included the Column Letters and Row numbers that you seem to have forgotten about. A B C D E F G H I 1) Date: Time In: Time Out: AM Hours Time In: Time Out: PM Hours RegularTotal HRS Over 2) 01/11/2016 08:30 AM 12:00 PM 3.5 1:00 PM 5:00 PM 4 7.5 0.5Is there a specific reason you have separated out the AM Hours and the PM Hours?

You can shorten your sheet by two columns if not.There is no reason to use the SUM() function on a single cell in this formula:

=IF(SUM(H8)>7,SUM(H8)-7,"")

For the formula in cell H2 try this:=IF(G2+D2>7,7,G2+D2)

For the formula in cell I2 try this:

=IF(G2+D2>7,(G2+D2)-7,0)

MIKE

message edited by mmcconaghy

Without knowing how your worksheet looks, the best I can offer is to look here: http://www.cpearson.com/excel/overt...

Should answer any questions you have about Timesheets.

MIKE

Thank you for the suggestion. I can provide a copy of the spreadsheet if that would be helpful and you'd be willing to look at it. Basically, I have totaled the number hours worked on a given day (7.50 for example) but, if there are hours >7, in this case .50, these hours appear in the overtime column. But, in the total hours column, I would like to have just the 7 hours "regular time" here and not the 7.50.

Any ideas are appreciated and I can provide the sheet if you wish. Thank you!

I can provide a copy of the spreadsheet if that would be helpfulRead this HOW-TO which explains how to use the < PRE > tags to align your data, when posting here in the forum:

http://www.computing.net/howtos/sho...

Post a small sample, including Column Letters and Row Numbers, along with any relevant formulas your currently using. The more info the better.

MIKE

Here is an example of the spreadsheet. Date: Time In: Time Out: AM Hours Time In: Time Out: PM Hours "Regular Total" HRS Over 1/11/2016 8:30 AM 12:00 PM 3.50 1:00 PM 5:00 PM 4.00 7.50 0.50I hope it makes sense. As you can see, the hours >7 appear just fine but, in the regular hours, 7.50 still appears and I would like to have only regular hours =<7 appear here. Thank you in advance.

So your data looks like this, I have included the Column Letters and Row numbers that you seem to have forgotten about. A B C D E F G H I 1) Date: Time In: Time Out: AM Hours Time In: Time Out: PM Hours RegularTotal HRS Over 2) 01/11/2016 08:30 AM 12:00 PM 3.5 1:00 PM 5:00 PM 4 7.5 0.5Is there a specific reason you have separated out the AM Hours and the PM Hours?

You can shorten your sheet by two columns if not.There is no reason to use the SUM() function on a single cell in this formula:

=IF(SUM(H8)>7,SUM(H8)-7,"")

For the formula in cell H2 try this:=IF(G2+D2>7,7,G2+D2)

For the formula in cell I2 try this:

=IF(G2+D2>7,(G2+D2)-7,0)

MIKE

message edited by mmcconaghy

Perfet, that works great! thank you very much

Ask Your Question

Weekly Poll

Are you able to work from home during the pandemic?

Discuss in The Lounge

Poll History