Microsoft Excel 02 Payroll Part 2 If formulas and functions

Excel Lesson #2 Use Excel to create a payroll table for a business. See studycoding.org for all tutorials by Shad Sluiter.

[@turtleshell0]

not to sound weird or anything but the powerpoint template, the loud music and the little introduction was honestly so cute its such a dad thing to put together

[@ashleybermudez1531]

These are some of the most helpful, well detailed, well articulated videos. Thank you!

[@Detroitmamma]

Thank you very much for posting this you are amazing my life is soooo simple now that I have dst down to create this spreadsheet! Many thumbs up from Trace in Detroit!!

[@ayeshafarooq7740]

Most Amazing set of Excel videos I’ve come across. Excellent teacher!

[@shadsluiter]

Glad you think so!

[@daviduribe5426]

Sir thank you for all you do. Simple and straightforward just how it should be.

[@ql2396]

Good job, you made it so easy and clear. thank you.

[@shashikamal1198]

Thank you, Found what i was searching for.

[@melindarash5197]

Yes overtime is 1.5 x the hourly rate, except he already calculated all of the hours into the pay. So he already paid for the 41 hours, now all he is doing is paying the difference for the overtime, which would be half the hourly rate. If he paid you for 41 hours, and then gave you an addition hour at time and a half you would get 42.5 hours of pay vs the 41.5 hours of pay that you should get

[@netasekllc]

Ohhhh thankyou. That makes perfect sense. I see now.

[@hellobeautiful3301]

Thank you for the excellent tutorial!

[@FantastyckplastycK]

amazing job!

[@misspeaches358]

Your soothing voice helps me lol

[@richardhidalgo3920]

Excellent VDO, thank you so much

[@streetfleet]

Thanks for sharing.

[@sweetsaros6680]

Great video..very helpfull for me 👍👍

[@markallan23]

excellent!

[@Maaha11990]

very easy n good explanation...thnks...n keep it up:)

[@Shaukatali-vd3ov]

[e-mail]

[@jkb1904]

Thank you very much

[@olueisaac8413]

Learnt alot

[@meckh7581]

thank you so much ...

[@eleodoralexandrustanescu5240]

great!

[@CoralYao]

Thank you very much for the post.

[@shadsluiter]

You're welcome

[@kemsler]

When trying to make the OT formula =if(D4>40,D4-40,0) I get an error. Did they change how IF formulas work in the newest version of excel? TIA

[@vandeshawhyte2936]

Can you do a tutorial on how to calculate time worked? Clocked in / clocked out

[@dijasrecipes8410]

Please how did u get the .5 for the OT calculation

[@BMFC82]

Can you put more than 1 formula into a column? like if you had double time and triple time for OT... or do you have to create a new column for each?

[@shadsluiter]

Each column can only have one formula. Add an extra column for 2x. Add another for 3x etc.

[@bramax8068]

i need clarity on calculating overtime this formular =IF(D5>40,D5-40,0) shows me an error please help

[@danielzhelyazkov4856]

Hi i tryed the IF formula but still strugle to get it wright Can you help me to create a formula Start - Finish - total hours - break devided - only over 8hrs of work per day so 02.00 to 10.00 = 8.00 - 0.45hrs Normal colum will give me 7.15 hrs worked but i have guarantged 8hrs of work so i want to start devide break only if more than 8hrs

[@bpietsch8392]

Very easy to follow, for every overtime hour you work you are already getting paid 1x your hourly rate because the 1st pay column is hourly rate * hours worked , therefore the only part missing is the 0.5x. (1.5 - 1 = 0.5) This is because of the method he has used to calculate total pay.

[@hawardsiwakwi3846]

I really enjoy your presentation keep up with good work

[@kathyreese2892]

I repeat, how do you get the workbook to practice?

[@sarahejenobo7806]

how did you get the .5 for the bonus time. cant it just be the pay divided by 40?

[@shadsluiter]

The first 40 hours is paid at the regular rate. Any overtime is given an extra 50% pay.

[@oanhluong7473]

I enjoy your lesson on how to calculate payroll. I would greatly appreciate it if you could please show us how to create an estimation excel spreadsheet sheet for minimum wage and increase and sick leave for our student employees. Because I am not so sure how to do it. I really need help with this particular subject. Thank you for your kindness of helping me.

[@shadsluiter]

Great suggestion! If you can first put your calculation process into a math problem, then creating the Excel formula is easy.

[@oanhluong7473]

I am not so sure how to make the formula if work for this particular problem. Can you please shoe me how professor Shad? Thanks

[@kathyreese2892]

How do you get the assignment book?

[@Manell90210]

hey i didnt understand the last formulla to calculate overtime pls explain it im bad at math

[@shadsluiter]

If you get $10 per hour and work 50 hours the pay is calculated like this. Base rate is $10 x 50 = $500. However the overtime bonus is called "time and a half". The 10 hours of overtime should be paid at $15 per hour. The regular formula only paid you $10 for all of the hours. The 0.5 is the OT bonus. $10 * 0.5 = $5 extra amount paid per hour for the OT period. $5 OT bonus x 10 OT hours = $50 OT bonus. Total pay is $550.

[@richderibe4656]

waw god tiche

[@BMFC82]

When calculating the OT, wouldn't it be 1.5*? that would equal time and a half but your demonstration was only .5* So the first guy should have got an extra 23.85 for that 1 hour of OT. Right?

[@shadsluiter]

