• 515K Members
• 5,697 Online
• 611K Conversations

New Contributor

Need help with simple timesheet formula

Sorry, my skills with excel are limited, but I'm sure this will be a simple formula for most. Here's what I have and what I'm trying to achieve.

Cells  A-E all have a value of "10", representing the total number of hours worked for each day.

Cell F shows that total of hours as "50" ... I'm using =sum(A1,B1,C1,D1,E1)

Cell G shows the total of OT hours as "10" ... =if(F1>40,F1-40,"")

I want cell F to show 40 and still have cell G to show 10.

3 Replies

Re: Need help with simple timesheet formula

In cell F =IF(SUM(A1:E1)>40,40,SUM(A1:E1)) and in cell G =SUM(A1:E1)-F1 i guess this would solve your issue , if not please explain more in order for me to understand :)
Highlighted

Re: Need help with simple timesheet formula

F1=MIN(SUM(A1:E1),40)
G1=SUM(A1:E1)-F1

Re: Need help with simple timesheet formula

Both suggestions work. Thanks so very much. I'm still tweaking my sheet to best suit my needs, but now at least my numbers match.
Related Conversations
IF FUNCTION ISN'T WORKING NO MATTER HOW SIMPLE THE COMMAND IS
thomasea in Excel on
6 Replies
Need help with concatenate formula
Dave Cunningham in Excel on
2 Replies
formula for data calculation
aayushman_mishra in Excel on
10 Replies
Creating A Sublist
zjohnson in Excel on
5 Replies