time related help

[paramveer]

My job is to make salary of employees. I want some formula which depend on time i.e

assume some employee name Captain have taken LIC policy. which have 12 installments. I want a formula which i give command to excel formula after every month it increment one step. for example No of installment increment one step every month and after 12 it show nothing in that coloum

Policy amount

No. of installments

total amount recovered

amount balanced

850

7

5950

4250

 

[SirJB7]

Hi, paramveer!
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Besides try to elaborate and explain it in plain language, i.e., for non HR people. In my case I've understood about half of your explanation.
Regards!

 

[paramveer]

Hi Sir JB7

Now you please consider attach file

 

[SirJB7]

Hi, paramveer!
I should say that you didn't add much more information, but anyhow give a look at the uploaded file.
Regards!

 

[Debraj]

Hi Paramveer,

In B4, try this formula..

=VLOOKUP(TEXT(TODAY(),"mmmm"),A12:B24,2,0)

I think, all other part, already you have covered... regarding starting month and no of installment..

 

[SirJB7]

@Debraj(ex-Roy)
Hi!
Thanks for the catch, I missed that.
Regards!

 

[Debraj]

Hi SirJB007,

Dont worry.. above formula will never work..
We Indian, never pay our policy premium on time..

 

[SirJB7]

@Debraj(ex-Roy)
Hi!
I do worry, since I had the intention of asking paramveer for a policy of 100.000 £ (UK Pounds), ok, ℮ (Euros), but I don't settle for $ (US Dollars).
Have you seen that installments are with no interest? Oh... that's why I wanted a loan and not a policy...
Regards!

 

[paramveer]

Debraj thanks for your help can u explain your formula

 

[SirJB7]

Hi, paramveer!

Not only just as a tip, remember that if you enter in an empty cell "=XXX(" (unquoted) and press the "fx" button at the left of the edition bar text box, you'll be prompted with the function wizard which shows every argument, gives a brief description of the function and by the bottom left link lets you access the built-in or online help. It works for any function.

Have you tried doing this with Debraj(ex-Roy)'s formula? If so, which part of it (arguments, intermediate results, final value, etc.) didn't you understand?

Regards!

PS: Tip & clue. Try first with the inner function:
=TEXT(TODAY(),"mmmm")
and then with the whole original formula.