+ Post New Thread
Results 1 to 14 of 14
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. #1

    nephilim's Avatar
    Join Date
    Nov 2008
    Location
    Dunstable
    Posts
    11,665
    Thank Post
    1,614
    Thanked 1,866 Times in 1,384 Posts
    Blog Entries
    2
    Rep Power
    400

    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. #2

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    333
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    Err you mean like date-now?

    =A1-TODAY()

    Or did I misunderstand that?

  3. #3

    nephilim's Avatar
    Join Date
    Nov 2008
    Location
    Dunstable
    Posts
    11,665
    Thank Post
    1,614
    Thanked 1,866 Times in 1,384 Posts
    Blog Entries
    2
    Rep Power
    400
    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. #4

    nephilim's Avatar
    Join Date
    Nov 2008
    Location
    Dunstable
    Posts
    11,665
    Thank Post
    1,614
    Thanked 1,866 Times in 1,384 Posts
    Blog Entries
    2
    Rep Power
    400
    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. #5

    nephilim's Avatar
    Join Date
    Nov 2008
    Location
    Dunstable
    Posts
    11,665
    Thank Post
    1,614
    Thanked 1,866 Times in 1,384 Posts
    Blog Entries
    2
    Rep Power
    400
    Cracked it! hahaha, at last!

  6. #6

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    333
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    Quote Originally Posted by nephilim View Post
    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. #7

    nephilim's Avatar
    Join Date
    Nov 2008
    Location
    Dunstable
    Posts
    11,665
    Thank Post
    1,614
    Thanked 1,866 Times in 1,384 Posts
    Blog Entries
    2
    Rep Power
    400
    Yeh, took me a while to work out but I got there in the end!

  8. #8

    Steve21's Avatar
    Join Date
    Feb 2011
    Location
    Swindon
    Posts
    2,686
    Thank Post
    333
    Thanked 513 Times in 481 Posts
    Rep Power
    178
    Quote Originally Posted by nephilim View Post
    Yeh, took me a while to work out but I got there in the end!
    Sorted the month part too?

    Steve

  9. #9

    nephilim's Avatar
    Join Date
    Nov 2008
    Location
    Dunstable
    Posts
    11,665
    Thank Post
    1,614
    Thanked 1,866 Times in 1,384 Posts
    Blog Entries
    2
    Rep Power
    400
    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. #10

    nephilim's Avatar
    Join Date
    Nov 2008
    Location
    Dunstable
    Posts
    11,665
    Thank Post
    1,614
    Thanked 1,866 Times in 1,384 Posts
    Blog Entries
    2
    Rep Power
    400
    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. #11

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Quote Originally Posted by nephilim View Post
    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. #12

    nephilim's Avatar
    Join Date
    Nov 2008
    Location
    Dunstable
    Posts
    11,665
    Thank Post
    1,614
    Thanked 1,866 Times in 1,384 Posts
    Blog Entries
    2
    Rep Power
    400
    Genius, that fixed it!

  14. #13

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,432
    Thank Post
    1,432
    Thanked 1,160 Times in 794 Posts
    Rep Power
    705
    Quote Originally Posted by nephilim View Post
    Genius, that fixed it!
    Glad to be of service!

  15. #14

    nephilim's Avatar
    Join Date
    Nov 2008
    Location
    Dunstable
    Posts
    11,665
    Thank Post
    1,614
    Thanked 1,866 Times in 1,384 Posts
    Blog Entries
    2
    Rep Power
    400
    You have been rep'd and thank'd Enjoy

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 6
    Last Post: 23rd December 2010, 06:54 PM
  2. Quick Excel Question
    By karldenton in forum Windows
    Replies: 2
    Last Post: 8th September 2009, 04:16 PM
  3. Excel Question / Formula Needed
    By timbo343 in forum How do you do....it?
    Replies: 22
    Last Post: 4th August 2008, 09:06 PM
  4. office for mac excel formula question.
    By russdev in forum Office Software
    Replies: 2
    Last Post: 9th May 2008, 11:51 AM
  5. Excel VBA Loop Question
    By vunsev in forum Coding
    Replies: 5
    Last Post: 11th April 2008, 08:08 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •