• 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...

  • 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

Thomas Kuriakose

Active Member
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
 

Attachments

  • Date with zeros.xlsx
    8.7 KB · Views: 2
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:
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
 
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
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
 
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
 

Attachments

  • Date with zeros.xlsx
    10.6 KB · Views: 1
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

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:
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
 

Attachments

  • Date with zeros.xlsx
    10.9 KB · Views: 3
Respected Sir,

Thank you for this formula,

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

Thanks,

with regards,
thomas
 

Attachments

  • Date with zeros.xlsx
    11.1 KB · Views: 2
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
 
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
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:
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
 

Attachments

  • Date with zeros.xlsx
    11.2 KB · Views: 5
.......
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.
thomas

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
 

Attachments

  • Date with zeros.xlsx
    33.4 KB · Views: 1
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
 
Back
Top