Convierta una variedad de entradas de texto a la fecha en Excel
Probé varias de las soluciones sugeridas en StackExchange para convertir texto hasta la fecha en Excel, sin embargo, ninguna de ellas funciona de manera confiable para los datos que tengo; consulte la explicación a continuación.
Tengo una columna con una "fecha de inicio". Contiene la fecha en las siguientes formas:
(Estado de inicio) 17/9/2019 9:30:59 AM 20/10/2020 4:45:59 PM 26/02/2020 12:20:59 PM
Esta imagen muestra datos y el estado final deseado ...
Quiero convertir la entrada completa en un valor de fecha de Excel para poder hacer cálculos útiles sobre ella, por ejemplo, restar una hora de inicio el 20/11/2020 de una hora de finalización el 21/11/2020, por ejemplo, quiero la segunda la entrada anterior para terminar como (preferiblemente) 23/02/2020 20:31:59 (porque soy británico), pero incluso si puedo obtener la versión de EE. UU., está bien, puedo trabajar con eso.
(Estado final deseado) 17/09/2019 09:30 20/10/2020 16:45 26/02/2020 12:20
Por favor, no me señale otras respuestas que parezcan resolver esto. He intentado usarlos. Cada uno parece tener un problema y no veo cómo resolverlo, o no habría publicado esta pregunta.
por ejemplo, si lo intento =datevalue(left(a1,9)), no obtiene entradas con un mes de dos cifras (es decir, 10, 11, 12).
Si intento lo =date(mid.... etcmismo: no captura toda la información de manera confiable porque las diferentes celdas tienen diferentes longitudes de información.
He usado el método Text-To-Columns , que lo divide en celdas sin formato de fecha / hora y corta el PM o AM en otra columna que puedo descartar, PERO esto significa que en lugar de 20:31:59 obtengo 08:31:59 en una columna y PM en otra, y no veo cómo hacer que esto devuelva 20:31:59.
Si algo en esta pregunta no está claro, pregunte.
Y si me he perdido algo realmente obvio, me disculpo, pero he pasado horas intentando solucionarlo.
Respuestas
Mi primer intento sería intentar hacer coincidir un formato de fecha personalizado en la celda de texto. Si funciona, el texto de la celda se sangrará por un espacio y podrá decir = A1 + 0 para agregar cero días a la fecha sin arrojar un error. Luego reformatee esa nueva celda según su gusto.
En el peor de los casos, puede dividir la cadena en un formato de fecha nativo que Excel comprenda. No necesita longitudes fijas, pero puede usar el espacio y los caracteres / como delimitadores, por ejemplo, para obtener el día entre las dos /
=mid(A1,search("/",A1)+1,search("/",A1,search("/",A1)+1)-1-search("/",A1))
Que semánticamente significa
=mid(A1, from first "/" position +1, with length of second "/" position -1 - first "/" position)
Dar o recibir un +1 o -1. No estoy frente a mi computadora ahora para probar la fórmula. Pero si devuelve, por ejemplo, un carácter a poco, simplemente agregue un +1 al argumento de longitud. Una vez que haya aislado el día, el mes y el año (y la hora si es necesario), puede reconstruir una fecha con la función de fecha.
Ok, creo que podría tener una solución adecuada para ti. Parecería que la forma en que Excel convierte el texto a la fecha depende de la configuración de la región de Windows. Establecer el formato de fecha de la celda en Excel solo cambia cómo se muestran los valores de fecha , no cómo se convierte en primer lugar.
La configuración de mi región en Windows está actualmente establecida en Sudáfrica. Mi hoja de cálculo resuelve esto, con la fórmula adyacente = A1 + 0:
Ahora, si me dirijo al panel de control de Windows > Cambiar formatos de fecha, hora o número y cambio temporalmente el formato a inglés (Estados Unidos), luego regrese a la hoja de cálculo y vuelva a evaluar la fórmula (presione ctrl + alt + F9 si no se actualiza automáticamente):
Ahora, copie y pegue como valores la nueva columna de valor de fecha encima de sí misma. Guarda el archivo. Cambie la configuración de su región como estaba. Seleccione la columna y archive el formato de celda de fecha y hora que desee en "personalizado":
Así que eso:
