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

kunal kishore

New Member
I have date in this format - 30.11.2006.Now this is actually not the proper format for date.No conditional formatting can be applied,neither the difference between two dates in same format can be calculated.I've tried to format the cells but it isn't working.Now I want the dates to appear in the same above format where all types of formulas,conditional formatting etc.can be applied.Is it possible?Please help.
 
Even the custom no.format is not working in this case.Dan is quiet correct this type of format is from SAP.I've tried other options,but failed.
 
I've devised a method by completely splitting the date format by using right,left and mid

functions.However not able to combine the whole into a single formula.Could you please suggest a better option?
 
@Kunal.. ok, so assuming the messed up SAP date is in cell A1,

in A2 write


=date(value(left(a1,2)),value(mid(a1,3,2)),value(right(a1,4)))


This will get you equivalent date value in cell A2.


PS: You can use text-to-columns utility to bulk-cleanse dates. See here: http://chandoo.org/wp/2010/03/23/text-to-date-convertion/
 
I think,I've solved the problem myself.


Date is in cell C2,I applied the formula -=DATE(RIGHT(C2,4),MID(C2,4,2),LEFT(C2,2)).


formatted the result cell into Date format-Austria(Germany).Got the result.
 
Back
Top