Une autre question de mon séminaire Atlanta Power Excel:
Comment arrondir le temps facturable au quart d'heure suivant?
Dites que vous gardez un journal de votre temps facturable. Vous avez une heure de début et une heure de fin. Une formule telle que =E2-D2calculera le temps écoulé.
Le résultat en F2 est de 55 minutes. Mais la mise en forme par défaut indique 12h55. Choisissez toute la colonne. Appuyez sur Ctrl + 1 pour formater les cellules. Sélectionnez la catégorie Heure. Lequel de ceux-ci devriez-vous utiliser? Pour l'instant, choisissez 13:30:55, mais vous verrez plus tard le problème avec cette sélection.
La politique de votre entreprise est d'arrondir toutes les factures jusqu'au prochain quart d'heure. Faites ce calcul: il y a 24 heures dans une journée. Il y a 4 quarts d'heure dans une heure. Multipliez 24 * 4 pour obtenir 96. Cela signifie qu'un quart d'heure équivaut à 1/96 d'une journée.
Utilisez la formule =CEILING(F2,1/96)pour arrondir le temps écoulé en F au quart d'heure suivant. Dans la méthode de stockage du temps d'Excel, midi est 0,5. 6AM est 0,25. 12:15 AM est 1/96 ou 0.010417. Il est plus facile de se souvenir de 1/96 que de 0,010417.
Les résultats sont montrés plus bas. Remarquez comment les 35 minutes tournent à 45 minutes. Cela semble être la formule que mon avocat utilise… haha, c'est juste une blague Esquire Dewey! Ne me poursuivez pas! Je sais qu'ils utilisent =CEILING(F2,4)…
Pause ici… cet article est arrondi au prochain quart d'heure parce que c'est la méthode que Larry utilise dans mon séminaire. Il y a de fortes chances que votre entreprise se tourne vers autre chose. L'image ci-dessous montre comment arrondir à cinq minutes ou 1 minute. Vous pouvez utiliser une logique similaire pour arrondir aux 6 minutes (1/240) ou 12 minutes (1/120) les plus proches.
Aussi - une suggestion douce montrant la différence entre FLOOR, MROUND et PLAFOND. La fonction FLOOR dans B9: B22 arrondit toujours vers le bas. Le MROUND en C9: C22 arrondit au plus proche. Le PLAFOND en D9: D22 s'arrondit.
Remarque
L'infobulle Excel vous suggérera d'utiliser FLOOR.MATH ou CEILING.MATH au lieu de FLOOR ou CEILING. Les nouvelles versions ".Math" de ces fonctions gèrent différemment les nombres négatifs. Étant donné que le temps dans Excel ne peut jamais être négatif, il est bon d'utiliser le FLOOR ou le PLAFOND normal.
Changez la fraction de 1/96 à 1/288 pour arrondir à des incréments de cinq minutes.
Conversion des temps en heures
Une autre suggestion de Larry: Convertissez les heures en heures réelles. De cette façon, lorsque vous multipliez par le taux de facturation par heure, le calcul fonctionnera. Prenez le temps écoulé arrondi et multipliez par 24 pour obtenir un nombre décimal d'heures. Assurez-vous de formater le résultat sous la forme d'un nombre avec deux décimales et non une heure.
Quel format horaire utiliser?
Il est temps de parler de ces choix de mise en forme de l'heure: vous auriez pu choisir 13:30, 13:30:55 ou 37:30:55. Dans les captures d'écran ci-dessus, j'ai utilisé 13:30:55. Le problème avec cela apparaît à la fin de la semaine. Lorsque vous additionnez les heures décimales dans la colonne H, vous avez 53 heures facturables. Lorsque vous additionnez les temps en G, vous obtenez cinq heures. Que se passe-t-il?
Les deux sont corrects, si vous comprenez le format numérique que vous avez sélectionné. Dites que vous entrez =NOW()dans une cellule. Vous pouvez formater la cellule pour afficher la date et l'heure, juste la date ou juste l'heure. Excel n'a aucun problème à laisser la partie du 29 mai du 29 mai à 08:15:34 si vous lui dites de le faire. La réponse dans la cellule G57 est "2 jours et cinq heures". Mais lorsque vous avez choisi 13:37:55 comme format d'heure, vous disiez à Excel de supprimer le nombre de jours et de ne vous montrer que les heures, les minutes et les secondes.
Si vous convertissez la colonne G au format 37:30:55, vous obtenez le nombre d'heures correct:
La légende au-dessus de cette image capture le petit problème avec le format 37:30:55: il montre les secondes, ce qui semble inutile puisque nous arrondissons au quinze minutes près. Après avoir choisi 37:30:55, cliquez sur la catégorie Personnalisé. Vous pouvez voir le code de formatage des nombres (h):mm:ss;@utilisé. Les crochets autour du H sont la partie importante.
Pour afficher vos heures en heures et minutes, modifiez le format des nombres pour supprimer: ss.
Regarder la vidéo
La vidéo d'aujourd'hui contient un conseil bonus à la fin pour formater les décimales sous forme de fraction.
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2210: arrondissez au prochain quart d'heure.
Salut, bon retour sur le netcast, je suis Bill Jelen. Aujourd'hui, une autre question de mon séminaire Atlanta Power Excel. Quelqu'un faisait une feuille de calcul de facturation et voulait arrondir au quart d'heure suivant, n'est-ce pas? Donc, pour chaque tâche ici, ils ont un client, une heure de début et une heure de fin, et pour calculer le temps écoulé - c'est simple, si ce sont des temps réels ou s'ils sont simplement = E2-D2. Donc, ça veut dire 55 minutes, mais ça dit d'une manière vraiment bizarre - "00h55." Nous voulons aller de l'avant et formater cela. Choisissez Time, et hé, regardez, il y a quelques choix différents ici: 13:30; 13:30:55, qui afficherait les secondes; puis 37:30:55. Maintenant, pour l'instant, je vais choisir 13h30, mais nous allons revenir plus tard et réexaminer ce choix. Donc, je choisis OK et double-clique pour le copier,et vous voyez qu'il ne fait que faire le calcul - alors ici, 25 minutes. Mais, la personne qui a posé cette question - l'entreprise pour laquelle elle travaille - elle veut arrondir au quart d'heure suivant.
Très bien, alors, un quart d'heure. Pensons aux temps dans Excel. Midi est 0,5. Si je formate cela comme une heure - 12 h 00. Et si je fais 0,25, ce sera 6h00. D'accord, donc la façon dont cela fonctionne est qu'une journée complète est le nombre 1.0, et donc, si nous voulions comprendre 15 minutes, ce serait - il y a 24 heures x 4 quarts d'heure, donc nous devrions mettre = 1/96 là-dedans, et cela nous donne 15 minutes. Maintenant, 1/96 - je peux en quelque sorte faire ce calcul dans ma tête: 24x4; 1/96 - il est certainement plus facile de se souvenir de 1/96 que de se souvenir de cette décimale: 0,01041666667. D'accord, il était donc plus facile d'utiliser 1/96 là-bas, et nous allons donc utiliser une fonction appelée CEILING.
PLAFOND. Vous voyez, nous avons le choix entre PLAFOND et PLAFOND.MATH pour les nombres positifs identiques. Il est plus court de taper CEILING, donc je vais utiliser CEILING. Et, arrondi, il y a 1/96 de jour - soit 15 minutes - et c'est la bonne réponse, dans le mauvais format. Nous allons copier ces formats - Ctrl + C, puis Alt + E + S + T pour coller des formats spéciaux - et les 55 minutes sont arrondies à l'heure suivante. Double-cliquez pour copier cela vers le bas, et voyez, les 35 minutes sont arrondies aux 45 minutes suivantes; 25 arrondis aux 30. Cela semble fonctionner, bien que la personne qui a posé cette question - ils arrondissent aux 15 suivants.
D'accord, donc pour arrondir au quart d'heure le plus proche, nous utilisons 1/96. Mais pour arrondir aux 5 minutes les plus proches - il y a 24 heures, il y a 12 périodes de 5 minutes dans chaque heure, donc 12x4 = 288. Utilisez 1/288 pour arrondir aux 5 minutes près. Si vous arrondissez aux 6 minutes les plus proches, il y a 10 périodes de 6 minutes x 24 heures; vous arrondissez au 1/240 le plus proche. Pour arrondir à la minute près, sélectionnez au 1/1440 le plus proche.
Très bien, vous pouvez le faire, et nous avons également le choix. PLAFOND va toujours s'arrondir, donc 16 minutes - passons au 1/96 là - 16 minutes vont arrondir à 30, et, même si je suis sûr que mon avocat pense que c'est une excellente façon de facturer, c'est pas vraiment juste pour moi - le client. D'accord, vous savez donc que CEILING s'arrondira. Mon avocat n'utilisera jamais ceci: ce FLOOR sera toujours arrondi. Mais, ce qui est probablement le plus juste ici, c'est d'utiliser MROUND. Donc, = MROUND du Temps au 1/96 le plus proche, et cela arrondira pour tout jusqu'à environ 22,5 minutes; et ensuite, après ça, ça s'arrondira. Très bien, donc il arrondit à ce dont il est le plus proche. Donc, vous devez choisir si vous allez utiliser FLOOR, MROUND ou CEILING, et ensuite quel sera votre diviseur - si c'est 's va être aux 5 minutes les plus proches, à la minute la plus proche ou, vous savez, peu importe. D'accord, alors là nous arrondissons.
Maintenant, un conseil supplémentaire ici - c'était Larry dans ce séminaire - Larry dit: "Hé, écoutez, ce n'est pas vraiment bien d'afficher le temps facturable dans des moments comme celui-là; nous voulons vraiment convertir en heures parce que vous allez vraiment pour facturer à un certain taux - vous savez, 35 $ / heure, 100 $ / heure, 500 $ / heure, quel que soit votre tarif - et nous devons être en mesure de multiplier les heures x les tarifs. Donc, ce serait bien de être en mesure de convertir ces heures en heure décimale. Donc, nous prenons ce temps dans G2 et le multiplions par 24, et cela va nous donner un nombre décimal d'heures, mais ce sera dans le mauvais format. Ils sont choisir un format d'heure dans ce cas, alors qu'il ne devrait pas être du tout un format d'heure; il doit être simplement au format numérique. Et assurez-vous d'inclure au moins 2 décimales. Double-cliquez pour le copier vers le bas, et nous obtenons notre temps. "
Bon, maintenant, revenons à notre question ici de "Quel format de nombre devrions-nous utiliser?", C'est un très bon endroit pour parler de "Pourquoi?". Si nous choisissons tous ces nombres, ici, nous verrons que la somme est de 53 heures, et si je viens ici et que je mets une fonction SOMME, nous obtenons 53 heures. C'est parfait. Mais, si j'utilise la fonction SOMME sous une colonne de temps, et que je fais Alt + = ou l'AUTOSUM, cela me donne 5 heures… 5 heures. Ce qui est juste - 53 ou 5?
Eh bien, il s'avère que dans un sens étrange, ils ont tous les deux raison. Mais voici pourquoi ils ont tous les deux raison. Je vais entrer une fonction appelée = MAINTENANT. Vous verrez que j'enregistre en fait ça le 9 mai, donc, il y a 20 jours, et il est 7h08 Et avec = MAINTENANT, parfois je veux montrer la date et l'heure, mais d'autres fois, je veux juste pour afficher la date. Donc, je peux faire = MAINTENANT me donner la date actuelle, ou, je peux faire = MAINTENANT me donner l'heure actuelle. Et ainsi, les fonctions numériques sont heureuses soit de tronquer le temps de congé, soit de supprimer la date de congé. Et celui que nous avons choisi ici, le 13:30, dit: "Hé, je ne veux pas voir le nombre de jours; montre-moi juste l'heure." Oui, c'est ça le problème - parce que c'est vraiment 2 jours et 5 heures - 2 jours c'est 48 heures - 2 jours et 5 heures. Et nous l'avons dit - nous avons dit à Excel, "Hé, ne me donnez pas la peine de me montrer les jours. "Eh bien, nous ne savions pas que nous disions cela à Excel, mais c'est ce que nous disions à Excel lorsque nous avons choisi celui-ci ou celui-ci. D'accord, vous êtes obligé de le faire descendez ici à 37 h 30 h 55. C'est celui qui va nous montrer le nombre total d'heures, et nous obtenons la bonne réponse de 53 heures.
Mais, il semble vraiment idiot de montrer ces secondes supplémentaires lorsque nous arrondissons à 15 minutes. Alors, revenons là-dessus. Après avoir choisi 37:30:55 pour apprendre le format numérique derrière cela, cliquez sur Personnalisé. Vous verrez que ce qui fait que cela fonctionne, ce sont les crochets autour de l'heure. Mais nous n'avons pas besoin de: ss. Donc, maintenant, nous pouvons modifier ce format personnalisé, et nous avons donc des heures et des minutes. Nous n'avons pas besoin de comprendre aujourd'hui ce que signifie le; @. Il dit que, pour le texte, montrez simplement le texte; mais laissez simplement cette partie là, même si vous ne la comprenez pas. Cliquez sur OK, et nous avons maintenant notre temps en heures et minutes, en laissant de côté les secondes. Mais, les totaux fonctionnent. Vraiment, vraiment subtile, que 13h30 et 13h30:55. Vous savez, cela ne dit pas: "Hé, vous dites à Excel d'ignorer la date… les jours entiers." Mais,tu es.
Des choses comme Prendre le temps en mathématiques sont dans ce livre: LIVe: Les 54 meilleurs conseils Excel de tous les temps. Cliquez sur ce "I" dans le coin supérieur droit pour le vérifier.
Cela ressemblait à une question si simple; Je ne peux pas croire que nous sommes déjà dans 7 minutes. Mais, une entreprise veut facturer en quart d'heure et elle veut toujours arrondir au quart d'heure suivant. Donc, 24 heures par jour, 24x4 = 96; 15 minutes = 1/96. Utilisez donc PLAFOND (A2,1 / 96) pour arrondir au quart d'heure supérieur suivant. Utilisez MROUND (A2,1 / 96) pour arrondir au quart d'heure le plus proche, puis utilisez FLOOR (A2,1 / 96) pour arrondir au quart d'heure inférieur - revient toujours en arrière. Il y a 24 heures dans une journée, donc vous pouvez faire 1/288 pour obtenir des périodes de 5 minutes, 1/240 pour obtenir des périodes de 6 minutes, 1/1440 pour arrondir à la minute la plus proche. Et puis, le conseil bonus à la fin (quel format de nombre) - vous voulez toujours revenir à ce 37:30:55 si vous allez afficher des heures au-delà de 24 heures.
Pour télécharger le classeur à partir de la vidéo d'aujourd'hui, visitez cette URL dans la description Youtube.
Je tiens à vous remercier de votre visite; nous vous reverrons la prochaine fois pour un autre netcast de.
Hey! Merci d'être resté. Mais, juste un autre bonus ici (que je ne vais même pas prendre la peine de mentionner). Ce avec quoi j'ai commencé ici - clarifions tout cela - une petite formule qui est venue et qui a indiqué l'une de ces décimales - donc, le quart d'heure le plus proche. Mais, je voulais montrer que pas comme le 0.010417. C'est génial - dans le formatage des nombres, il y a en fait quelque chose qui s'appelle Fractions, et si je choisis "Jusqu'à deux chiffres", je vais obtenir 1/96; mais, je dois gérer ce 1/288, donc je choisis "Jusqu'à trois chiffres", et il me montre toujours 1/96; mais, alors quand je me convertis à celui-ci - le 1/288 - il le montrera correctement. Maintenant, je suppose que j'aurais dû faire "quatre chiffres" pour gérer le 1/1440. Qu'est-ce que ça va faire? Ça va me donner… Mais,si je vais dans le format numérique, que je vais sur Personnalisé, et que j'ajoute simplement un point d'interrogation supplémentaire, nous arriverons au 1/1440 de fraction le plus proche. Format numérique - chose cool, cool.
Télécharger le fichier Excel
Pour télécharger le fichier excel: round-to-quarter-hour.xlsx
Excellente pensée du jour
J'ai demandé à mes amis Excel Master leurs conseils sur Excel. Pensée d'aujourd'hui à méditer:
"Les amis ne laissent pas leurs amis utiliser INDIRECT"
Jordan Goldmeier
