+ Reply to Thread
Results 1 to 11 of 11

Excel 2007 : Calculate years, months, day

  1. #1
    Registered User
    Join Date
    01-17-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    11

    Exclamation Calculate years, months, day

    hi guys! good morning/eve.....! im a new member and i want really your help...i have a problem here.

    example in:
    full date in A1. example 1-jan-11 and in b2=i want in B1 add 10 years but (i have the formula) if B2 is blank i want to change it to "-" or any char until i fill up the A1.


    1-Jan-11 expired: 1-Jan-21
    but if i delete the date in A1 here is the result

    _______________ expired: 31-Dec-09 <-- i want this to change "-" or any word until i fill up the A1...hope you guys get my problem.
    this is the formula that i use:
    =EDATE(A1,120)
    Last edited by NBVC; 01-17-2011 at 11:13 PM. Reason: exploring excel formula...need to know other formula creating a form

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,440

    Re: Help me to calculate years, months, day

    Hi,

    I think this is what you need:

    =IF(A1="","-",EDATE(A1,120))
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    01-17-2011
    Location
    Kent
    MS-Off Ver
    Excel 2007
    Posts
    34

    Re: Help me to calculate years, months, day

    Have you tried an IF Statement? IF(A1="","-",EDATE(A1,120))

  4. #4
    Registered User
    Join Date
    01-17-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    11

    Re: Help me to calculate years, months, day

    Quote Originally Posted by sweep View Post
    Hi,

    I think this is what you need:

    =IF(A1="","-",EDATE(A1,120))
    W0W thanks!!!!! it work!!!!! sir can you explain the two quotes? and how it works?

  5. #5
    Registered User
    Join Date
    01-17-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    11

    Re: Help me to calculate years, months, day

    Quote Originally Posted by AppSupportKarl View Post
    Have you tried an IF Statement? IF(A1="","-",EDATE(A1,120))
    nope sir... I know only basics in excel..

    sir i have a problem again... sir is there a formula that if the cell is blank it will fill color grey or any color i want but if i type in a cell it fill color white....

  6. #6
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Calculate years, months, day

    The best way to learn what a formula is doing is to evaluate the formula.

    Select the cell with the formula and seclet Alt + t,u,f

    Then you can step thru what is happening.

    For the EDATE function look in Excel help. 120 months divided by 12 equals your 10 years.

    The first part of the formula says, If A1 does not equal anything "", the just put a - in the cell.

    When you fill A1 with a date it is no longer "" so the formula proceeds to the second half, the EDATE function.
    HTH
    Regards, Jeff

  7. #7
    Registered User
    Join Date
    01-17-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    11

    Re: Need Help in some advance excel formula.

    PROBLEM 3:

    guys need help for this formula...


    A1= 1-JAN-05 B1= 1-JAN-10 current date: 17-JAN-11 in C1=VALID, NOT VALID OR RENEWAL

    the problem is... if the expiry more than 8-12 months and the current date is 1-jan-08 the result in C1 will be valid, if B1 expire in 1-jan-10 and the current date is 17-jan-11 it will be not valid but if the expiry is B1= 1-jan-10 but the current date is 28-dec-09 it will be renewal...is possible to get this formula to you guys? i really need your help....

  8. #8
    Registered User
    Join Date
    01-17-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    11

    Re: Calculate years, months, day

    Quote Originally Posted by jeffreybrown View Post
    The best way to learn what a formula is doing is to evaluate the formula.

    Select the cell with the formula and seclet Alt + t,u,f

    Then you can step thru what is happening.

    For the EDATE function look in Excel help. 120 months divided by 12 equals your 10 years.

    The first part of the formula says, If A1 does not equal anything "", the just put a - in the cell.

    When you fill A1 with a date it is no longer "" so the formula proceeds to the second half, the EDATE function.
    thank youy sir!!!! i get it now what the quotes do in excel...is possible also to use this in my problem 2?

    this is the problem 2:

    sir i have a problem again... sir is there a formula that if the cell is blank it will fill color grey or any color i want but if i type in a cell it fill color white....

  9. #9
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Calculate years, months, day

    take a look at http://www.contextures.com/xlcondFormat01.html

    for your problem3 please attach a workbook which will detail all three of your results

  10. #10
    Registered User
    Join Date
    01-17-2011
    Location
    Philippines
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    11

    Re: Calculate years, months, day

    Quote Originally Posted by jeffreybrown View Post
    take a look at http://www.contextures.com/xlcondFormat01.html

    for your problem3 please attach a workbook which will detail all three of your results
    here is the attachment sir.... for my next post i will create a different thread title because admin staff send me a message (sorry)
    Attached Files Attached Files

  11. #11
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Calculate years, months, day

    Sorry it's getting late here and my brain is just not digesting this problem of yours.

    I'll try back again tomorrow.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1