Formulaires et fonctions Excel Partie 7 : Fonctions de date et d'heure

1. Introduction à les fonctions de date et de l'heure: D' accord. Bienvenue, tout le monde. C' est la Section 7. Fonctions de date et d'heure. Une de mes sections préférées remet l'une des sections les plus importantes de ce cours de formation, simplement en raison du fait que beaucoup de gens rencontrent un certain nombre de problèmes et de maux de tête essayant de travailler avec les dates et les heures dans Excel. Je sais que j'ai passé environ un an à fouetter ces gens qui ne comprenaient pas tout à fait ce qui se passait . Jusqu' à ce que je me sois vraiment assis pour comprendre comment Excel pense aux dates et comment excelle lesdates de traitement. Jusqu' à ce que je me sois vraiment assis pour comprendre comment Excel pense aux dates et comment excelle les Et une fois que j'ai eu cette compréhension, c'est en fait assez simple, et cela a rendu tout plus facile quand il s'agissait de travailler avec les dates et les heures. Donc, nous allons commencer par une section de date Time 101 ou intro. Nous allons parler de la valeur de la date, qui est un peu la pièce clé ici sur la raison pour laquelle il va être votre nouveau meilleur ami quand il s'agit de travailler avec les dates et les heures et exceller la mise en forme de la date de conversation, puis l'option de remplissage Siri, qui est un joli petit raccourci pour créer des dates séquentielles que nous allons aujourd'hui et maintenant , qui sont des fonctions volatiles qui retournent la date actuelle et l'heure actuelle exacte nous allons parler année, mois, jour, heure, minute et seconde, qui conviennent des moyens de catégoriser vos données de date. Nous parlerons de fin de mois et de votre frack en semaine jours de réseau. Ces conseils pour montrer combien de jours de travail tombent entre deux dates ? Quelle date suit un certain nombre de jours de travail à partir d'aujourd'hui, ainsi de suite et ainsi de suite ? Et puis nous finirons avec la date diff, qui est un autre outil pour calculer les différences entre deux dates et ensuite terminer avec la , euh, une démo très utile, où je vais vous montrer comment créer votre propre outil de rythme budgétaire, que vous pouvez personnaliser et transformer en tout ce que vous voulez. Deux fichiers à télécharger ont obtenu notre Excel pour les analystes fonctions date heure. PDF et sa section sept fonctions de date Temps Excel Fichier, qui va marcher à travers que nous nous déplacons à travers les conférences. Alors allez-y et téléchargez ces deux fichiers. Vous pouvez les imprimer si vous choisissez et, euh, plongons directement dans 2. DATEVALUE : Votre nouveau BFF: D' accord. Donc, abord et avant tout, je veux parler de quelque chose appelé la valeur de date. Et s'il y a une conférence à écouter ou une diapositive que vous allez lire dans cette section entière, c'est celle à laquelle il faut prêter attention. Et c'est parce que la valeur de date est la pièce critique qui permet Excel de traiter les dates et les heures afin de chaque date. En fait, chaque moment dans Excel a un ID associé sous-jacent à une valeur numérique de date. C' est ainsi qu'excelle, capable de faire des choses comme calculer le passage du temps ou appliquer des fonctions statistiques à dates sans ces valeurs de date, les dates seraient simplement traitées comme des champs de texte. Vous ne seriez pas capable de faire grand-chose. Ah, en termes de manipulation avec ces dates. Donc minuit le 1er janvier 1900 est traité comme, je suppose, le début des temps dans l'univers excels. Donc, le 1er janvier 1900 a une valeur de date sous-jacente d'un, puis chaque jour suivant au-delà du 1er janvier 1900 a une valeur de date d'un nombre plus élevé, donc le 2 janvier 1900 a une valeur de date de deux et ainsi de suite et ainsi de suite. Donc Excel est vraiment, vraiment intelligent sur les dates sur, puis il reconnaît la plupart des dates de type et applique automatiquement un format de date commun. Dans la plupart des cas, il s'agit d'une barre oblique du jour du mois, avec quatre chiffres ainsi que sa valeur de date associée. Maintenant, la façon de vérifier si votre entrée est reconnue comme une date est de faire un clic droit pour vendre , formater et passer en général, et il vous montrera s'il y a une valeur de date sous-jacente. Je vais vous montrer un exemple de ça maintenant. Si je saute dans un cahier vide, je vais taper un rencard à la main. Je vais faire le 1er janvier 2015 percenter et avoir un clic droit sur celui qui va formater les cellules . Comme vous pouvez le voir, il est par défaut le champ de date. Comme vous pouvez le voir, Il s'agit de la version par défaut de l'année du mois Dave. Mais si je passe à la catégorie générale, vous pouvez voir qu'il a une valeur sous-jacente de 42,000 et cinq parce que c'est le jour qui est tombé 42,005 jours après le 1er Janvier 1900 s. Donc cela me dit que ok, Excel a a reconnu mon entrée comme une date qui peut le traiter comme telle. C' est aussi assez bon sur la compréhension, un peu étrangement formaté. Donc, je pourrais dire comme, Oh un tiret Owen, Dash 15 et vous pouvez voir qu'il l'a commuté à la version par défaut parce qu'il savait que j' essayais de taper une date. Tu peux vraiment devenir un peu bizarre avec ça. Tu sais, j'aime faire un test pour voir à quel point je peux être étrange. Voir même cette version où je l'ai tapée. Comme vous le savez, tiret et combinaison de tirets et de barres obliques, il l'a toujours reconnu comme une date, et il lui a toujours appliqué cette valeur de date. Donc Excel est en fait assez intelligent quand il s'agit de ces choses sur. Et c'est une sorte de la raison principale pourquoi sont capables de faire de telles manipulations intéressantes et des calculs contre les états eso si vous arrivez à type de date. Cela, sauf ne reconnaît pas un exemple commun que j'ai vu est si vous tapez une série de nombres comme date mois jour. Donc, pour le 1er janvier 2015 à nouveau si vous avez tapé 2015 0101 sans tirets ou barres obliques. Excel ne reconnaîtrait pas cela comme une date, et donc il ne s'appliquerait pas sous la valeur de date sous-jacente, et il le traiterait comme une chaîne de texte ou, dans ce cas, valeur. Donc, si c'est le cas comme si je télécharge des données et que le champ de date est dans un format très étrange comme ça, je vais vous montrer comment utiliser des outils comme le valuer de date, valeur de temps pour convertir ces dates non formatées dans deux formats que Excel comprend. Donc, en regardant quelques exemples ici encore, le 1er janvier 1900 est la date un, donc la valeur de la date est égale à un 10 jours plus tard. Le 11 en 1900 aurait une valeur de date de 11. Le 6 février est 42 041 jours après le 1er janvier 1900 et ces valeurs de date ne doivent pas nécessairement être nombres entiers. En fait, 42 0 41,5 équivaut à midi le 6 février, alors que 42 0 41,75 équivaut à 18 heures, soit 75 % de la journée. Ainsi, vous pouvez obtenir très granulaire même jusqu'à la seconde sur. C' est ainsi que vous pouvez commencer à travailler avec des moments très particuliers dans Excel, pas seulement tenir des dates, donc j'espère que cela aide Une fois que vous comprenez ce concept de valeur de date, tout devient soudainement un peu plus clair. Nous commençons à parler de réellement manipuler et appliquer des fonctions à ces dates. 3. Série de formage et de remplissage de dates: donc je veux juste couvrir deux autres conseils et concepts avant de plonger dans les fonctions elles-mêmes. Le premier est la mise en forme de date, donc lorsque vous formater des dates et Excel, vous avez quelques options. Vous pouvez sélectionner une option prédéfinie dans la catégorie de date dans la boîte de dialogue de format Cellules , laquelle vous pouvez accéder en cliquant avec le bouton droit de la souris sur une cellule en choisissant la mise en forme sud. Ou vous avez la possibilité de créer votre propre format personnalisé. Ainsi, les formats prédéfinis vivent à nouveau dans la ligne de date ou la catégorie de date de la boîte de dialogue Format des cellules . Et il y a un certain nombre d'options. Excusez-moi. C' est un peu difficile à lire, mais vous pouvez faire défiler vers le bas et vous pouvez afficher votre date avec un certain nombre de variations différentes . Vous pouvez montrer juste le jour et le mois, pas l'année. Vous pouvez le montrer comme une année à deux chiffres contre quatre. Vous pouvez écrire le jour et laisser l'année comme un nombre, comme il y a des dizaines et des dizaines de façons de le montrer et a probablement ce dont vous avez besoin. L' autre option est de sauter à la catégorie personnalisée et écrire vos propres formats ici, Donc ici les codes que vous pouvez utiliser. Ce n'est qu'un échantillon d'entre eux. La liste complète est disponible au support dot office dot com, mais cela vous permet juste d'afficher une date d'autres façons supplémentaires. Donc, par exemple, si j'ai une date dans une cellule comme le 1er janvier 2015 ah, et au lieu de montrer la date entière, je veux juste montrer quel jour de la semaine c'est. Je pourrais formater cette cellule avec un format personnalisé de de de, et il affichera la valeur comme samedi ou vendredi ou mercredi. Je ne sais pas si le haut de ma tête quel jour de la semaine c'était. Mais, hum, important de noter que cela ne changera pas la valeur sous-jacente elle-même. Cela ne changera que la façon dont vous l'affichez. La deuxième chose que je veux vous montrer est de Phil Siri, qui est un excellent raccourci qu'Excel utilise pour vous aider à remplir une série de dates sans avoir orteil taper manuellement des jours ou des mois ou des années séquentiels. Donc, disons que j'ai écrit 11 2015 et que j'en vende un ici. Excel l'a reconnu comme une date. Si je prends le coin inférieur droit de cette cellule et que je le fais glisser vers le bas vers un cinq par défaut. Excel va supposer que je veux le montrer comme rester sur la tendance de la journée. Donc, fondamentalement, en ajoutant un jour pour chaque ligne. Si je dis non, je ne veux pas faire ça. Je veux juste une copie. 11 2015 et chaque cellule que je peux cliquer sur ce petit bouton qui apparaît après que vous avez glissé les valeurs et je peux vérifier copie vend. Vous verrez qu'il est par défaut pour remplir sérieux, ce qui suppose que je veux jour après, jour après, jour après jour, et puis j'ai d'autres options. Voici Eh bien, je ne peux remplir que le formatage de Sally un. Je pourrais le remplir sans le formatage ou si je voulais montrer la séquence à un niveau autre que le jour. J' ai aussi ces options, donc je peux dire Ok, plutôt que d'aller par incréments de jours, montrons-le par incréments de mois, auquel cas ce serait 1121314151 ou années, auquel cas il serait 11 2015 2016 17 18 19 ou même jours de semaine donc je peux dire augmenter la date d'un jour. Purcell, mais exclut les week-ends. C' est donc une façon vraiment cool de créer des fenêtres de date mobiles ou d'automatiser une grande partie du processus lorsque vous travaillez avec des ensembles de données axés sur la date qui sont en constante évolution. Alors gardez ces deux trucs à l'esprit, nous allons les utiliser un peu dans certains des exercices à venir. 4. AUJOURde JOURD'HUI ()/MOEL(): très bien, les premières fonctions de date et d'heure que je veux partager avec vous, notre aujourd'hui et maintenant qui sont conçus pour simplement retourner la date actuelle ou l' heure exacte actuelle . Et la bonne nouvelle est que ces air peut-être deux des fonctions les plus simples dans Excel et ils sont si simples parce qu'ils ne font même pas référence à d'autres cellules. Vous ouvrez et fermez vos parenthèses. Ne mettez même rien à l'intérieur. Appelez ça un jour et vous êtes prêt à y aller. Il est important de noter que ce sont des fonctions volatiles,ce qui signifie qu'elles seront mises à jour chaque fois que vous apporterez une modification de feuille de calcul dans un classeur ouvert. Il est important de noter que ce sont des fonctions volatiles, Donc, il y aura constamment des changements. Allons dans Excel et, euh, allez-y et ouvrons la section sept fonctions Date heure. Je vais faire une promenade rapide à travers, comme vous pouvez le voir dans le premier onglet fonctions diurnes. J' ai un espace réservé pour la date actuelle et l'heure actuelle, que nous allons remplir dans une minute en utilisant aujourd'hui et maintenant nous avons aussi une date d'échéance du projet hypothétique , que nous aurons plus tard. Et puis c'est essentiellement là que nous allons pratiquer toutes les fonctions que nous avons apprises dans les conférences à venir. Le deuxième onglet est un outil de rythme budgétaire vide. C' est la démo que nous allons parcourir à la fin de la section où nous allons travailler ensemble et utiliser les fonctions de date et d'heure pour construire un outil de rythme budgétaire personnalisé . Et puis dernier mais pas le moindre sur le troisième onglet appelé exemples de données. On a juste un Siris de dates et de dépenses et ces airs vont alimenter l' outil de rythme budgétaire que nous construisons. Donc sauter à l'heure de date fonctionne sur Tom un. Remplissons simplement ces aujourd'hui et fonctionne maintenant dans la cellule C deux et C trois. Donc, pour la date actuelle, je vais juste taper égal aujourd'hui. Ouvrez, fermez. Entrez ESO renvoie la date actuelle, qui est le 18 août 2015 Heure actuelle. Même processus est égal à Maintenant ouvrir fermer appuyez sur Entrée. Et comme vous pouvez le voir, ils retournent la même chose sauf l'heure actuelle ou la fonction maintenant est juste un peu plus granulaire qui introduira les composants heure, minute et seconde que la date actuelle ou la fonction d'aujourd'hui ne sera pas. Donc là, vous allez extrêmement simple, mais ce sera notre point de départ pour pratiquer les fonctions dans les prochaines conférences 5. ANNÉE/MOIS /JOUR et HEURE /JOUR, MOIS et heure/MINUTE: afin qu'Excel calcule toujours les dates et les heures en fonction de leurs valeurs céréalières sous-jacentes précises ou des valeurs de date. Mais que faire si vous avez besoin de travailler avec des valeurs moins spécifiques ? Et si vous ne vous souciez pas du jour précis ? Il suffit de se soucier du mois ou de l'année, ou même de l'heure ou de la minute. Um, si vous avez besoin de remonter vos données d'une manière moins granulaire et de voir les choses à un niveau supérieur, il y a un certain nombre de formules de sérialisation qui vous permettent de le faire, donc les plus courantes incluent l'année, mois et le jour et heure, minute et seconde. Fondamentalement, tout ce qu'ils font est d'extraire ces composants individuels à partir d'une date donnée. Donc, en termes de syntaxe, ils sont à peu près aussi faciles qu'ils sont écrits exactement comme vous vous attendiez. Donc, année est année, mois, ce mois-ci, Jours, jour et ainsi de suite, et la seule composante réelle de la formule est la référence à une cellule contenant une date ou une heure. Donc, nous allons sauter dans Excel et écrire quelques-uns de ceux-ci. Ce que je vais faire, c'est remplir les cellules C 5 à C 10 pour extraire l'année, mois, jour, heure, minute et la seconde de nos champs de date et d'heure actuels. Donc l'année en cours, ça va être égal à l'année et le numéro de série ou la référence qui pourrait être soit C 2 ou C 3 dans ce cas. Donc je commence par C pour le fermer. Vous verrez qu'il extrait 2015 car c'est la date du jour en cours. Je pourrais également changer la référence. Je vais te montrer un petit raccourci ici plutôt que d'entrer et de changer ça et de taper trois. Euh, évidemment, ce ne serait pas très difficile. Mais ce que je vais faire à la place, c'est appuyer sur F 2 pour plonger et ensuite juste saisir ma référence et faire glisser vers le bas pour voir trois appuyez sur Entrée. Cela accomplira exactement la même chose en passant au mois. même histoire ici, faire mois de C trois jours de C trois. Donc ça me dit que nous sommes dans le huitième mois et le 18e jour du mois Maintenant, pour notre minute et seconde, je dois me référer à la fonction maintenant parce que la fonction d'aujourd'hui ne me donne pas ce niveau de détail. Donc si j'essayais de faire notre de C 2, ça va juste me donner zéro parce qu'il n'y a pas de notre spécification, hum qui sort de cette formule. Donc, ce que nous allons faire à nouveau, c'est juste changer la référence. Glissez-le vers le bas et maintenant il vend Madame dans la 12e heure de la journée pour une minute à nouveau, reportez-vous à la section 3/30 minute et enfin, deuxième de Seat trois est 16 et encore. Comme il s'agit de fonctions volatiles, vous pouvez les regarder changer à chaque fois que vous effectuez une mise à jour de classeur. Donc, si je tape quelques valeurs ici, voir cette deuxième valeur changer ou si je suis allé à l'onglet des formules, compressé ce bouton de calcul maintenant et regardé les secondes cocher. Alors, voilà. C' est un moyen très simple mais très précieux d'extraire des composants spécifiques d'une date. 6. La fonction EOMONTH de la fonction EOMONTH: ensuite, nous avons la fin du mois ou le mois. fonction, qui calcule le dernier jour d'un mois donné, peut également être utilisée pour calculer, commencer ou terminer les dates des mois précédents ou futurs. Donc, la syntaxe sage, juste deux composants de cette formule. Ça commence par votre date de début. Ça pourrait être une date actuelle. Ça pourrait être une date aléatoire. Il peut s'agir de toute référence de cellule à une cellule contenant une date, puis le composant mois, c' est-à-dire le nombre de mois avant ou après la date actuelle de démarrage. Donc si je suis intéressé par le dernier jour du mois en cours, je mettrais un zéro pour les mois ici. Si je suis intéressé par la dernière date du mois prochain, je mettrais une bonne. Si je cherchais la dernière date du mois précédent, je mettrais une date négative et ainsi de suite. Donc, disons que nous avons une date de début ou date actuelle de 83 2015 en auto voir deux. Ici, nous pouvons utiliser la fonction Mois EOE de plusieurs façons. Si nous voulons calculer la fin du mois en cours, nous réduisons Eo Mois C deux virgule zéro. Hum, pour calculer ce début du mois, vous devez être un peu intelligent, mais vous pouvez utiliser la fonction EOE Month pour le faire. Ainsi, par exemple, le début du mois en cours pourrait être calculé en utilisant Eo Month voir à négatif, ce qui me donne la dernière date du mois précédent et ensuite il suffit d'ajouter une pour obtenir la première date du courant mois. Rappelez-vous donc que les dates ne sont vraiment que des valeurs en raison de cette valeur de date sous-jacente. Donc, en ajoutant un à cela essentiellement juste taxe sur un jour. Donc cas similaire ici pour obtenir cette date de début du mois suivant affaiblir, disons, eo mois, Voir à zéro commun pour obtenir le dernier jour de ce mois, puis ajouter un pour obtenir la date de début du mois suivant. Donc c'est sauter pour exceller et en fait pratiquer ça, euh, pour le dernier jour du mois et vendre voir 12. Tout ce que j'ai à faire est juste égal à un mois. Et puis ma référence de début, de date ou de vente à une date pourrait être C 2 ou C 3. vais juste choisir C pour ici et ce que je vais faire est d'appuyer sur F 4 pour corriger cette référence, qui lui donnera la possibilité de copier et coller cette formule ailleurs tout en continuant à se référer à siège à. Donc, je ne veux pas que cette référence se déplace sur ma virgule vers la pièce suivante, qui est des mois. Et dans ce cas, j'aimerais le dernier jour du mois en cours. Donc je vais juste mettre un zéro là, fermer les parenthèses et appuyer sur Entrée. Donc, le résultat est de 4 à 247 Et si vous avez fait attention aux premières conférences, vous reconnaîtrez probablement qu'il s'agit d'une valeur de date sur laquelle il me montre. Cela signifie que cette cellule est formatée avec le type de mise en forme général. Donc, je pourrais faire un clic droit pour mettre en forme les cellules, le changer à ce jour, puis sélectionner une option que je choisis ici. Donc je vais aller avec l'option par défaut, euh, mois, euh, mois, jour année pour nous. D' accord. Et voilà. 8 31 2015 Gardez à l'esprit que selon le moment où vous faites ces exercices, vous allez voir des valeurs et des dates différentes ici que moi. Ne vous inquiétez pas pour ça. les concepts et les principes fondamentaux devraient toujours s'appliquer. Dans tous les cas, je suis donc pour obtenir le premier jour du mois, je vais en fait copier cette formule, la coller, coller, et maintenant me rappeler le petit truc au lieu de zéro, qui s'applique au mois en cours. Je veux la dernière date du mois précédent. Donc je vais mettre un négatif, puis on saute hors des parenthèses et on y ajoute un jour de plus, ce qui m'amènerait au premier jour du mois en cours, soit 81 2015. Alors maintenant, dernier et non des moindres, premier jour de l'année, celui-ci est un peu plus compliqué. Il y a quelques façons de le faire. La première façon est de copier et coller la même formule que je viens d'écrire. Mais au lieu de regarder un mois dans le passé, je vais regarder huit mois dans le passé parce que le mois actuel est août ou huit. Donc ça m'arrivera le dernier jour de décembre de l'année dernière, et puis je vais en ajouter un, qui devrait revenir le premier jour de son année en cours. Ma presse entre. Ça semblait marcher. J' ai donc eu 11 2015. Maintenant, quand vous le faites à la maison, vous ne pouvez pas soustraire huit mois, selon le mois que vous regardez. Nous pouvons donc modifier cette formule pour qu'elle fonctionne peu importe la date actuelle et l' heure actuelle . Et pour ce faire, je vais juste remplacer les moins huit ici. Donc, ce que je dois faire est de soustraire le numéro du mois actuel, qui me demande de savoir quel est le numéro du mois, puis de le multiplier par un négatif pour obtenir la bonne composante du mois pour cette formule. Donc, dans la maladie des imprimés, je vais faire négatif une fois des mois de la date actuelle et puis fermer toutes ces parenthèses. Donc, pour résumer ce que je viens de faire maintenant, je veux la fin du mois. On est Mike date actuelle ou point de départ de deux. Mais le nombre de mois que je veux regarder en arrière dans le temps est fonction de ce que mon mois actuel est. Donc, si je suis actuellement au huitième mois de l'année, je veux revenir dans le temps huit mois et me trouver le dernier jour d'il y a huit mois. Si je suis dans le 10e mois de l'année. Je veux sauter en arrière et récupérer les huit derniers mois d'il y a 10 mois et ainsi de suite et ainsi de suite. Et encore une fois, cette dernière pièce est juste une attaque sur ce jour supplémentaire pour obtenir le premier du mois suivant . Donc si on appuie sur Entrée, voilà. Nous obtenons la même réponse 11 2015 et qui va maintenant retourner le premier jour de l'année, quelle que soit votre date et heure actuelles. Donc, comme vous pouvez le voir, E O mois est une fonction vraiment utile. Vous pouvez l'utiliser pour faire un certain nombre de choses très créatives, et c'est un outil assez critique pour les utilisateurs Excel travaillant avec les dates et les heures car il n'y a vraiment pas de moyen plus simple de le faire. Étant donné que le nombre de jours par mois varie, ce genre d'automatiser beaucoup de ce travail manuel fastidieux que vous auriez à faire autrement. Donc là, vous allez E O fonction mois 7. La fonction YEARFRAC: la fonction suivante dont je veux parler. C' est ce qu'on appelle l'année frack, qui calcule la fraction de l'année représentée par la différence entre deux dates. Donc, généralement, il est utilisé pour comparer la première date d'une année donnée par rapport à la date actuelle pour indiquer pourcentage de l'année que vous traversez. Mais vous pouvez le faire pour deux dates et regarder. Fondamentalement, tout le monde sait que vous choisissez si syntaxe sage, trois composants de cette formule que vous avez votre date de début, votre date de fin, et votre base de base vous permet juste de spécifier le type de nombre de jours à utiliser ou, autrement dit, comment définissez le nombre de jours dans une année. Um, zéro est la valeur par défaut. Je recommande en fait d'en mettre un ici, qui regarde le nombre réel de jours dans l'année réelle dans laquelle vous êtes en fonction de vos dates de début et de fin. Donc cela représentera des choses comme, euh, années bissextiles, qui pourraient avoir un jour supplémentaire 366 contre 365 pour l'année précédente ou l'année suivante. Voici donc un exemple. Si vous avez une date de début dans Selby 211 2015 et une date de fin dans la cellule B trois 2 28 2015 Voici , par exemple, les sorties dans le premier cas, nous utilisons la base unique, qui est les chiffres réels. Et cela nous dit que le 28 février 2015 représentait 15,9 % de l'année . Et c'est tout au long de l'année. 2015 si nous changeons la base pour que ce pourcentage change légèrement à 16,1, parce que maintenant il est basé sur une base de 360 par année. Donc pro tip ici année frack est un excellent outil pour des choses comme le rythme et les calculs de projection . Jetons un coup d'oeil à Excel et fondamentalement, nous allons juste utiliser les données que nous avons déjà entrées ici pour calculer le pourcentage à travers l'année de notre date actuelle. Donc, je vais faire égal que vous êtes frack, ouvrir la parenthèse, notre date de début va utiliser le premier jour de l'année, que nous avons calculé dans la dernière conférence, puis la date de fin et voir à, et puis ma base va pour être un. Quand je ferme ça, je reçois 63 %. Donc ça me dit ça aujourd'hui, le 18 août. Je suis 63% de l'année. Alors, voilà. Tu es fâché. 8. JOUR SEMAIN, JOURNÉES de travail et de réseau: accord, alors parlons de trois autres fonctions de date ou en parler jour de semaine, jour de travail et jours de réseau. Donc, en commençant par le jour de la semaine vraiment précieux. Il vous montre quel jour de la semaine une date donnée tombe, et il y a deux façons de le faire. Le premier est juste d'utiliser un format de cellule personnalisé comme nous avons parlé dans la conférence à, Donc, vous pouvez utiliser soit trois DS pour obtenir le jour abrégé de la semaine ou quatre D pour obtenir le jour complet de la semaine et encore noter que cela ne change pas réellement la valeur ou la création de de nouvelle valeur. C' est sur Lee de changer comment la valeur de la date d'origine est affichée pour vous. La seconde, qui est l'approche que je recommande, utilise la fonction de jour de semaine, qui renvoie en fait une valeur série correspondant à un Dave particulier cette semaine, soit de un à sept ou de zéro à six. Donc, cette syntaxe est assez simple. Vous avez d'abord votre numéro de série, qui est la vente qui contient une date ou une heure, puis votre type de retour, qui est facultatif où zéro est votre valeur par défaut, qui attribue un numéro un à un dimanche, jusqu'au numéro sept, qui est samedi. Um, si vous préférez appeler lundi début de la semaine et assigné un lundi, vous pouvez en mettre un ici au lieu d'un zéro ou au lieu de le laisser vide eso une fonction vraiment utile, surtout quand il s'agit de faire des choses comme les analyses du jour de la semaine où nous voulons voir comment performances ou les données diffèrent, euh, euh, mardi par rapport à un vendredi par rapport à un mercredi et ainsi de suite, euh, jour de travail et jours de réseau. Donc, la journée de travail, pour être tout à fait honnête, est un peu déroutante. Retourne fondamentalement une date qui est un nombre spécifié de jours avant ou après une date de début donnée , à l'exclusion des week-ends et jours fériés, si vous choisissez d'ajouter ceux-ci. Donc, l'application la plus courante à laquelle je peux penser est de dire à quelle date tombe 50 jours ouvrables à partir d'aujourd'hui. Donc une fois que j'ai passé 50 jours de travail, qui n'inclut pas les week-ends, ce qui n'inclut pas les week-ends,la date à laquelle je vais atterrir, et donc le péché fiscal, ça commence par votre date de début, puis le nombre de jours. Vous pouvez soit mettre une valeur positive ici si vous voulez regarder vers l'avenir ou une valeur négative. Si vous voulez regarder en arrière dans le passé. Ces air tous les composants requis. Il existe un composant de congés facultatifs qui vous permet de référencer une liste de jours fériés, et il exclut essentiellement ceux du nombre de jours ouvrables qu'il calcule . Donc c'est la journée du travail. Network Day, qui trouve en fait beaucoup plus utile et plus pratique, vous montre le nombre de jours de travail entre deux dates. Donc, vous et mettre une date de début à la date de fin et une référence facultative à une liste de jours fériés, et il vous dira combien de jours de travail ou de jours ouvrables sont tombés entre ces dates. Donc, deux exemples ici j'ai les mêmes références à ce jour que j'ai mentionnées dans la dernière conférence, qui sont le 1er janvier 2015 comme notre début, le 28 février 2015 comme notre fin. Donc, si nous disons journée de travail, être trop commun 20 que dire à partir de la date de début du 1er Janvier 2015. Quelle date tombe 20 jours ouvrables après cela, et il retournera la date 29 Janvier 2015 si nous utilisons la fonction jours réseau et définir B deux est notre date de début, et B trois est là terminé. Il nous dira qu'entre le 1er janvier et le 28 février 2015. Il y avait 42 jours ouvrables , soit le nombre total de jours moins toutes les fins de semaine. Alors, allons sauter dans Excel et voir comment cela fonctionne dans la pratique. Donc en F 3, j'ai une cellule de date d'échéance du projet, et je vais juste me rattraper un rendez-vous. Loin dans le futur, ça ne veut rien dire. Alors disons le 9 mai 2017. Si c'est un grand projet, ça va prendre beaucoup de temps. Jours avant de faire cela est vraiment simple. Puisque les valeurs de date air vraiment juste des valeurs que je peux effectuer n'importe quelle opération normale comme je le ferais tous les autres nombres, je vais juste dire que la date d'échéance moins la date actuelle retourne maintenant 6 30 Donc avoir 630 jours entre le 18 août 2015 et le 9 mai 2017. Donc c'est le nombre total de jours jusqu'à maintenant faire. Si je veux le nombre de jours de travail jusqu'à ce que faire évidemment je ne vais pas passer mes week-ends travailler sur ce projet. Je peux utiliser les jours réseau, donc la date de début des jours ouvrables nets est la date actuelle. 18e date de fin est la date d'échéance. Donc, si je laisse le champ des vacances facultatives vide. Fermez ces parenthèses. Impressionner le centre. Cela me dit que bien, j'ai 630 jours au total jusqu'à ce que ce soit fait, je n'ai que 451 jours ouvrables jusqu'à ce que ce soit pour la pièce des fêtes. Ce que je ne peux pas faire, c'est juste taper un certain nombre de vacances comme cinq et dire OK, exclure ces cinq jours fériés. Vous allez juste retourner la même valeur de 4 51 Ce dont il a besoin ici est une référence réelle à une liste de dates qui définissent les vacances que je veux exclure eso ce que je peux faire juste pour un exemple. Disons 12 25 2015. Je veux décoller Noël et je veux décoller le 1er janvier 2016. Et ce sont les deux jours fériés entre la date d'aujourd'hui et le 9 mai 2017 que j'ai l'intention de décoller et de ne pas travailler. Alors quoi, je peux le faire dire OK, au lieu de mettre un cinq là-bas, je vais me référer à l'âge 536 qui est ma liste de dates de vacances et appuyez sur Entrée, et maintenant vous pouvez le voir mis à jour à 4 49 donc il a exclu ces dates de vacances aussi. Notez que si vous avez un jour férié qui tombe également un week-end, il aurait déjà été exclu ici. Mais dans ce cas, ces deux dates sont des jours de semaine, donc elles étaient comptées dans le total de 4 51. Et donc quand j'ajoute cette référence dans, il exclut ces deux et quitte. Déplacer 449 jours ouvrables jusqu'à ce que le projet soit dû. Last but not least pour le jour de la semaine à nouveau, je pourrais juste faire un clic droit et aller au formatage des cellules sur le champ de date réelle et dire, Faisons juste une suppression personnalisée de cette ligne de type et mettre Dee dee dee dee. Ok, c'est un mardi, mais je veux continuer à montrer la date dans son format actuel. Et il suffit d'ajouter le jour de la semaine séparément ici. Donc je vais utiliser la fonction de jour de semaine est la semaine, jour du numéro de série, qui est F 3. Je vais laisser le type de retour par défaut, donc j'ai un trois. Et quand je formate les trois qui peuvent changer cela à personnalisé de de de de et là vous allez me donne mardi. Donc le 9 mai 2017 est un mardi. Alors, voilà. Il y a le jour de la semaine et les jours de réseau 9. La fonction DATEDIF: Donc, la dernière fonction dont je veux parler dans cette section est appelée date def. Et il calcule le nombre de jours, mois ou d'années entre deux dates données. Donc, trois composants de la syntaxe de formule commence par la date de début, puis vous et mettez une date de fin. La troisième pièce est l'unité. Donc, c'est de déterminer comment vous voulez calculer la différence entre les deux jours. Vous pouvez définir un d ici pour calculer, car le nombre de jours entre les dates M renvoie le nombre de mois entre les dates. Pourquoi retournerait-il le nombre d'années entre les dates ? Il y a aussi des options comme M d y d ou pourquoi je m. Qui calculera ces différences indépendamment des mois ou indépendamment des années. Donc, deux exemples ici encore, nous regardons les mêmes dates de début et de fin que celles que nous avons utilisées dans les deux exemples précédents. 1er janvier 2015 Zehr début 28 février 2015 est notre fin. Donc, si nous fixons le décalage de date entre sa date de début et être à notre date de fin et B trois et dit l'unité deux jours, il retournera 58. Comme ils sont 58 jours entre ces états et notez que cela fonctionne comme une fonction de jours réseau, sauf les jours réseau excluant les week-ends. Cela renvoie toutes les dates et note aussi que, euh, ça fait exactement la même chose que d'écrire la formule B trois moins B deux. Alors pourboire pro. Si vous avez seulement besoin de calculer le nombre de jours entre les dates, il suffit d'utiliser une simple soustraction. Vous n'avez pas besoin d'utiliser une fonction de date if pour cela. Mais où la date a fonctionné devient beaucoup plus précieux et où il ne peut pas être répliqué . utilisation d'opérations simples est si vous voulez calculer une différence en termes de mois ou années, ou quelqu'un et ainsi de suite. Donc, si nous voulons dire diff date entre le 1er janvier et le 28 février avec l'unité M D, qui est le nombre de jours entre les dates ignorant les mois et les années, il va dire qu'ils sont 27 jours entre le 28 et le premier. soit, si je regarde un mois différent ou une année différente, jetons un coup d'oeil à Excel et pratiquons ceci. Donc, pour revoir cette date d'échéance du projet que nous avions écrit dans la dernière conférence. Ce que je peux faire ici, c'est, si je veux calculer les mois restants jusqu'à ce que mon projet soit dû, je peux taper égal date sourd. Et puis je ne sais pas pourquoi c'est le cas, mais Excel ne vous aide pas à travers cette fonction. Ça te laisse en quelque sorte pendre. Je ne sais pas si c'est un bug ou quoi, mais si nous comptons sur notre mémoire, nous savons que le premier composant de la date, def, est la date de début, qui est ma date actuelle. Et la date de fin sera la date d'échéance. Et la dernière pièce est l'unité, tu te souviens ? Donc dans ce cas, j'ai besoin des mois restants, donc je vais mettre un M entre guillemets. Et cela ne marchera pas à moins que je mette le M entre guillemets ici. Alors gardez ça à l'esprit. Et puis quand j'appuie sur Entrée, cela me dit qu'il me reste 20 mois à partir de la date actuelle jusqu'à la date à laquelle mon projet est dû et puis cas similaire ici, ce que je vais faire au lieu d'écrire ceci à partir de zéro, je suis Je vais juste réparer les références dans mes fonctions originales de réparer le C 2 et réparer le F 3. Copiez cette formule, collez-la et changez le M en un Pourquoi, puisque je veux les années restantes. Donc ça me dit qu'il me reste un an jusqu'à ce que mon projet soit dû. Je pouvais aussi sauter en arrière pour vendre F cinq, où avait calculé mes jours jusqu'à ce que faire comme juste f trois moins C deux et nous allons voir si nous pouvons obtenir la même réponse. 630 en utilisant une date a fonctionné. Donc je vais faire date sourd C deux virgule F trois virgule de entre guillemets et là vous allez 6 30 donc encore deux façons de faire la même chose et je ne sais pas pour vous, mais je préférerais juste dire F trois moins C deux et l'appeler un jour. Mais la date si peut être une excellente option, surtout si vous voulez agréger la différence entre deux dates au niveaudu mois ou de l'année niveau 10. MISE EN VALEUR DU PROJET : Concevoir un exemple de stimulation de budget: D' accord. Bienvenue à la fin de la section 7. Félicitations pour l'avoir fait passer. Nous avons parlé d'une tonne defonctions de date et d'heure différentes et utiles, fonctions de date et d'heure différentes et utiles, et maintenant il est temps de mettre certaines d'entre elles en pratique. Donc, ce que nous allons faire est un peu de marche à pas à travers ce projet qui intègre un tas de fonctions de temps de rencontre en tandem avec certaines des fonctions que nous avons apprises plus tôt dans notre formation. Alors faisons-le. La première chose dont nous avons besoin est la date actuelle et de voir et contrairement à ce que vous pourriez penser, en fait, je ne veux pas utiliser la fonction d'aujourd'hui ici. La première chose dont nous avons besoin est la date actuelle et de voir et contrairement à ce que vous pourriez penser, en fait, C' est un peu trompeur, mais je ne veux pas revenir quel jour c'est aujourd'hui. C' est un peu trompeur, Je veux retourner le dernier jour dans mon ensemble de données. Donc, si vous vous souvenez de cet échantillon de données a des dépenses déclarées par jour descendant le 16 mai. Donc, si je n'ai pas de données de dépenses après cela et que je tape aujourd'hui, qui est en août, cet outil ne sera pas très utile. Donc, à la place, je vais utiliser une fonction max. Je veux juste retourner la date maximale de la colonne A de mes données échantillon, Ted, maintenant nous allons retourner 5 16 L'autre avantage de faire ceci est que lorsque vous travaillez à travers cet exercice seul à la maison et que vous utilisez la fonction d'aujourd'hui, ça ne va pas rivaliser avec des échantillons de données et ça n'aura pas vraiment de sens. Donc, pour l'instant, utilisez simplement la fonction du Mac. Et si vous finissez par en construire un ou en personnaliser l'un pour votre propre usage, vous pouvez le modifier comme bon vous semble. Je suis tellement date de début va être le premier jour du mois en cours à nouveau courant signifiant les dernières données dans notre ensemble de données. Donc, je vais utiliser la fonction EOE Month ici. Date de début. Ce sera C deux et rappelez-vous que pour obtenir la première date du mois en cours, je dois prendre la dernière date du mois précédent donc moins un plus un jour, ça me donne 51 2015 ce qui est exactement ce que je veux maintenant pour pourcent du mois. Celui-ci semble que ça devrait être vraiment facile, mais c'est en fait un peu plus compliqué que vous ne le pensez parce qu'il n'y a vraiment pas de fonction de frack de mois . Euh, tu sais, année. Frack est vraiment utile parce que vous pourriez juste dire me dire le pourcentage tout au long de l'année que je suis Il n'y a pas un équivalent pour le mois frack et les défis que chaque mois a un nombre différent de jours. Donc je sais que j'ai 15 jours de mai. Mais est-ce que je divise ça par 30 jours ou 31 ou 29 jours ? Donc, ce dont nous avons besoin est une petite table utile, que j'ai commodément ajouté ici dans les cellules B 24 à C 35 qui me dit le nombre de jours dans chaque mois en fonction des numéros de mois afin que je puisse utiliser cela pour calculer mon pourcentage de mois est égal à ma date actuelle moins ma date de début. Et ils entourent cette paix entre parenthèses. C' est le nombre de jours au cours du mois qui est actuellement. Et je vais diviser ça par le nombre total de jours du mois. Pour obtenir cela, j'ai besoin d'utiliser une fonction de recherche V et la valeur de recherche sera le mois en cours, qui est le mois de ma date actuelle. Donc le mois de C deux retournera juste cinq et cinq est la valeur de recherche que je cherche dans cette table. D' accord, donc ça va chercher cinq, et ensuite je retournerais la valeur dans la deuxième colonne. C' est là que vivent mon nombre de jours. Et puis ce sera une correspondance exacte. Alors finissez-le avec une fermeture zéro. La parenthèse et le hit enter ont maintenant rapporté 48,4 % me disant que le 16 mai, j'étais 48,4 % du mois. Hum, et encore une fois, juste pour résumer ce que fait cette formule, c'est le calcul. Le nombre de jours se sont écoulés en mai divisé par le nombre de jours en mai. Et il trouve cela en cherchant le mois de ma date actuelle vers le bas dans ce tableau de table et en retournant la valeur dans la deuxième colonne sur. Donc c'est cette pièce. La prochaine pièce porte sur mon budget mensuel et mes dépenses mensuelles. Donc, le budget mensuel est un chiffre entré par l'utilisateur, et c'est ce que vous voulez. Donc, disons que votre budget mensuel est de 2500$. C' est juste une sorte de mise en pierre maintenant pour calculer les dépenses mensuelles. Il y a une étape que nous devons franchir parce que nos données sur les dépenses se situent au niveau quotidien et que nous cherchons à les regrouper au niveau mensuel. Nous devons donc créer une nouvelle colonne, un mois appelé, qui enroule ces données au niveau du mois. Donc ça va juste égaler le mois de la date battue, aussi, et ensuite s'appliquer, et ça va me montrer. OK, premier mois pour toutes les dates de janvier au 3 février pour mars pour avril et cinq à mai. Et maintenant que j'ai cette dimension supplémentaire, euh, je peux utiliser une formule « certains if » pour regrouper mes totaux de dépenses au niveau du mois, pas seulement au niveau du jour. Donc, si vous vous souvenez des si de la section Fonctions statistiques, nous allons juste commencer par quelques if, parenthèses ouvertes, et puis la plage est fondamentalement où vivent les données. Ce que j'essaie d'agréger est la colonne de dépenses. Voir mes données échantillon 10 et puis la plage de critères est fondamentalement le numéro de mois parce que c'est les critères dont il a besoin pour répondre à ces totaux de dépenses de sorte que la plage de critères appelle A et mes critères sont le mois de la date actuelle. Alors rappelez-vous ce dont je vais avoir besoin, c'est une fonction qui retournera le numéro cinq ici. Et donc ce sera juste des mois de l'outil de rythme budgétaire mensuel cellule C 2, puis fermez ces deux parenthèses. Et voilà. Donc, quand j'appuie sur entrer, il revient. 16 03 Ce qui me dit que toutes les données sur les dépenses du mois de mai totalisent 2 1603$. Je peux vérifier cela en faisant défiler les données de mai et en regardant le soleil 16 03 Donc, cela fonctionne correctement. Um, si je voulais rendre ces formules un peu plus faciles, ce que je pourrais aussi faire est juste dans une autre cellule. Je pourrais juste écrire cette fonction de mois pour retourner les cinq, puis dans mes quelques if, plutôt que d'imbriquer la fonction de mois là, je peux simplement me référer à cette cellule où les ont déjà mis en fonction et obtenir le même réponse. Donc, quelques façons différentes de le faire. Cela dépend en quelque sorte de ce que vous cherchez à faire maintenant. Pourcentage du budget va juste être ce que j'ai dépensé divisé par ce que mon budget est donc 64,1 %. Hum, maintenant, vous verrez que j'ai ajouté le cadre pour le graphique. Je suis donc cliquez sur cela, et nous allons faire un clic droit pour sélectionner les données. Et je sais que cette formation n'est pas sur les graphiques et les graphiques, donc excuses si c'est difficile à suivre, euh je vais publier, bien sûr , des graphiques et des graphiques dans un proche avenir. Alors restez à l'écoute pour ça. Mais en attendant, essayez de suivre étape par étape au fur et à mesure que nous construisons cela. Donc je vais ajouter un Siri. Le nom de Siri va être appelé pour cent du mois dans les valeurs d'un Siri va juste être cellule C pour juste me dire le pourcentage du mois touché. Ok, ajoutez-en un de plus. Chez Siri. Le nom de ce Siri va être pour cent du budget, et les valeurs de Siri vont juste se vendre en 4, c'est bon, et puis on a frappé. Ok, encore une fois. Comme vous pouvez le voir, il est créé graphique pour moi. Cela montre le pourcentage du budget affiché en orange et le pourcentage du mois affiché en bleu . Maintenant un morceau de plus que je vais faire ici dans cette cellule, j'ai émergé cellule dans chacun à ce que je vais faire. Maintenant un morceau de plus que je vais faire ici dans cette cellule, Il suffit de calculer la différence entre les deux pourcentages. Donc ça va juste être égal à F pour moins C quatre. Donc ça me dit que j'ai dépensé 15,7 % de points au-dessus de ce que je fais pour le mois. Donc, ce que je peux faire maintenant juste pour ajouter des cloches et des sifflets va mettre en évidence les règles de cellule de mise en forme conditionnelle et dire, Ok, si ce nombre est supérieur à zéro, cela signifie que pour cent du budget est supérieur à pour cent du mois, ce qui veut dire que nous sommes sur le rythme, ce qui est une mauvaise chose. Je vais formater cela avec un rouge clair rempli de texte rouge foncé, qui est l'une des options par défaut et appuyez sur OK, puis aussi à une règle où, si c'est inférieur à zéro, ce qui signifie qu'ils étaient sous rythme, nous ferons le remplissage vert avec du texte vert foncé et appuyez sur OK, donc maintenant si mon budget mensuel était de 5000$ maintenant, nous allons les envoyer sous rythme de 16,3% points afin que vous puissiez jouer avec elle, vous savez, vous savez, pour voir comment les mises à jour du graphique et puis durer, mais pas Au moins, si je voulais Teoh, , ajoutez-y un peu plus de flair. Je pourrais ajouter un opérateur logique ici qui dit, euh donc égal. Si H 2 est supérieur à zéro, alors je veux que cela dise sur le rythme. Sinon, sous rythme, Fermer le Francis Donc cela va juste ajouter une petite étiquette là sous mon formaté conditionnellement . Alors montrez-moi d'accord, je suis sous le rythme maintenant, mais si mon budget était à nouveau 2500 maintenant, je suis sur le rythme. Alors, voilà. C' est Thea, un excellent exemple de la façon de combiner un certain nombre de fonctions différentes à partir d'un certain nombre de catégories de formules différentes pour créer quelque chose qui est réellement utile. La dernière chose à laquelle parler est que cet outil est génial car il va mettre à jour un peu seul que vous ajoutez plus de données. Donc disons qu'une autre semaine passe. Vous savez que vous pouvez faire glisser cela vers le bas car vous pouvez voir l'orteil par défaut de Siri ajouter des jours pour chaque ligne. Je pourrais juste faire glisser ma formule du mois vers le bas et dire que OK a dépensé 90$ sur le 17e 114 le 18e et 75 le 19e et quand je retourne à mon outil, comme vous pouvez le voir, il est mis à jour. Je pourrais juste faire glisser ma formule du mois vers le bas et dire que OK a dépensé 90$ sur le 17e 114 le 18e et 75 le 19e et quand je retourne à mon outil, comme vous pouvez le voir, Donc maintenant, la date actuelle est le 19e depuis deux fonctions max. Je suis 58,1% et j'ai 75,3%. C' est mon budget qui tient compte de ces jours supplémentaires que je viens d'ajouter. Nous avons donc passé un peu de temps à construire le front end, mais maintenant il est assez automatisé pour utiliser très facilement aller de l'avant. Donc, cela enveloppe les fonctions de temps de rencontre. J' espère que vous l'avez trouvé utile. Restez à l'affût de la section suivante, qui est basée sur des règles de mise en forme basées sur des formules. 11. HOMEWORK: : Fonctions de la date et de l'heure: D' accord. Félicitations pour avoir terminé la Section 7. Fonctions de date et d'heure. Si vous voulez obtenir un peu de pratique supplémentaire dans. J' ai de bonnes démos pour vous dans le fichier d'exercice de devoirs Excel. Alors allez-y et ouvre-le. Accédez à l'onglet des fonctions de date et d'heure. Et là, nous avons sept pas. Essentiellement, vous allez vous entraîner à utiliser toutes les fonctions que nous avons abordées dans ce chapitre aujourd'hui. Maintenant jour de la semaine, jour du réseau, fin du mois chez Phil Siri. Alors allez-y et donnez qu'un fichier de réponse de tir est disponible dans le cours. Ressource est ou tout simplement me tirer un message si vous avez besoin de soutien. Bonne chance.