featured_spectre Posted October 12, 2011 Posted October 12, 2011 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.
Steve21 Posted October 12, 2011 Posted October 12, 2011 Err you mean like date-now? =A1-TODAY() Or did I misunderstand that?
featured_spectre Posted October 12, 2011 Author Posted October 12, 2011 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,"")
featured_spectre Posted October 12, 2011 Author Posted October 12, 2011 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?
Steve21 Posted October 12, 2011 Posted October 12, 2011 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
featured_spectre Posted October 12, 2011 Author Posted October 12, 2011 Yeh, took me a while to work out but I got there in the end!
Steve21 Posted October 12, 2011 Posted October 12, 2011 Yeh, took me a while to work out but I got there in the end! Sorted the month part too? Steve
featured_spectre Posted October 12, 2011 Author Posted October 12, 2011 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?
featured_spectre Posted October 13, 2011 Author Posted October 13, 2011 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,"")
LosOjos Posted October 13, 2011 Posted October 13, 2011 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: =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,""))))))) 1
LosOjos Posted October 13, 2011 Posted October 13, 2011 Genius, that fixed it! Glad to be of service!
featured_spectre Posted October 13, 2011 Author Posted October 13, 2011 You have been rep'd and thank'd Enjoy
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now