1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Date with zero value

Discussion in 'Ask an Excel Question' started by Thomas Kuriakose, Jun 18, 2017.

  1. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    559
    Respected Sirs,

    We have report in our ERP system which gives dates in dd.mm.yyyy format.

    We need to arrive at categories based on the date values. Kindly find attached a file with desired result in column C.

    If the date value is 00.00.0000 it should give category as A
    If the date is 2014 it should give category as B
    If the date is 2015 it should give category as C
    If the date is 2016 it should give category as D

    Kindly let me know how to get this result.

    Thank you very much,

    with regards,
    thomas

    Attached Files:

  2. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,046
    Try,

    In C2, copy down :

    =LOOKUP(RIGHT(A2,4),{"0000";"2014";"2015";"2016"},{"A";"B";"C";"D"})

    or,

    =CHAR(MAX(RIGHT(A2,4)-1948,65))

    Regards
    Bosco
    Last edited: Jun 18, 2017
    Thomas Kuriakose likes this.
  3. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    559
    Respected Sir,

    Thank you s much for this solution.

    Sir one more query, if we need to use the date 00.00.0000 and other dates in column A for count, conditional format or sum functions how can we use this. For example,
    a) conditional format all 00.00.0000 dates to red color.
    b) if todays date minus dates in column A is greater than 365 - 1 year

    Thank you very much once again,

    with regards,
    thomas
  4. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,046
    a) Select A2:A7 >> conditional format >> New Rule >> Use a formula :

    ="00.00.0000"=$A2

    >> Format >> red color >> OK

    upload_2017-6-19_18-36-19.png

    b) If todays date minus dates in column A is greater than 365, formula :

    =IFERROR(TODAY()-SUBSTITUTE(A2,".","/")>365,"")

    Regards
    Bosco
    Thomas Kuriakose likes this.
  5. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    559
    Respected Sir,

    Thank you so much for this solution once again.

    Much appreciated.

    with regards,
    thomas
  6. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    559
    Respected Sir,

    One clarification, when we use substitute to change the date with "/", I am not able to get correct values in H5 and H6 as per the attached file.

    Also with substitution how to get the value for 00.00.0000 to zero or is not a date.

    Kindly let me know where am going wrong.

    Thank you very much once again,

    with regards,
    thomas

    Attached Files:

  7. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,046
    Try,

    1] Use IF function to test :

    =IF(-RIGHT(G2,5),TODAY()-G2,0)

    or,

    2] To use IFERROR function :

    =IFERROR(TODAY()-G2,0)

    Regards
    Bosco
    Last edited: Jun 19, 2017 at 8:25 AM
    Thomas Kuriakose likes this.
  8. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    559
    Respected Sir,

    Thank you very much for the revised formula.

    The values for dates in G5 and G6 are not getting calculated. Kindy check and let me know where I am wrong.

    Thank you very much,

    with regards,
    thomas

    Attached Files:

  9. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,046
    What is the expected result in I5 and I6, marking of "not ok" in column J ?

    Regards
    Bosco
    Last edited: Jun 19, 2017 at 9:43 AM
  10. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    559
    Respected Sir,

    The expected result in I5 = 427 and in I6 = 795.

    Thank you very much,

    with regards,
    thomas
  11. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,046
    In I2, copy down :

    =IFERROR(TODAY()-G2-1,0)

    Regards
    Bosco
  12. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    559
    Respected Sir,

    Thank you for this formula,

    Kindly check the attached. It is giving zero value for the two dates.

    Thanks,

    with regards,
    thomas

    Attached Files:

  13. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,046
    Are you talking about L5 and L6, they are worked for me !

    upload_2017-6-20_19-29-54.png

    Regards
    Bosco
  14. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    559
    Respected Sir,

    Yes, how did you get the correct values, is it a formatting issue or I have done some mistake.

    In fact I am not getting the results in H5/H6, I5/I6 also.

    Kindly let me know.

    Thanks,

    with regards,
    thomas
  15. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,046
    Maybe you are using a old Excel 2003 or earlier version.

    IFERROR was introduced since Excel 2007.

    Then,

    You should consider to use IF function instead :

    =IF(-RIGHT(G2,5),TODAY()-G2-1,0)

    Regards
    Bosco
    Last edited: Jun 19, 2017 at 1:03 PM
  16. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    559
    Respected Sir,

    It seems I am not getting the place where I am going wrong.

    I am using excel 2016. Kindly find attached the sheet with the if function which is giving value error.

    Thanks,

    with regards,
    thomas

    Attached Files:

  17. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,046
    upload_2017-6-20_22-1-41.png

    1] The formulas in Column L and M are worked for me as see above.

    2] In suit with the "Column M" IF function, the Substitute formula in G2 was changed to :

    =SUBSTITUTE(A2,".","-")

    3] Enclosing herewith my testing file for your information

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose likes this.
  18. Thomas Kuriakose

    Thomas Kuriakose Active Member

    Messages:
    559
    Respected Sir,

    Thank you very much for the revised formulas.

    I need to recheck the formats as it is still not working at my end. This is showing value error.

    Thank you very much once again,

    with regards,
    thomas

Share This Page