The 0.5 calculation is a premium for the first hour ot. The employee was already paid the usual hourly wage in the calculation. 0.5 is a bonus value.

[@Thekittentamer811]

That OT needed to be at time and a half 1.5 not .5 ... if you make $10 an hr and worked 41 hours you make $400 (40 hrs) plus $15 (1.5 x $10) total $415

[@shadsluiter]

You are correct. However the formula in the sheet pays you $10 per hour for all of the hours worked ($10 x 41 = $410). The OT bonus formula gives you an extra $5 bonus for each OT hour in the paycheck. $410 (regular pay) + $5 OT bonus for one of the hours worked = $415. You could design the formula in different ways to arrive at the correct answer.

[@osamawahbeh]

Overtime should be 1.5(One and a half) of hourly rate not 0.5(half)

[@shadsluiter]

You can use 0.5 if you multiply it by the original and then add it to the original number. You can use 1.5 if you only multiply it by the original number.

[@gasser5001]

im just wondering how no one noticed that the OT formula is incorrect...its supposed to be "1.5" not "0.5".

[@shadsluiter]

that depends on what you do with the calculated result. Add it to the hourly wage and you get a 1.5 result.

[@mawaisonline]

thanks you asked when we calculate it already with the 41 hours then its already included with the overtime hour. so 0.5 formula should be added to make it 1.5

[@kathyreese2892]

What is the "return button, please! Around min. 3:29 - 3:37

[@shadsluiter]

Enter key. = Return key (Mac)

[@kathyreese2892]

I have a Lenovo. Hitting the enter key doesn't seem to work. Thanks for your previous prompt reply.

[@kathyreese2892]

When I hit enter it just moves down, rather than" entering " a function.

[@miniajayan6585]

Please in malayalam

[@roxanay6180]

😀

[@savikapoon559]

Y is this formular not working on my computer

[@Manell90210]

maybe the language is different

[@bramax8068]

=IF(D5>40,D5-40,0) same applies to me

[@banklog7495]

People came here with the aim of chasing money more than knowledge and that will damage your progress, trust me. Chase knowledge first and I promise! The money will follow you just like it’s following some us now

[@lynj5153]

Bloody hell, if I read another "the OT time should be 1.5, not .5..." he's already explained it so many damn times in ther comments!!!!

[@MegaHarvickFan29]

You actually calculated overtime incorrectly. Overtime or 'time & a half', however you choose to call it, is actually calculated (the formula to use) as 1.5*C4*E4 The formula you used only paid them $7.95 for that 41st hour worked. Why would someone work overtime if you were only paying them 50% their base pay? The actual overtime pay would be $23.85 for the 41st hour they worked & total pay for that pay period would be $675.75

[@xirruz]

$651.90 included the $15.90 for the 41st Hour, he was just adding the $7.95 overtime premium for the 41st hour.

[@nurlindafsihotang49]

+John missing the point. overtime should be paid 1.5 a ka hourly payment+0.5, because it is OVERTIME, outside his supposedly working hours. she is correct, according to the international labor organization treaty, overtime paymet are 1.5*hourly ordinary wage

[@xirruz]

Wait what? That's what the video did. Although a different approach. Hourly wage - 15.9 x 40 hours = 636. 15.9 x 41 hours = 651.9, so the only thing missing was the +0.5. If you make it 1.5 then you're basically giving the employee three times his hourly wage for overtime. The total pay is right, he did make a mistake stating that $651.9 was for 40 hours at the video so I can understand the confusion. The calculation checks out though. Edit: The calculation is as follows: $15.9 x 40 hours = $636 $15.9 x 1 hour Overtime = $23.85 Total pay: $636+$23.85 = 659.85

[@swathih1]

I was thinking the same. Although his excel video is impeccable, the overtime according to US wage and hour division is 1.5 times the regular rate of pay for hours overworked.

[@pharawer6364]

Lol, the government doesn't care how you get to your numbers in the excel sheet as long as they are correct. And the video was correct indeed. their paycheck will be correct too. There are several ways to this calculation. Use whichever you want as long as they are correct, but claiming that something is incorrect just because you don't understand it is inappropriate. He may have his reason to do the formula that way, who knows. You sometimes need to do things in odd ways so some advance formulas can work.

[@pendaflex6527]

His OT rate is INCORRECT. It should be regular hourly rate/wage x 1.5 ( $ 15.90 x 1.5 = $ 23.85) so employee's 1 hr OT = $23.85 NOT $7.95!!!

[@shadsluiter]

You could write the pay formula to use the 1.5 multiplier if you applied it only to the OT hours. The way the formula is written in the example is that all of the hours worked were already given the base pay. The OT hours deserve a bonus of 0.5 beyond what was already paid. There are multiple ways of grouping numbers in any calculation.

[@nuralam5188]

My insurance povided rong rood

[@IrisMG]

"Hours Worked", not "House Worked"

[@dijasrecipes8410]

Please how did u get the .5 for the OT calculation

[@shadsluiter]

1.5 is standard rate for OT pay aka "time and a half". 1.0 is hourly rate multiplier for standard pay.. 0.5 is the OT bonus added to the regular hourly rate. Add the two amounts together to get a 1.5 rate.

[@IrisMG]

"Hours Worked", not "House Worked"

[@mariamhayek4384]

all this valuable, detailed explanation and all you see is "house worked" big deal

[@Nobodysvlog]

@@mariamhayek4384 hahahhaha! Obviously that's just a super minor issue that no one shouldn't be making a big deal of. hahaha