Formula to Calculate Hours 40+ and Move to Another Column

**Franchesca** · 03-04-2020, 01:41 PM

Please see the attached timesheet template. I am SO close to getting this right!

The current template has hidden columns with formulas to calculate and round hours, but now I am stuck on the totals. Anything over 8 hours a day or 40 hours a week is considered overtime.

I have everything over 8 hours a day going into overtime, but I can't quite to the formula for the 40+ hours.

Once the hours hit 40 hours in a week, I need it to move ALL the remaining hours that week into the overtime column. I feel like this is simple and I've let it get inside my head. Please help!

**tim201110** · 03-04-2020, 02:38 PM

for rows 14:20, col Q
=IF(SUM($P$14:P14)<$W$10,MIN(8,P14),$W$10-SUM($Q$13:Q13))
and down
w10=40

**Franchesca** · 03-04-2020, 03:28 PM

Hi tim201110, thanks for your help!

I can't get this formula to work for me. It is zeroing out my "regular hours" total, without effecting my "OT Hours" column at all. It may be user error

, but I am still stuck.

**tim201110** · 03-04-2020, 03:41 PM

uploaded a wb in post #2

**Franchesca** · 03-04-2020, 04:15 PM

Got it! Thanks! I plugged in the formula and it's almost working. Midway through the second week, it puts 16 hours in the regular column instead of moving the excess 8 into the OT column. I've attached the file so you can see what I am trying to explain.

**tim201110** · 03-04-2020, 04:29 PM

=if(sum($p$26:p26)<$w$10,min(8,p26),min(8,$w$10-sum($q$25:q25)))

**Franchesca** · 03-04-2020, 05:06 PM

I used this formula, but now it is populating hours in days without time entered. Am I doing something wrong? Updated template is attached.

**tim201110** · 03-04-2020, 05:10 PM

=if(p26=0,,if(sum($p$26:p26)<$w$10,min(8,p26),min(8,$w$10-sum($q$25:q25))))

**Franchesca** · 03-04-2020, 06:04 PM

The last modification looks like it's working! YOU ARE MY HERO!! Thank you SO MUCH for your help with this!

Formula to Calculate Hours 40+ and Move to Another Column

LinkBack

Thread Tools

Rate This Thread

Display

Formula to Calculate Hours 40+ and Move to Another Column

Re: Formula to Calculate Hours 40+ and Move to Another Column

Re: Formula to Calculate Hours 40+ and Move to Another Column

Re: Formula to Calculate Hours 40+ and Move to Another Column

Re: Formula to Calculate Hours 40+ and Move to Another Column

Re: Formula to Calculate Hours 40+ and Move to Another Column

Re: Formula to Calculate Hours 40+ and Move to Another Column

Re: Formula to Calculate Hours 40+ and Move to Another Column

Re: Formula to Calculate Hours 40+ and Move to Another Column

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula to calculate hours worked w/lunch or w/o + OT column only total after 40 hours

[SOLVED] Calculate Total Hours/Minutes between Min and Max Date in a column

formula to calculate total hours excluding certain hours

Formula to calculate regular hours and overtime hours

[SOLVED] Formula to Calculate Normal Hours, Time & 1/2 & Double Time from Daily Hours per week

[SOLVED] Calculate hours with predefined Working Hours and Weekend Hours

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions