Use the vacation hours if available but only what is available. IF then stmnt?

You could use something like this instead in E4:

=MIN(MAX(0,40-C4),I4)

Not 100% sure what you're trying to do with the rest, e.g. what is meant to be in the "holiday hours" column?

Nick,
I am attaching a different spreadsheet here with multiple sheets. These are the actual sheets that I use (minus some personal info) to get my payroll numbers.

I would like to have the sheet calculate the overtime (40 hours is regular time anything over 40 is OT- and I have managed to calculate that). I would also like the sheet to calculate the vacation/sick hours needed to get an employee to 40 hours WITH or WITHOUT holiday pay, but only up to the amount they have available. I am not sure how to make the sheet denote between the 2 and if it is something that I can add into the 'Time Card Worksheet' to tell it to pull from one another, I am down with that too...like enter the time in the rows but add "s" to the end to denote using sick time or something. I may be asking too much for the sick time usage also and manually entering it is cool too.

Examples:
Adam has 32 hours but is not getting paid for the holiday, if he wants to use his vacation time then it needs to be added in the column, but he only has .03 hours to use. Can it show .03?
Also in the same sheet, Alex has 35.43 regular hours, but is getting 8 hours of holiday pay, so there is no need to use vacation/sick hours to make 40 as it will be over. Same with Randall as he has 32 hours on the books as regular time and will be getting 8 hours of holiday pay.

I can manually do some of this, but if I didn't have to remember to subtract the "extra" hours from vacation/sick that would be great.

I'm a bit unclear on the process...

You take info from the Time Card worksheet to get number of hours worked (is this manually typed in?). This is regular hours (up to a max of 40).

Are sick hours input manually? If not, where are they recorded?

Same for holiday hours? (is this paid holiday, and vacation is unpaid holiday?)

So you want to automatically calculate overtime and vacation hours. Overtime is the remainder after you add hours worked, sick hours and holiday hours, and subtract 40 - is that right? If so then back to earlier question of how sick/holiday are derived?

Vacation hours is just used to make up the shortfall if the total of regular, sick and holiday is less than 40, is that right? What happens (or what do you want the sheet to show) if there are no vacation hours left?

Hi

Try this in E4 and down....
=(IF(40-C4>=I4,I4,40-C4))

I manually input the information into the time card worksheet, yes.

Sick hours are currently being input manually as I have not figured out how to include them automatically if the vacation hours have been used. Both the sick and the vacation hours/time are recorded on the 2022 Tracking sheet, by employee. Sick hours may be used at the discretion of the employee and may therefore will be entered manually. Long story short is that if an employee doesn't work a full 40 hours per week then vacation/sick time will be used to make the 40 each pay week until the hours are gone.

Holiday hours (paid holidays) will be entered manually as some employees will get them and some will not, depending on their work schedule, and it is not tracked anywhere.

Yes, I would like to have the sheet automatically calculate the OT and Vacation with hours worked, vacation, sick and holiday hours included. Yes, vacation AND sick will be used that way. If there are no hours left then it would just be 0 since there is nothing to use and the employee is not getting paid for hours they do not "have" from either working or PTO.

A potential problem is, are these things mutually exclusive? e.g. if someone is sick monday morning and only works 4 hours (so 4 hours sick) but then works 9 hours on the other 4 days, does this go down as 40 regular hours and no sick hours or 36 regular hours, 4 sick hours and 4 hours overtime? Or is it up to the employee?

If you want to keep the current format then something like this might work:

overtime (d4) - hours worked plus vacation, sick, holiday, minus 40 (but not less than 0)

=MAX(0,'Time Card worksheet'!G26+E4+F4+G4-40)

vacation (e4) - 40 minus regular hours, sick and holiday, not less than 0, not greater than vacation left

=MIN(MAX(0,40-C4-F4-G4),I4)

or if vacation will always be used before sick then:

=MIN(MAX(0,40-C4-G4),I4)

If vacation always used before sick then you could use this for sick in F4:

=MIN(MAX(0,40-C4-E4-G4),J4)

Holiday to be input manually.

Alternatively you could re-organise your whole file to allow a more easily interrogated input but I don't have time to do that now.