"Todas las semanas me llega un informe con diversos datos y en la primera columna me encuentro con fechas en formato americano y, además, con formato de texto. Me gustaría poder convertirlas a formato español y poder operar con ellas".
Vamos a solucionar este problema con las funciones FECHA, IZQUIERDA, DERECHA y EXTRAE. Supongamos que tenemos la siguiente tabla con fechas, que además nos llegan con formato texto, en formato americano (mes,día,año) y queremos convertirlas en formato español y numérico (día,mes,año):
Vamos a "trocear" el problema para que se entienda mejor la solución:
1. Nos situamos en C3 y escribimos la fórmula:
=EXTRAE(B3;4;2)
La función EXTRAE nos permite extraer parte de un texto desde una posición inicial que le debemos indicar. En nuestro caso le estamos pidiendo que del texto que hay en B3 y desde la posición inicial 4 (4 caracteres) extraiga 2 caracteres. De esta manera obtendremos los dos caracteres referente al día.
2. Nos situamos en la celda D3 y escribimos la siguiente fórmula:
=IZQUIERDA(B3;2)
Esta función extrae dos caracteres de la celda B3 empezando por la izquierda.
3. Nos situamos en E3 y escribimos la fórmula:
=DERECHA(B3;4)
Esta función extrae cuatro caracteres de la celda B3 empezando por la derecha.
Ya hemos conseguido separar los caracteres referentes al día, mes y año:
Sólo nos queda "reunirlos" con formato de fecha válido para poder operar después con ellas. Esta labor la realiza la función FECHA:
4. Nos situamos en la celda F3 y escribimos:
=FECHA(E3;D3;C3)
Seleccionando ahora el rango C3:F3 y haciendo doble clic en la parte inferior derecha de la selección terminaríamos nuestro trabajo:
Podemos resumir todos estos pasos en una sola fórmula, a saber:
=FECHA(DERECHA(B3;4);IZQUIERDA(B3;2);EXTRAE(B3;4;2))
excelente información es exactamente lo que estaba buscando!!!!!!
ResponderEliminarGracias Karen. Me alegra que te haya resultado útil.
ResponderEliminarHola Kiko, yo tengo un problema un tanto similar, en una hoja se descargaron datos de un programa en el que, no sé cómo; aparecen las fechas invertidas de la forma siguiente: 08/03/2009, en formato español, esto se leería como 8 de marzo de 2009, cuando en realidad la fecha es 3 de agosto de 2009. ¿Sabes cómo cambiarlo? No sé sime expliqué bien. Saludos y muchas gracias.
ResponderEliminarPerdón pero no entiendo bien la pregunta. Aparentemente es lo mismo que solucionamos en este artículo. La cuarta fecha de este ejemplo aparece como 07/01/2009 cuando en realidad es 01/07/2009
ResponderEliminarHola Kiko,
ResponderEliminarLa fórmula funcionaria siempre y cuando la fecha americana no coincida con una fecha española. Por ejemplo 03/01/2010 siendo la fecha americana, usando tu fórmula se convierte en 20/06/2084 porque entiende que es 3 de Enero de 2010.
Me ha gustado mucho la informacion, antes yo usaba mucho el comando "si", y se que funciona limitadamente, y con mucha mas cosas.Pero he visto muy funcional y hasta divertida la forma tambien de conseguir las fechas de esta forma. Gracias. Cristian
ResponderEliminarHola Kiko.Gracias por la información. Sólo hay una cosa que no me resulta: tras seleccionr el rango C3:F3 hago doble clik en la parte inferior derecha pero no sucede nada. Yo utilizo excel_Office 2007.
ResponderEliminarEl resto de fórmulas trabaja perfecto.Gracias
Hola, oye la formula funciona muy bien con formato de texto, pero el ultimo ejemplo que pones 07/01/2009, en mi sistema si es coherente, no es un formato de texto, al ocupar la técnica no da como resultado 01/07/2009, que sugieres hacer con formatos númericos.
ResponderEliminarCuando hay celdas con formato texto y otras con formato numérico te sugiero la siguiente fórmula. La primera parte soluciona las celdas con formato texto y la segunda las que presentan formato numérico pero no devuelven la fecha en el formato deseado (español):
ResponderEliminar=SI(ESERROR(MES(B4));FECHA(DERECHA(B4;4);IZQUIERDA(B4;2);EXTRAE(B4;4;2));
FECHANUMERO(MES(B4)&"/"&DIA(B4)&"/"&AÑO(B4)))
Hola Kiko,tengo un problema bastante complejo. Espero me puedas ayudar. Tengo un sistema contable que al sacar un reporte de facturación tira la fecha con el formato d/m/aa. El problema es que al exportarlo al excel,este automaticamente cambia el formato a m/d/aa. Es decir la fecha de una factura del 1ro de junio del 2010 en el sistema es 1/6/10 pero al pasarla a excel asoma el número tal cual en la celda (1/6/10) pero el formato se cambia a m/d/aa. Dicho de otra forma, pasa a ser enero 6 del 2010. No puedo usar la forma EXTRAE porque las fechas no tienen un formato de dos dígitos para cada valor. Es decir en los días y meses de un dígito solo sale 1 dígito y no 2. Ejemplo 1ro de junio de 2010 es 1/6/10 y no 01/06/10 y esto impide usar EXTRAE. Mi consulta puntual es: hay forma de decirle a excel quiero que cambies el formato de m/d/aa a d/m/aa pero no toques (des la vuelta) el numero que está en esa celda? Espero me puedas ayudar. Saludos. Pablo
ResponderEliminarHola Pablo. Por favor mándame una hoja a kiko.rial@ie.edu con el ejemplo de lo que te ocurre y lo que necesitas.
ResponderEliminarYo he encontrado una manera de hacerlo. Os la comento por si os sirve:
ResponderEliminar1. cerramos excel
2. nos vamos a panel de control--> configuración regional y elegimos Inglés de Estados Unidos.
3. abrimos excel
4. lo volvemos a cerrar.
5. volvemos a panel de control y lo ponemos a español.
6. excel habrá convertido todo en español sin fórmula.
Espero que os sirva de ayuda.
Saludos.
hahhaha... que feo....2013
Eliminar:-)
Eliminargracias .. simple y sencillo y me quitaste la peor pesadilla que tenía en mis manos con un reporte que modificaba las fechas y debía recapturarlas manualmente
EliminarMuchas gracias, fue de gran ayuda la informacion.
ResponderEliminarBUENA EXPLICACION PARA CAMBIAR DE FORMATO LAS FECHAS! GRACIAS FELIZ AÑO!!
ResponderEliminarmi hemano un millon de gracias
ResponderEliminarhola bueno el aporte pero una pregunta yo tengo un formato q también incluye la hora (02/25/11 01:55:00 PM) pero este esta en el formato americano lo que necesito es que se vuelva al español(25/02/11 01:55:00 PM)..la explicación q tu haces es muy buena pero yo necesito también la hora como puedo hacer??....de antemano muchas gracias
ResponderEliminarBuena explicación pero a mi no me funciono, encontré otra solución, le cambias el formato de todas las fechas a numero sin decimales, lo guardas y después le pones otra vez el formato de fecha.
ResponderEliminarHola kiko, mi problema es el siguiente:
ResponderEliminartengo dos columnas en excel en formato general aaaa/mm/dd pero sin los separadores es decir aaaammdd y me interesaria que me restara la fecha menor de la mayor, pero claro como va año/mes/dia no la puedo pasar de formato general a fecha porque no lo entiende, y si resto las celdas lo único que hace es una resta numérica pero no de fechas. ¿Como soluciono el problema? Gracias de antemano. Pepe
Para que lo entienda como fecha debes escribir la siguiente fórmula (estoy suponiendo que la primera fecha la tienes escrita en la celda B2):
Eliminar=VALOR(DERECHA(B2;2)&"/"&EXTRAE(B2;5;2)&"/"&IZQUIERDA(B2;4))
Con esto convertirás tus valores en fechas con las que trabajar.
como copio una columna que tiene solo fechas con formato de fechas a otra columna pero que se pase con formato de texto sin alterar la fecha
ResponderEliminarPartiendo de, por ejemplo, una fecha escrita en A1 nos ponemos en B1 y escribimos la siguiente fórmula:
Eliminar=TEXTO(A1;"dd/mm/aaaa")
Ahora copiaríamos hacia abajo tantas celdas como tengamos y posteriormente procedemos a copiar y pegar valores.
Gracias por la repuesta me ha servido
ResponderEliminarHola KIko,
ResponderEliminargracias a tus comentarios he podido solucionar la mayor parte de mi problema
ya que con las fechas en las que aparecen como dd/mm/aaaa todo bien, pero las que aparecen como dd/mmm/aaaa estas en texto americano no logro convertirlas a dd/mm/aaaa en español, ejemplo de lo que requiero 08/May/2012 a 08/05/2012 existira alguna forma de solucionarlo?
Perdón pero no sé si entiendo bien el problema. Supongo que la fecha de partida es May/08/2012 ? En este caso la fórmula a aplicar sería =IZQUIERDA(B3;3) De esta manera obtendríamos el mes. Si montas una tabla que sea el nombre del mes y en la siguiente columna el número del mes Ene;1;Feb;2... aplicando la función BUSCARV a dicha tabla obtendrías el número del mes y a partir de ahí ya sería el mismo caso que en este post.
EliminarGracias por tu respuesta, esto funciona con los textos en español pero creo no haberme explicado los datos bajan como texto en ingles en el siguiente orden, 23/Aug/2012 se requiere cambiar a un formato en español 23/08/2012 espero exista alguna solucion.
Eliminarde nuevo muchas gracias.
Vamos allá, a ver si es esto lo que necesitas:
Eliminar* En A2, y en las celdas debajo de ésta, tenemos fechas en el formato que indicas. Por ejemplo: 23/aug/2012
* En el rango D2:D13 escribo una lista con el nombre de los meses en inglés (sólo tres letras): Jan;Feb;Mar;Apr...
*En el rango E2:E13 escribo el número correspondiente al mes, desde el 1 hasta el 12.
En B2 escribo la siguiente fórmula:
=FECHA(DERECHA(A2;4);BUSCARV(EXTRAE(A2;4;3);$D$2:$E$13;2;FALSO);IZQUIERDA(A2;2))
Ya me dirás si es lo que buscabas.
excelente! esto lo soluciona, un millón de garcias
EliminarSi el mes o el día solo tienen un número no funciona la función. Por ejemplo 2/18/2013 Alguna solución??
ResponderEliminarPara que contemple cualquier caso, es decir, que tenga 1 ó 2 dígitos el día puedes utilizar la siguiente fórmula, suponiendo que la fecha en cuestión esté en la celda A1:
Eliminar=IZQUIERDA(A1;HALLAR("/";A1)-1)
Hola!! Se me hace muy util tus formulas, solo que tengo problemas al ejecutar la formula.. Y tengo el mismo problema de fechas pero no me sale :( podrias ayudarme por favor?
ResponderEliminarNecesito que me digas qué problema te encuentras. Un saludo
EliminarClaro... Problema ejemplo 02/20/2013 es mes, dia y año. Obviamente yo la quiero día mes y año aplico las formulas que das pero me marca error. Podrias ayudarme por favor...
ResponderEliminarSaludos!!
El ejemplo que me mandas es igual al tratado en el post y, teóricamente, no debería darte problemas. Necesito ver el fichero para averiguar que ocurre. Puedes mandármelo a kiko.rial@gmail.com? Un saludo
EliminarClaro.. Ya te envié el correo. Gracias por tomarte un tiempo para ayudarnos...
ResponderEliminarYa te mandé la solución. Un saludo
EliminarMuchas gracias por la solución, ya me salio... Oie tengo otra pregunta referente a fechas.. Podrias ayudarme?
ResponderEliminarTengo un problema.. tengo que sacar la diferencia entre dos fechas con horas por ejemplo:
ResponderEliminarEjemplo 1.
Primera fecha + 1Hora Segunda fecha + 2Hora
02/08/2011 18:00 pm 04/08/2011 20:00 pm
de estas fechas quiero saber la diferencia tanto en dias y horas...
Ejemplo 2
Primera fecha + 1Hora Segunda fecha + 2Hora
02/08/2011 18:00 pm 02/08/2011 20:00 pm
En este ejemplo solo quiero conocer las horas transcurridas de ese mismo día...
Ojala puedas ayudarme.. Gracias!!
Te recomiendo la lectura del artículo http://www.excelavanzado.com/2012/10/sumar-horas-en-excel.html del blog Excel Avanzado. Un saludo
Eliminarola tengo un problema mediante un formulario de vb en ecxel 2010 ingreso la fecha ej 12/02/2013 pero al pasarmela ala celda me la cambia por 02/12/2013 y la interpreta como 2 de dic del 2013 ,ya intente cambiando formatos de celda pero no funciona, habra algun codigo que al pasar ala celda me respete el formato dd/mm/yy....Saludos gracias
ResponderEliminarLa solución debes aplicarla en la programación del formulario de VB y no en la celda.
EliminarHola,
ResponderEliminartengo unos campos tipo fecha que me muestran el dia de la semana en español, hay alguna forma de hacer que se muestren en ingles?
Por ejemplo:
El valor del campo es "11/02/2013 10:00:00", que se muestra como "lun 11/02 10:00", yo quiero que me muestre "mon 11/02 10:00", como puedo hacerlo sin tener que utilizar funciones?
Saludos y gracias
Hola Kiko, soy Sebastian, la verdad que intente de varias formas de hacerlo y utilizando tus formulas, solucione el problema de una manera muy extraña, utilice parte de la formula de texto y aca viene lo extraño..., me dio parte del resultado, modificando los valores me quedo asi.
ResponderEliminar=TEXT(C3;"mm/dd/e") o en español =texto(c3;"mm/dd/e), de esta forma una fecha que la tenia con estilo americano, se paso a español. Por favor proba y vas a ver que funciona.
Eso si cuadno llegas a las fechas que poseen dias mayores a 10, ya no sirbe, pero como yo lo tengo ordenado por fecha, cambio la formula en ese punto, arrastro nuevamnete y listo!...
Hola Kiko,
ResponderEliminarllevo un tiempo buscando solución a un problema que parece ridiculo de sencillo pero sin resultado (y eso que he llamado al servicio de microsoft que me enviaron un enlace a un articulo de "How to"que no me sirvio para nada.
En fin eso es lo que pasa: sin inserto una fecha usando el sistema europeo (por ejemplo pongo 24/5 para decir 24 de mayo) no me lo interpreta como fecha... almenos que la cifra de dia sea inferior a 12 y entonces me lo interpreta como mes.
He intentado cambarlo desde los formatos de fecha (cambiando sistema, eligiendo formatos personalizados de dd-mm-yy) pero no hay manera: si no inserto la fecha en un formato americano (mm-dd) ya no lo considera como fecha. Tambien he entrado en las preferencias del sistema pero no he conseguido nada ahi tampoco.
el verdadero problema no es como se vea la fecha una vez puesta sino que lo que pongo (en formato dd/mm)sea interpretado como fecha....
agradezco tu ayuda
Mounya
Hola Mounya. Pues la verdad es que no sé por qué no te funciona, ya que estoy probando en mi ordenador y escribo en mi hoja 25/5 y automáticamente me lo transforma a formato fecha 25-may (dd-mmm). Lo que parece claro es que en la configuración regional la tienes con el sistema americano y deberías cambiarla ¿tienes cargado el paquete de preferencias de idioma?
EliminarPues la verdad es que no lo se... cuando voy a formato de fecha y ubicacion cambio la ubicacion a españa pero se ve que por defecto est Estados Unidos y no he encontrado en las preferencias de excel una manera de cambiar esto...
ResponderEliminarHola otra vez! tenias razon, era la configuración de idioma de mi Mac, lo tenia en ingles y el formato de fecha de mi calendario se ajustaba a eso.Al cambiar "dates and time preference" a español y españa ya me solucionó el tema de escel :)
Eliminarmuchas gracias!
Mounya
Hola tengo un problema, tengo unos datos de un reporte que registra la fecha de esta forma:
ResponderEliminar20/07/2014 9:09:56PM
pero no la reconoce como fecha sino con un formato personalizado y yo necesito que excel la reconozca como fecha.. que puedo hacer?
Lo reconoce como formato personalizado porque no existe un formato de fecha "estándar" definido como dd/mm/aaaa h:mm:ss AM/PM
EliminarSi te refieres a que lo reconozca como valor y no como texto entonces tienes que seleccionar las celdas donde tienes dichos registros y en formato de número personalizado escribir precisamente: dd/mm/aaaa h:mm:ss AM/PM
Buenas todos, estoy desesperada he probado casi todas las soluiones que dais pero la única que me funciona es: texto("mm,dd,aa") el problema como en el comentario que copio mas abajo, es cuando el día es mayor de 10 me deja de funcionar...por qué sucede eso???
ResponderEliminarHola Kiko, soy Sebastian, la verdad que intente de varias formas de hacerlo y utilizando tus formulas, solucione el problema de una manera muy extraña, utilice parte de la formula de texto y aca viene lo extraño..., me dio parte del resultado, modificando los valores me quedo asi.
=TEXT(C3;"mm/dd/e") o en español =texto(c3;"mm/dd/e), de esta forma una fecha que la tenia con estilo americano, se paso a español. Por favor proba y vas a ver que funciona.
Eso si cuadno llegas a las fechas que poseen dias mayores a 10, ya no sirbe, pero como yo lo tengo ordenado por fecha, cambio la formula en ese punto, arrastro nuevamnete y listo!...
Muy bien, me ha funcionado a la perfección.
ResponderEliminar