//some 3rd party need geo

# Excel - delay automatic completion

cotom 21:29 22 Oct 04
Locked

I am preparing some calculations within a spreadsheet for staff personnel records. What I need is for a formula to take the date of commencement of post and depending on length of service update the annual leave entitlement, eg. if the person reaches 10 yrs they will more from 27 days to 30 days. This I can acheive myself through the 'IF'logical test. However, here is my problem, the new entitlement doesn't start until the 1st January of the year after the person has reached the service level, not the exact point where they reach it. So how do I get the formula to update the record perhaps months after the person reaches the entitlement indicated by the formula? eg 10 years are reached in July '04 but the new entitlement doesn't start until Jan 1st '05!

I wondered about a Macro but wasn't sure how to go about that, if that even is the solution. Turning off the auto-update until Jan each year is no good as it would affect other formulas as well. Any help?

VoG II 22:09 22 Oct 04

cotom 22:48 22 Oct 04

VoG

IF((SUM(NOW()-AG12)/365)>=15,30,IF((SUM(NOW()-AG12)/365)>=12,29,IF((SUM(NOW()-AG12)/365)>=10,28,IF((SUM(NOW()-AG12)/365)>=7,27,IF((SUM(NOW()-AG12)/365)>=5,26,20)))))

AG is the start date column

VoG II 23:22 22 Oct 04

You need to set one or more of the parts of your formula to

=DATE(YEAR(NOW())+1,1,1)

I have two confessions to make (1) I am useless with Excel and dates and (2) I had to ask on another forum to come up with that.

cotom 11:07 23 Oct 04

VoG

Thanks for the response. I've had to learn Excel as I go along so my formulas may not be the cleverest at times. If I follow it, it adds one year to the current date and the '1,1' at the end makes it Jan 1st. Am I correct?
Sorry to be a pain, but how would I incorporate this into my formula to make it work for all of the sections. I appreciate that you suggested that Excel dates werent your thing, but I am very much an amateur.

This is one way to achieve what you require in that the inspection is only looking at the year element of the 2 dates, and by increasing your check figures by one each time then the change can not take place until the 1 Jan in each case

=IF(YEAR(TODAY())-YEAR(A12)>=16,30,IF(YEAR(TODAY())-YEAR(A12)>=13,29,IF(YEAR(TODAY())-YEAR(A12)>=11,28,IF(YEAR(TODAY())-YEAR(A12)>=8,27,IF(YEAR(TODAY())-YEAR(A12)>=6,26,20)))))

HTH

A slightly different approach is to put part of the formula in to a cell, I have used A1 and in A1 I have put "=Year(Today())" without the quotes, you can then place the following formula in B12 and copy as appropriate.

=IF(\$A\$1-YEAR(A12)>=16,30,IF(\$A\$1-YEAR(A12)>=13,29,IF(\$A\$1-YEAR(A12)>=11,28,IF(\$A\$1-YEAR(A12)>=8,27,IF(\$A\$1-YEAR(A12)>=6,26,20)))))

Get back if you require further help.

cotom 18:11 24 Oct 04

Thanks Whisperer,I've tried the first version you suggest and it seems to work well.

Cheers

This thread is now locked and can not be replied to.