Another Question about Dates - Dire Situation

[eSourceCity]

How can I show a date in the format YYYY-MM-DD referencing cells from a different sheet when the referenced cells are written as text using the following two formats:

1) 2014-04-23T16:38:06.000Z (currently formatted as "general")
2) 4/21/2014 9:09:52 PM (currently formatted as custom - m/d/yyyy h:mm



[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Raw Date[/TD]
[TD]Formatted Date[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]

<tbody>
[TD="class: xl65"]4/21/2014 21:31[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2014-04-22T15:32:38.000Z[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4/21/2014 22:24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2014-04-22T18:32:05.000Z[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]

<tbody>
[TD="class: xl66"]2014-04-22T20:00:25.000Z[/TD]

</tbody>

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4/21/2014 23:39[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

For example, the above table, what would be the single formula that would return the requested format YYYY-MM-DD for each row.

 

[Israel Monreal]

PUEDES ProBar esta, espero te SIRVA!
= TEXTO (SUSTITUIR (SUSTITUIR (B2, "Z", " ", 1), "T", " ", 1), " AAAA-MM-DD ")

me PARECE Que en tu Idioma es algo asi!

= TEXT (replace (replace (B2; "Z", " ", 1); "T", " ", 1), "aaaa-mm-dd")

la idea es sustituir la letra "T" y "Z", POR Espacios y USAR la fórmula de texto pára dar Formato

 

[eSourceCity]

PUEDES ProBar esta, espero te SIRVA!
= TEXTO (SUSTITUIR (SUSTITUIR (B2, "Z", " ", 1), "T", " ", 1), " AAAA-MM-DD ")

me PARECE Que en tu Idioma es algo asi!

= TEXT (replace (replace (B2; "Z", " ", 1); "T", " ", 1), "aaaa-mm-dd")

la idea es sustituir la letra "T" y "Z", POR Espacios y USAR la fórmula de texto pára dar Formato

Thanks, but the formula needs to work with both existing formats shown in the referenced cells. Perhaps there is a hardcode for this?

 

[Israel Monreal]

Thanks, but the formula needs to work with both existing formats shown in the referenced cells. Perhaps there is a hardcode for this?

y esta?
4/21/2014 21:31 2014/04/21
2014-04-22T15:32:38.000Z 2014/04/22
4/21/2014 22:24 2014/04/21
2014-04-22T18:32:05.000Z 2014/04/22
2014-04-22T20:00:25.000Z 2014/04/22
4/21/2014 23:39 2014/04/21

use la siguiente formula:

=SI(ESERROR(ENCONTRAR("/",A2)),TEXTO(SUSTITUIR(SUSTITUIR(A2,"Z"," ",1),"T"," ",1),"aaa/mm/dd"),TEXTO(EXTRAE(A2,ENCONTRAR("/",A2,4)+1,4)&"/"&EXTRAE(A2,1,ENCONTRAR("/",A2,4)-1),"aaa/mm/dd"))

en ingles me parece seria asi:

=IF(ISERROR(FIND("/",a2)),TEXT(SUBSTITUTE(SUBSTITUTE(a2,"Z"," ",1),"T"," ",1),"yyy/mm/dd"),TEXT(MID(a2,FIND("/",a2,4)+1,4)&"/"&MID(a2,1,FIND("/",a2,4)-1),"yyy/mm/dd"))

saludos!!

 

[eSourceCity]

y esta?
4/21/2014 21:31 2014/04/21
2014-04-22T15:32:38.000Z 2014/04/22
4/21/2014 22:24 2014/04/21
2014-04-22T18:32:05.000Z 2014/04/22
2014-04-22T20:00:25.000Z 2014/04/22
4/21/2014 23:39 2014/04/21

use la siguiente formula:

=SI(ESERROR(ENCONTRAR("/",A2)),TEXTO(SUSTITUIR(SUSTITUIR(A2,"Z"," ",1),"T"," ",1),"aaa/mm/dd"),TEXTO(EXTRAE(A2,ENCONTRAR("/",A2,4)+1,4)&"/"&EXTRAE(A2,1,ENCONTRAR("/",A2,4)-1),"aaa/mm/dd"))

en ingles me parece seria asi:

=IF(ISERROR(FIND("/",a2)),TEXT(SUBSTITUTE(SUBSTITUTE(a2,"Z"," ",1),"T"," ",1),"yyy/mm/dd"),TEXT(MID(a2,FIND("/",a2,4)+1,4)&"/"&MID(a2,1,FIND("/",a2,4)-1),"yyy/mm/dd"))

saludos!!

You Sir, are a genius. Works perfect once I tweak it to work in my specific spreadsheet. Thanks so much!!!

 

[Rick Rothstein]

If I understand your request correctly, I think you can use this formula and then format the cell using yyyy-mm-dd...

=INT(LEFT(A2,FIND("T",A2&"T")-1))

That way, your cells would contain real dates that could be used as part of calculations in other formula. However, if you are happy with a straight Text output, then you can use this formula instead...

=TEXT(INT(LEFT(A2,FIND("T",A2&"T")-1)),"yyyy-mm-dd")