Forum Discussion
I need a (complicated?) formula
I have created an excel databased from which I have a pivot table to summarize employee time data. This pivot table is based on dates within a two week period of time. It then rounds the time. I take that data and have it copied into another sheet (via formulae) so that I can create a second pivot table that futher summarizes and caclulates overtime, time off, holidays...etc. It was working beautifally with just one week. (I have hard copies of all that data from which I am currently posting all of this and trying to elimnate all of the paper and work involved...if I get this working properly it will take processing from 2.5-3 hours to about 10 minutes.)
When I add the second week...my regular time isn't taking into account OT paid on a weekly basis. For example: an employee works 43 hours week 1 and 38 hours week 2. Regular time for the two week period should be 78 hours and 3 hours overtime. Instead, I am getting 80 hours regular and 1 hour overtime.
My formula currently reads: =IF('Residual Hours'>(80-PTO),80-PTO,'Residual Hours') where Residual Hours is a calculated field taking total hours and subtracting time off, holidays, etc.
I know this is because it is not differentiating the two different weeks. So...how do I write a formula to tell excel to look at the dates, determine that they are within a certain range and do the above caclulation based on 40 hours per week? I would also love to be able to not have to change the range criteria each pay period if possible.
As an option, I could add a column to the database itself to indicate week 1 or week 2, then I could add that as a column to the first pivot table and the calculated fields would be relatively easy to manipulate. However, I am still in need of a range function formula. They always give me problems :(
Any help will be most appreciated!
Here is an example of using vhlookup to grab a period you could use in your pivot table to group by
Good Morning.
As for Resizing your range. Look into converting your range to a table. I use 2016 and it has excel inherently just knows your modifying your table and youe can easily resize it off of the 'design' Ribbon.
As for identifying dates. Create a column coilumns to the right of your data and create a formula in that column that calssify's a date as in falling inot a payperiod. Then in the pivottable add a sum by period.
I can attach an example if you would like.
Good luck ..
Joe
