need date formula

[yallamanda.b]

Hi Team,

can you please share the date formate,

 

[NARAYANK991]

Hi ,

Try this :

=0+(MID(A2,9,2) & " " & MID(A2,5,3) & " " & RIGHT(A2,4))

Enter this in B2 , and copy down.

Narayan

 

[bosco_yip]

Just notice that the OP's column A data all appeared in "June 2016",

this shorter formula can consider,

in B2 copy down :

=--(MID(A2,9,2)&-6)

Regards
Bosco

 

[PP3321]

@bosco_yip
@NARAYANK991

Thank you always but if you do not mind, could you please explain
conditions for turning text to date serial number?

I googled "requirement for converting to date serial"
But I could not find answer.

For example, below worked too but it just has "13-6"

=DATEVALUE(MID(A2,9,2)&-6)

 

[NARAYANK991]

Hi ,

If you enter two digits separated by a hyphen (-) or a forward slash (/) , then Excel interprets the data automatically as a date , and does its best to make it a valid date.

Thus 1-1 or 1/1 is interpreted as January 1 , 2016 , where the year is the current year.

However 1-32 or 1/32 is interpreted as January 1 , 1932 !

Even worse , 1-23 or 1/23 is interpreted as January 1 , 2023 !

Thus , it is always good to use as much of the data as possible , even when we can see that all the dates are in June or in 2016.

Using a few more functions is not going to overload Excel , nor is using a few functions less going to make your workbook more efficient.

Narayan

 

[PP3321]

I agree with your point totally. Accuracy is more important.
Thank you for your explanation...

*I was able to find this on Google too...

If the year portion of the date_text argument is omitted, the DATEVALUE function uses the current year from your computer's built-in clock. Time information in the date_text argument is ignored.

 

[yallamanda.b]

Thanks for all.