Formula for adding pto by hour - really new to this, please have patience

[punkindews]

I have a monthly calendar where I have counted the working weekdays with conditional formatting. I also have the total of hours that have been taken as sick time or vacation time. I would like to take those 2 cells and figure out what the hourly rate (.0962) accrual would be based on 8 hour days for a running total to current day.

I thank you in advance for any help you can offer!

[AliGW]

Welcome to the forums! Please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[punkindews]

I have been trying to attach my file but nothing shows up in the attachment drop down. The file shows that is uploaded. What am I doing wrong?

[punkindews]

LOL Nevermind, still new to this.

[JeteMc]

Try pasting the following formula in AH5 and then copy AH5 to AH11:

Please Login or Register  to view this content.

Let me know if you have any questions.

[punkindews]

I would never have been able to come up with that. I tried to deconstruct it and only some made sense.
So, that being said, I re-read the comment for the total and I meant to say that I didn't want the -8 days counted. I removed that from the formula and I don't think that the total is correct.

Thanks again for the help!

[JeteMc]

Paste the following formula in AH5, then copy AH5 to AH11:

Please Login or Register  to view this content.

Note: the modified formula gives the exact amount expected for Feb, however it is not in agreement with the stated expected value for Jan. Watching the Evaluate Formula run for AH5 it seems to do what you want, counts 21 working days in the month then subtracts the 3 days for which a value of -8 is listed in row 5 meaning that it multiplies 18 days x 8 hours x .0962 accrual, yielding a total of 13.8528 leave hours earned for the month.
Let me know if you have any questions.

[JeteMc]

It occurred to me that the number of workdays in Jan excludes the 1st as it is a holiday. The following formula takes that into account and agrees with the expected results for both months:

Please Login or Register  to view this content.

Let me know if you have any questions.

[punkindews]

Perfect!!! Thank you so much!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.