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

Excel date formula help

Jagdev Singh

Active Member
Hi Experts,

I somehow managed to derive a formula to display a date in the way I want it, but I am facing issue with the year which is prior 2000. The reason for this is usage figure 20 in the formula of date. Please let me know if there is any turnaround way to deal with the years prior 2000.

Regards,

JD
 

Attachments

  • sample.xlsx
    31.6 KB · Views: 4
Hi JD,
Good Day...

I have include a condition for 99 in your existing formula:
IF(--RIGHT(A2,2)=99,0,20)

so you can use this as a start-up:
=IF(LEN(A2)=5,DATE(IF(--RIGHT(A2,2)=99,0,20)&RIGHT(A2,2),MID(A2,2,2),LEFT(A2,1)),IF(LEN(A2)=6,DATE(IF(--RIGHT(A2,2)=99,0,20)&RIGHT(A2,2),MID(A2,3,2),LEFT(A2,2)),""))

Currently I have include condition for 99 only.

See if this works.

Regards,

Khalid
 
Hi Khalid

Thanks for the formula it works fine with the year 99, but I have many years in the range of 90’s. I derived one formula and would like to share it with you.

=IF(LEN(A2)=5,DATE(IF(RIGHT(A2,2)*1<50,"20" & RIGHT(A2,2),"19"&RIGHT(A2,2)),MID(A2,2,2),LEFT(A2,1)),IF(LEN(A2)=6,DATE(IF(RIGHT(A2,2)*1<50,"20" & RIGHT(A2,2),"19"&RIGHT(A2,2)),MID(A2,3,2),LEFT(A2,2)),""))

Kind regards for your input on this thread.

Regards,

JD
 
:) JD i was working on this:

In case you need the range of years from 90 to 99:

just replace:
IF(--RIGHT(A2,2)=99,0,20)

with this:

IF(OR(--RIGHT(A2,2)=ROW($90:$99)),0,20)

Now it become array formula you have to CSE it.

Adjust the $90:$99 as per your need.

Regards,
 
Hi Khalid

Thanks for the formula it works fine with the year 99, but I have many years in the range of 90’s. I derived one formula and would like to share it with you.

=IF(LEN(A2)=5,DATE(IF(RIGHT(A2,2)*1<50,"20" & RIGHT(A2,2),"19"&RIGHT(A2,2)),MID(A2,2,2),LEFT(A2,1)),IF(LEN(A2)=6,DATE(IF(RIGHT(A2,2)*1<50,"20" & RIGHT(A2,2),"19"&RIGHT(A2,2)),MID(A2,3,2),LEFT(A2,2)),""))

Kind regards for your input on this thread.

Regards,

JD
Good JD
Thanks for sharing.
Its working fine.
 
If you replace:
=ROW($90:$99)

with:
={90,91,92,93,94,95,96,97,98,99}

You will not required CSE.

Regards,
 
Hi Khalid

Thanks for the formula it works fine with the year 99, but I have many years in the range of 90’s. I derived one formula and would like to share it with you.

=IF(LEN(A2)=5,DATE(IF(RIGHT(A2,2)*1<50,"20" & RIGHT(A2,2),"19"&RIGHT(A2,2)),MID(A2,2,2),LEFT(A2,1)),IF(LEN(A2)=6,DATE(IF(RIGHT(A2,2)*1<50,"20" & RIGHT(A2,2),"19"&RIGHT(A2,2)),MID(A2,3,2),LEFT(A2,2)),""))

Kind regards for your input on this thread.

Regards,

JD
Hey JD Khalid works like a magic for you everytime..don't u think??;)
 
Back
Top