VIP
Trusted Members
June 25, 2016
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[LEFT],">="&EDATE(TODAY(),-6))
=SUMIFS(tbl_4[Contracted hours],tbl_4[LEFT],">="&"01/07/2017")
Hope this helps.
Sunny
October 29, 2017
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
VIP
Trusted Members
June 25, 2016
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
October 29, 2017
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
VIP
Trusted Members
June 25, 2016
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
October 29, 2017
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
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
June 25, 2016
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
October 29, 2017
Hi Sunny, the formula's all work fine, thanks.
The only logical thing that won't work with the current formula's is if (for example), the staff member started on 08/01/2018 when the 'JAN' pay period began on 07/01/2018. They would need to be included in 'JAN' pay period but the formula doesn't allow this to happen. They are only inserted from 'FEB' onwards...?
It works all ok when a leave date is inserted and they are in the last pay period because of the way the dates/formula's work.
Can you think of a way around this because it's a vital part of the wages sheet I'm trying to design?
Andy
P.S I know we could just amend the start date to suit the formula but I want the start date to be accurate for future records.
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
June 25, 2016
October 29, 2017
Hi Sunny/all, I'm looking for some help in the attached spreadsheet to calculate holiday entitlement for my employees. I'm looking to have this calculated based on start or end date for the 2021 calendar year. Are you able to help me?
Happy to take any questions.
Thanks
Andy
1 Guest(s)