Hi Andy
Can you give an example on how to calculate the contracted hours for one employee? What is the expected result?
The notional 36 hrs is for a week/month? What if an employee join/leave in the middle of a month? Will you use a full month to calculate?
Looking back at my previous solution, you can simplify column V of the Data sheet by using =IFERROR(Table14[@[STAFF NAME]],"") instead of the original formula. Sometimes I tend to make simple things complicated when I think too much 
Sunny
Hi Sunny, don't worry about calculating the contracted hours, this will be input in the data tab when the employee starts
I don't think it needs a complicated formula. If an employee has a leave date then sum these contracted hours together. I should then be able to add the average number of monthly (worked) hours calculation.
The calculation for staff turnover I'm trying to obtain is: Number of staff hours who have left / the average number of worked hours per month
Hi Andy
Hope I understood you correctly. This is what I am doing.
If there is a date left, checking to see how many months within the year did the staff work.
Multiply that number by 36 hours (since you don't have the actual figures, otherwise just add them)
Hope this is correct.
Sunny
Hi Sunny, less complicated that that. I have created a new tab in the attached (sheet 1) to make it clearer.
If there is a leave date, sum all these contracted hours across all the employees that have left (regardless of the leave date). No multiplying of 36 needed.
Sorry I'm making this confusing for you lol
Andy
Hi Andy
Maybe I am not too clear what you wanted. You want to sum all contracted hours of all employees that have left.
Try this on sheet1 =SUMIFS(tbl_4[Contracted hours],tbl_4
,">0").
You should get 252. If this is not correct, then let me know what is the expected result from sheet1.
Sunny
Thanks Sunny! Exactly what I was after.
Is it possible to just calculate this for the last six months (on an ongoing basis) based on the leave dates of employees?
Hi Andy
Without knowing your cut-off date for the 6 months, you can try any of the formulas below. Change the date if necessary.
=SUMIFS(tbl_4[Contracted hours],tbl_4
,">="&EDATE(TODAY(),-6))
=SUMIFS(tbl_4[Contracted hours],tbl_4
,">="&"01/07/2017")
Hope this helps.
Sunny
Hi Sunny, you did some work for me previously and I need some help on a wages sheet I'm currently developing. It's based on the previous formula's you did. There's a couple of comment boxes in the tabs in the attached. Are you able to develop the formulae for me?
Thanks
Andy
Hi Andy
I don't quite understand your requirement.
Are you saying if you have already used a rate (say column E of the Employee database) for sheet 08.01.18 - 04.02.18 and if the rate changes later (let say $10), you want the original rate (in this case $9) to remain in sheet 08.01.18 - 04.02.18?
Sheet 15.02.18-04.03.18 will then use the new rate of $10?
Maybe you can give more examples with values so that it will be clearer.
Sunny
Hi Sunny, yes, precisely this. Reason being, if the wage rate increases during the year, I don't want the previous 4 week sheets to reflect that new rate also. I want them to stay at the 'old rate'. The reason for this is because I want to accurately calculate the total wages for each given 4 week period across the year (amongst other calculations). Hope that makes sense.
Andy
Hi Andy
That cannot be done as Excel will always recalculate whenever a cell value is changed.
The best I can think of is to convert your formulas into values or maybe you need to maintain a history of rates.
I suggest you create an additional column to separate the rates and then convert the formulas to a value to maintain the rates.
Sunny
Thanks Sunny.
Separately, I'm looking to insert a macro/button in the database which returns the user to the latest spreadsheet based on a given day's date. I was looking on Google and it is do-able if you put the dates that relate to each payroll period in each sheet & then do VBA code to find that code. Could you create this code for me in the example sheet I included above so I can copy it straight into my actual sheet?
Andy
Hi Andy
Please refer attachment.
You will need to include a button on each sheet.
Sunny
Hi Sunny, are you able to help me with this amendment to a previous excel you developed for me. See comments box inside.
Thanks
Andy
Hi Andy
See if this is what you wanted. I have changed the year to 2018 to match the data for testing purpose.
You can delete the dates you entered in row 2 once everything is correct as I am only using it to compare against row 1.
If this is not what is expected, then please post samples of the expected output.
Hope this helps.
Merry Christmas
Sunny
