Convert date to different format

[DMP3]

I have a formula which returns the date in yymmdd for which I want to convert to a serial date.

example:
in cell J6 I have this formula = RIGHT(LEFT(B6,12),6)
which returns "110319"

I want to convert this date (110319 which is yymmdd) to a serial date (ie 40621)

I appreciate any help!

 

[jmthompson]

Replace your formula in J6 with this

=DATE(Concatenate("20",LEFT(RIGHT(LEFT(B6,12),6),2)),MID(RIGHT(LEFT(B6,12),6),3,2),RIGHT(RIGHT(LEFT(B6,12),6),2))

This formula does require that all date values are always 2 digits.

Caution- this formula will only work with dates through the year 2099

 

[Scott Huish]

Replace your formula in J6 with this:

=TEXT(MID(B6,9,4)&MID(B6,7,2),"00\/00\/00")+0