Convertir une variété d'entrées de texte à ce jour dans Excel
J'ai essayé un certain nombre de solutions suggérées sur StackExchange pour convertir du texte à ce jour dans Excel, mais aucune d'entre elles ne fonctionne de manière fiable pour les données que j'ai, veuillez voir ci-dessous pour une explication.
J'ai une colonne avec une «date de début». Il contient la date sous les formes suivantes:
(État de départ) 17/09/2019 09:30:59 20/10/2020 16:45:59 26/02/2020 12:20:59 PM
Cette image montre les données et l'état final souhaité ...
Je veux convertir l'entrée entière en une valeur de date Excel afin que je puisse faire des calculs utiles dessus, par exemple soustraire une heure de début le 20/11/2020 par exemple d'une heure de fin le 21/11/2020, donc par exemple je veux la seconde l'entrée ci-dessus pour finir comme (de préférence) 23/02/2020 20:31:59 (parce que je suis britannique), mais même si je peux l'obtenir à la version américaine, ça va, je peux travailler avec ça.
(État final souhaité) 17/09/2019 09:30 20/10/2020 16:45 26/02/2020 12:20
Veuillez ne pas me diriger vers d'autres réponses qui semblent résoudre ce problème. J'ai essayé de les utiliser. Chacun semble avoir un problème, et je ne vois pas comment le résoudre, sinon je n'aurais pas publié cette question.
Par exemple, si j'essaye, =datevalue(left(a1,9))il n'obtient pas d'entrées avec un mois à deux chiffres (c'est-à-dire 10, 11, 12).
Si j'essaye la =date(mid.... etcmême chose: il ne saisit pas toutes les informations de manière fiable car les différentes cellules ont des longueurs d'informations différentes.
J'ai utilisé la méthode Text-To-Columns , qui le divise en cellules au format non date / heure et coupe le PM ou le AM dans une autre colonne que je peux supprimer, MAIS cela signifie qu'au lieu de 20:31:59 je reçois 08:31:59 dans une colonne et PM dans une autre, et je ne vois pas comment obtenir que cela renvoie 20:31:59.
Si quelque chose dans cette question n'est pas clair, veuillez demander.
Et si j'ai raté quelque chose de vraiment évident, je m'excuse, mais j'ai passé des heures à essayer de résoudre ce problème.
Réponses
Mon premier essai serait d'essayer de faire correspondre un format de date personnalisé sur la cellule de texte. Si cela fonctionne, le texte de la cellule sera indenté d'un espace et vous pourrez dire = A1 + 0 pour ajouter zéro jour à la date sans générer d'erreur. Puis reformatez cette nouvelle cellule selon votre goût.
Dans le pire des cas, vous pouvez découper la chaîne dans un format de date natif qui comprend parfaitement. Vous n'avez pas besoin de longueurs fixes, mais vous pouvez utiliser l'espace et les caractères / comme délimiteurs, par exemple pour obtenir le jour entre les deux /
=mid(A1,search("/",A1)+1,search("/",A1,search("/",A1)+1)-1-search("/",A1))
Ce qui signifie sémantiquement
=mid(A1, from first "/" position +1, with length of second "/" position -1 - first "/" position)
Donnez ou prenez un +1 ou -1. Je ne suis pas devant mon ordinateur maintenant pour tester la formule. Mais s'il renvoie par exemple un caractère à petit, ajoutez simplement un +1 à l'argument de longueur. Une fois que vous avez isolé le jour, le mois et l'année (et l'heure si nécessaire), vous pouvez reconstruire une date avec la fonction de date.
Ok, donc je pense que je pourrais avoir une solution appropriée pour vous. Il semblerait que la manière dont Excel convertit le texte en date dépende de vos paramètres de région Windows. La définition de la mise en forme de la date de la cellule dans Excel ne change que la façon dont les valeurs de date sont affichées , pas la façon dont elles sont converties en premier lieu.
Mes paramètres de région dans Windows sont actuellement définis sur l'Afrique du Sud. Ma feuille de calcul résout ceci, avec la formule adjacente = A1 + 0:
Maintenant, si je me dirige vers le panneau de configuration Windows > Changer les formats de date, d'heure ou de nombre et que je change temporairement le format en anglais (États-Unis), puis reviens à la feuille de calcul et réévalue la formule (appuyez sur ctrl + alt + F9 si elle ne se met pas à jour automatiquement):
Maintenant, copiez et collez en tant que valeurs la nouvelle colonne de valeur de date au-dessus d'elle-même. Enregistrez le fichier. Modifiez les paramètres de votre région tels qu'ils étaient. Sélectionnez la colonne et enregistrez le format de cellule de date et d'heure que vous aimez sous "personnalisé":
Pour que:
