increment cell with a value

[QCGH]

Hi everyone,

I just joined the forums and it's my first time posting here. I found this forum with a great help in solving my problems with excel. I might as well ask for help.

Here's my situation. In column D8-D12, I have the years of service of my employees (the base values of D8-D12 are 8,6,12,40,25). My base date is the current date which is in A3.

What I want to do is base on the current date (In A3, I format it "yyyy" so that it would only show the year),
I want to increment the values D8-D12 by 1 when the year increases (ex. 2013 then next year 2014 and so on..) basing on A3.

Another is in column F8-F12 is the amount they will receive for the years of their service. I would like the column of F8-F12 to add 50 if the values of D8-D12 reach the multiples of 10.

I hope I had provided my problem clearly. Any help would be greatly appreciated. Thanks in advance.

[vlady]

there are functions like

=DATEDIF(datestartofservice,A3,"y")

or
=DATEDIF(datestartofservice,A3,"y")&" yrs "&DATEDIF(datestartofservice,A3,"ym")&" months"


this will auto increment based on the value in A3

If you have the Analysis ToolPak (tools, add-ins) there is the"NETWORKDAYS" function, this do not include the weekends or holidays in the count,used by HR personnel.

[QCGH]

Thanks vlady!

I'm not that good in excel, so whatever new things I've encountered at work, I browse different forums to find the solution.

Regarding my 2nd question => "Another is in column F8-F12 is the amount they will receive for the years of their service. I would like the column of F8-F12 to add 50 if the values of D8-D12 reach the multiples of 10."

Was that possible? or does it need to be solve using VBA? I'm not good at VBA though

[vlady]

so we have to check the value in F8 first if it is a multiple of 10?

if we will use another column for this one.

say in column G8 down

=IF(or(D8="",D8=0),"",IF(MOD(D8,10)=0,TRUE,FALSE)) -> check number in D8
now we put the increment of 50 "inside" the if formula replacing the True/False argument

=IF(or(D8="",D8=0),"",IF(MOD(D8,10)=0,F8 + 50, F8)) -> previous true/false

[QCGH]

Thanks vlady for the help. It worked.