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

How to format dates.

jindon said:
So, it is not really an Error, just "N/A"...

=IFERROR(INT(INDEX(G$1:G$21,MIN(IF(G1:G$21<>"N/A",ROW(G1:G$21))),)),DATE(RIGHT(INDEX(G$1:G$21,MIN(IF(G1:G$21<>"N/A",ROW(G1:G$21))),),4),LEFT(INDEX(G$1:G$21,MIN(IF(G1:G$21<>"N/A",ROW(G1:G$21))),),2),MID(INDEX(G$1:G$21,MIN(IF(G1:G$21<>"N/A",ROW(G1:G$21))),),4,2)))

And you must confirm with Ctrl+Shift+Enter before you copy down.
Click to expand...
That seemed to fix it. THANKS!!!
 
Chihiro said:
Something like below?
=IF(G4="N/A","N/A",IF(ISNUMBER(G4),G4,VALUE(RIGHT(G4,4)&"/"&LEFT(G4,2)&"/"&MID(G4,4,2))))

Since I'm using Japanese regional setting on the copy of Excel I'm working on, you may need to change below portion of the formula.

VALUE(RIGHT(G9,4)&"/"&LEFT(G9,2)&"/"&MID(G9,4,2))

To
VALUE(LEFT(G2,2)&"/"&MID(G2,4,2)&"/"&RIGHT(G2,4))
Click to expand...
Thank you.
 
You must log in or register to reply here.
Back
Top