Excel 30 96

+

Excel Trucs et astuces Combien de fois avez-vous regardé votre montre et tapé l'heure actuelle dans votre feuille de calcul? Ou tenté de convertir d'un fuseau horaire à un autre et produit une erreur? Ou se demande pourquoi un temps montre par ressembler à "0,9285"? Heureusement, ces questions sont faciles à répondre une fois que vous comprenez comment les formules de temps Excel fonctionnent. Cet article: Explique quelques formules de temps de base tels que NOW () et TIME (), Montre comment additionner et soustraire fois, et Utilise ces deux classeurs échantillons: un calculateur de feuille et un convertisseur de fuseau horaire. Le NOW () Fonction NOW () renvoie la date et l'heure de votre ordinateur. Vous pouvez reformater le résultat par un clic droit sur la cellule, choisir "Format de cellule", et de choisir un format de temps différent. Si vous voulez seulement le temps et non la date, vous pouvez formater comme un temps et prétendre la date n'existe pas, ou vous pouvez utiliser "= NOW () - AUJOURD'HUI ()", qui soustrait la partie date et les feuilles seule la portion de temps. Peu importe comment vous formater l'affichage, cependant, la valeur sous-jacente reste constante jusqu'à ce que vous recalculer. Chaque fois que vous recalculer (ou chaque fois que Excel recalcule automatiquement parce que d'autres cellules ont changé), la fonction tire le temps à partir de ce moment. Utilisez cette fonction lorsque vous avez besoin d'une heure toujours mis à jour dans vos calculs. NOTE: Quelqu'un qui ouvre la feuille de calcul en six mois verra sa date et l'heure actuelle dans ce domaine, pas la date et l'heure que vous avez enregistré le classeur. Le raccourci Ctrl-Maj-; Lorsque vous avez besoin d'enregistrer un enregistrement permanent de l'heure actuelle, maintenez la touche Ctrl enfoncée et la touche Maj enfoncée pendant que vous tapez un point-virgule (;). Comme NOW (), il retourne l'heure actuelle du système. Contrairement à NOW (), cependant, il tamponne le temps comme une valeur fixe. Vous pouvez changer le format, mais la valeur est toujours le même. De même, vous pouvez enregistrer la date actuelle en maintenant la touche Ctrl enfoncée (mais pas la touche Maj) et en tapant un point-virgule. Parce qu'il enregistre la valeur et non une fonction actualisable, quelqu'un qui ouvre la feuille de calcul en six mois verra la date et l'heure que vous tamponné sur la page, et non pas sa propre date et l'heure. La fonction TIME () Tant la fonction NOW () et les touches Ctrl + Maj +; raccourci retourner l'heure du système actuel. Que faire si vous avez besoin de stocker une valeur pour une autre fois? Lorsque vous avez besoin de convertir un certain nombre d'heures, minutes et secondes à un temps Excel, utilisez la fonction TIME () avec les paramètres les nombres d'heures, minutes et secondes. Taper "= TIME (14, 30, 10)" produit: Assurez-vous d'entrer les heures en utilisant une horloge de 24 heures. Ajout et Soustraire fois Lorsque vous avez besoin d'ajouter ou de soustraire un nombre fixe d'heures, minutes ou secondes pour une valeur de temps, il suffit d'ajouter ou de soustraire la façon dont vous le feriez pour tout décimaux. Si vous ajoutez 4 heures à 11h30, vous obtenez 15h30: Pour comprendre l'arithmétique de temps plus pleinement, vous devez d'abord comprendre comment stocke les temps de calcul Excel. Essayez ceci: dans une cellule vide, tapez "= NOW ()" et appuyez sur. Le résultat indique la date et l'heure actuelle, probablement formaté comme ceci: Ensuite, cliquez-droit sur la cellule, choisissez "Format de cellule" et choisissez Le résultat devrait ressembler à ceci "Général.": Voici comment les magasins Excel Date / fois - la date est le nombre à gauche de la virgule, et le temps est de la partie décimale du nombre, où 0 correspond à 00:00, 1 est minuit, et tout autre moment de la jour se situe quelque part entre les deux. Les temps sont des valeurs décimales entre 0 et 1. Pour convertir un temps de sa valeur décimale, diviser les heures (en utilisant l'horloge de 24 heures) par 24 (le nombre d'heures dans une journée complète): 12:00:00 (Noon) est exactement à mi-chemin à travers la journée, de sorte que la valeur décimale est de 1/2 = 0,5. 06:00:00 est un quart de la journée, de sorte que la valeur décimale est de 1/4 = 0,25. 21:00:00 correspond à 21:00:00, de sorte que la valeur décimale est 21/24 = 0,875. D'autre part, pour convertir une valeur décimale de temps pour le nombre d'heures, il faut multiplier par 24. Parce que les temps sont tout simplement des nombres compris entre 0 et 1, vous pouvez faire toute l'arithmétique des temps, comme vous le feriez avec d'autres nombres décimaux. Ajoutez-les, les soustraire, multiplier par deux, les diviser par quatre comme tous les autres nombres décimaux. Mais il y a quelques avertissements que vous entrez dans la date arithmétique. Pourquoi le 15 heures + 18 heures = 9 heures? Essayez ceci, en utilisant un format de 24 heures: Faites un clic droit sur les cellules A1 à A3, choisissez "Format de cellule", et sélectionnez "Général". Cela montre que Excel a fait, en effet, ajouter les deux valeurs de temps, ce qui a entraîné un nombre supérieur à un, ce qui signifie plus d'une journée complète. Comme un odomètre, cependant, les formats de temps standard d'Excel rouler et ne montrent que la partie-0,375 décimal, ce qui équivaut à 09:00:00. Lorsque vous avez besoin de montrer des heures supérieures à 24, comme lors de l'ajout des heures à partir d'une feuille de temps, choisissez un format de temps qui affiche les plus grands nombres: Lorsque vous travaillez avec des formats de clients, entourer les heures avec les supports de crochets ([]): En appliquant ce format à cet exemple produit 33 heures, comme prévu: Pourquoi 4 heures - 12 heures Produire une erreur? Cette situation se produit lorsque vous calculez combien de temps à l'avance, vous avez besoin pour commencer une tâche de sorte qu'il sera complété par le temps cible. Il se pose également dans certaines conversions de fuseau horaire qui se croisent dans la journée précédente, tels que le calcul du temps de Londres quand il est 05h00 à Tokyo. (Voir le convertisseur de fuseau horaire ci-dessous et l'erreur de piégeage utilisée dans le calcul.) Malheureusement, Excel n'aime pas les temps négatifs, vous aurez donc besoin d'introduire une formule de piégeage d'erreurs. Chaque fois que les temps de soustraction pourrait entraîner un temps négatif, l'envelopper dans un IF () déclaration qui ajoute 1 au résultat. (Dans les cas qui pourraient aboutir à quelque chose avant un jour de retour, vous aurez besoin d'ajuster votre formule encore plus loin.) Enfin, vous êtes prêt pour les applications utiles de ces formules. Calculatrice Timesheet Le calculateur de feuille jointe utilise la soustraction de temps simple pour déterminer le nombre d'heures travaillées dans chaque segment. Temps - temps dans le format pour les heures travaillées est «[h]: mm," qui ignore secondes et ne pas afficher AM ou PM. Les pièges de calcul des nombres négatifs en utilisant un if (): si le temps est plus grand que le temps, il affiche le message, "Time out doit être postérieure à temps." Ensuite, le calculateur ajoute la durée de chaque segment dans chaque jour pour atteindre un total de tous les jours. Encore une fois, cela est simple arithmétique de temps: "= E8 + E9". Les sommes totales hebdomadaires Les totaux quotidiens et utilise un format de «[h]: mm." Les crochets autour des heures permettent cette formule pour afficher des valeurs supérieures à 24 heures. Enfin, la formule des heures supplémentaires détermine si le nombre d'heures hebdomadaires est supérieur à 40. Rappelez-vous que 40 doit d'abord être converti en une fraction de jour en divisant par 24, de sorte que la formule compare les heures travaillées à 40/24. Si une plus grande, alors la formule des heures supplémentaires soustrait 40/24 des heures travaillées et renvoie la différence. Si moins, la formule des heures supplémentaires retourne 0:00. Time Zone Converter Dans le convertisseur de fuseau horaire fixé entre un temps de départ et le fuseau horaire dans les colonnes A et B (dans les cellules d'or). Colonne C convertit l'heure locale à l'heure de Greenwich, également connu sous le nom (temps universel coordonné) UTC, en soustrayant l'écart du GMT. Rappelez-vous que l'écart (en heures) doit être convertie en un nombre décimal premier en divisant par 24. Parce que cela pourrait entraîner un temps négatif si l'heure locale est loin à l'est et est tôt le matin, la formule utilise un () instruction IF , en ajoutant un 1 si le résultat serait négatif. Dans les colonnes D, F et H, entrent cibler des fuseaux horaires de la conversion. Colonnes E, G, et je calculer les temps de cibles en commençant par la base de GMT et en ajoutant l'écart par rapport à GMT. Tous ces calculs utilisent des données de la table sur l'onglet "Zone de données en temps". Sentez-vous libre d'ajouter des enregistrements, pour cette table est loin d'y compris tous les fuseaux horaires dans le monde entier. Notez que vous pouvez définir la abréviation de fuseau horaire dans la colonne A en utilisant un format que vous souhaitez. L'échantillon de données inclus montre quelques-unes avec des abréviations (GMT, HNE, PDT, etc.) et d'autres à l'aide de villes. Quelles que soient les valeurs que vous entrez ici apparaîtra dans la liste de sélection pour entrer des données dans les colonnes B, D, F et H du convertisseur. Prochaines étapes Ceci est juste le début du calcul de formules utilisant des temps. De ce point, vous pouvez développer des formules beaucoup plus complexes, telles que la détermination du temps moyen nécessaire pour effectuer une tâche, le temps maximum entre les appels téléphoniques, etc. Tant que vous vous souvenez que les temps sont juste des nombres décimaux qui représentent des parties d'un jour , vous pouvez travailler à travers tous les calculs restants. Vous avez les outils, et vous savez comment les utiliser. Prendre plaisir! Écrit par Christy Perry Christy Perry est un rédacteur technique basée à Minneapolis et éducateur. Un ancien dirigeant de produit chez Nationwide Insurance, elle utilise ses plus de vingt ans d'expérience dans la gestion des produits d'assurance et de services actuariels pour aider les particuliers et les organisations entrepreneuriales avec leurs besoins de geek. 8 Commentaires Walter Chin dit: Je voudrais voir s'il y a une fonction pour Excel pour fonctionner en continu une horloge. Une colonne a une horloge de jeu on a le numéro d'intervalle de 10-20-30- à plusieurs heures. Annette Morgan dit: Comment puis-je créer une fonction Si pour soustraire 15 minutes à partir du moment si la valeur est = 00:15 et ne rien faire si la valeur de E est midi 00-à-00:14. Je dois créer une conversion de 3 à 8 minutes à 0,1, 9 à 14 minutes pour .2, 15 à 20 minutes à 0,3, 21 à 26 minutes pour 0,4, 27 à 32 minutes à 0,5, 33 à 38 minutes à 0,6, 39 à 44 minutes à .7, 45 à 50 minutes à .8, 51 minutes à 56 minutes à .9, et 57 minutes à 60 minutes à 1,0 Adam White dit: La question que je vais avoir est que j'ai les travailleurs de quarts 2e qui travaillent à partir de 15 heures le lendemain matin 2 heures et cette formule ne fonctionnera pas + IF ((((D11-C11) + (F11-E11)) * 24 = 8 , 8, ((D11-C11) + (F11-E11)) * 24) maintenant qu'est-ce que je dois faire pour permettre excel de reconnaître une formule pour lui permettre de venir à laisse dire 10,5 heures de start3pm stop9pm start9: arrêt de 30pm 2 heures, il est allways bien jusqu'à juste après le 11h59 heure où la nouvelle journée de 24 heures commence. Comment puis-je calculer les heures supplémentaires lorsque le taux ne se déclenche après 5 heures de Ot