# Excel Question

Printable View

• 12th October 2011, 04:34 PM
nephilim
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.
• 12th October 2011, 04:36 PM
Steve21
Err you mean like date-now?

=A1-TODAY()

Or did I misunderstand that?
• 12th October 2011, 04:37 PM
nephilim
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,"")
• 12th October 2011, 06:03 PM
nephilim
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?
• 12th October 2011, 06:09 PM
nephilim
Cracked it! hahaha, at last! :D
• 12th October 2011, 06:12 PM
Steve21
Quote:

Originally Posted by nephilim
Cracked it! hahaha, at last! :D

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
• 12th October 2011, 06:19 PM
nephilim
Yeh, took me a while to work out but I got there in the end! :)
• 12th October 2011, 06:21 PM
Steve21
Quote:

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
• 12th October 2011, 06:25 PM
nephilim
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?
• 13th October 2011, 10:21 AM
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,"")
• 13th October 2011, 10:28 AM
LosOjos
Quote:

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,"")))))))`
• 13th October 2011, 10:30 AM
nephilim
Genius, that fixed it! :D
• 13th October 2011, 10:33 AM
LosOjos
Quote:

Originally Posted by nephilim
Genius, that fixed it! :D

Glad to be of service! :)
• 13th October 2011, 10:35 AM
nephilim
You have been rep'd and thank'd :) Enjoy