Office Software Thread, Excel Question in Technical; I am trying to set up a spreadsheet and have the following if possible If the date is set to ...
1. ## Excel Question

I am trying to set up a spreadsheet and have the following if possible

If the date is set to say 15/10/2011 and in 5 years time something needs replacing, how will I set up the function to show that "X years Y days" until something needs replacing?

All help appreciated and I will test said functions and when done I will upload the final spreadsheet for all to use.

2. Err you mean like date-now?

=A1-TODAY()

Or did I misunderstand that?

3. Not quite

If something is on a 5 year renewal cycle and I want to see how long left to replace it...basically trying to set up so I can see in excel.

At the minute i have used an if function to show how many years but cant get how many months...if function looks like this...

=IF((I4+365)>TODAY(),J4-1,"")

4. Or how can I set it so that I do the following

If something is selected in 1 cell (Eg 1, 2, 3, 4, 5, 6 ,7) and the date is input, how can I add the appropriate number of years to that date?

So for example the date is 15/09/2011 and I select 5, it will update in a new cell with 15/09/2016 is that doable?

5. Cracked it! hahaha, at last!

6. Originally Posted by nephilim
Cracked it! hahaha, at last!
Sorry was on way home, =DATE(YEAR(B1)+A1, MONTH(B1), DAY(B1)) would be how I did it (A1 = 1,2,3/4 etc, B1 = date)

Steve

7. Yeh, took me a while to work out but I got there in the end!

8. Originally Posted by nephilim
Yeh, took me a while to work out but I got there in the end!
Sorted the month part too?

Steve

9. Just doing the last bit and then will upload....how can I duplicate cells to go down an entire column apart from the top 3 cells?

10. Right, got almost everything worked out, just need to know how I can set it so that this formula outputs as currency rather than just a decimal...

I tried setting the cells to currency but no joy

Formula is this

=IF(K4=1,I4/100*75,"")&IF(K4=2,I4/100*65,"")&IF(K4=3,I4/100*50,"")&IF(K4=4,I4/100*45,"")&IF(K4=5,I4/100*25,"")&IF(K4=6,I4/100*15,"")&IF(K4=7,I4/100*10,"")

11. Originally Posted by nephilim
Right, got almost everything worked out, just need to know how I can set it so that this formula outputs as currency rather than just a decimal...

I tried setting the cells to currency but no joy

Formula is this

=IF(K4=1,I4/100*75,"")&IF(K4=2,I4/100*65,"")&IF(K4=3,I4/100*50,"")&IF(K4=4,I4/100*45,"")&IF(K4=5,I4/100*25,"")&IF(K4=6,I4/100*15,"")&IF(K4=7,I4/100*10,"")
I might be wrong, but I think because you're using ampersand concatenation, your formula's output is actually text as opposed to numerical. To fix it, nest you IF statements, like so:

Code:
`=IF(K4=1,I4/100*75,IF(K4=2,I4/100*65,IF(K4=3,I4/100*50,IF(K4=4,I4/100*45,IF(K4=5,I4/100*25,IF(K4=6,I4/100*15,IF(K4=7,I4/100*10,"")))))))`

12. ## Thanks to LosOjos from:

nephilim (13th October 2011)

13. Genius, that fixed it!

14. Originally Posted by nephilim
Genius, that fixed it!

15. You have been rep'd and thank'd Enjoy

SHARE: