Solved I'm trying to account for overtime hours earned?

January 24, 2016 at 08:55:33
Specs: Windows 7
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!

See More: Im trying to account for overtime hours earned?

Report •

✔ Best Answer
January 24, 2016 at 18:12:18
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.5

Is 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

http://www.skeptic.com/

message edited by mmcconaghy



#1
January 24, 2016 at 09:59:03
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

http://www.skeptic.com/


Report •

#2
January 24, 2016 at 16:25:54
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!


Report •

#3
January 24, 2016 at 17:04:29
I can provide a copy of the spreadsheet if that would be helpful

Read 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

http://www.skeptic.com/


Report •

Related Solutions

#4
January 24, 2016 at 17:22:19
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.50

I 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.


Report •

#5
January 24, 2016 at 18:12:18
✔ 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.5

Is 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

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#6
January 25, 2016 at 14:20:18
Perfet, that works great! thank you very much

Report •

Ask Question