Err you mean like date-now?
=A1-TODAY()
Or did I misunderstand that?
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.
Err you mean like date-now?
=A1-TODAY()
Or did I misunderstand that?
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,"")
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?
Cracked it! hahaha, at last!
Yeh, took me a while to work out but I got there in the end!
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?
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,"")))))))
nephilim (13th October 2011)
Genius, that fixed it!
You have been rep'd and thank'd Enjoy
There are currently 1 users browsing this thread. (0 members and 1 guests)