Heures à jours ouvrables

Où je travaille, je ne suis pas payé les heures supplémentaires, mais je accumuler des jours de vacances pour les heures supplémentaires que je travaille. J'ai la feuille de calcul suivante qui calcule la quantité d'heures supplémentaires que j'ai fait et les totaux dans D15.

Maintenant, je veux calculer le nombre de jours c'est sur la base de 8 heures par jour. Dans D16, j'ai fait =D15/8 et formaté comme h.mm \d\a\y\s, mais cela montre que 2.26 days au lieu de 2.4375 days.

Quelle est la bonne formule à utiliser dans D16?

Heures à jours ouvrables

Quels sont vos formules dans le mémorandum D3-13, puis dans D15?
D3-D13: =(B1-IF(B3="",B1,B3))+(IF(C3="", C1, C3)-C1) (remplacer 3 avec numéro de ligne) D15: =SUM(D3:D14). C'est à peu près hors de propos, parce que vous pouvez entrer quelque chose dans une cellule de test et de le formater comme hh:mm pour obtenir un "simulacre" de D15 cellule.
Vous pouvez trouver ce lien utile cpearson.com/excel/overtime.htm Il a aussi quelques Ressources Supplémentaires: vers le bas.
Le B$1 astuce sera parfait, c'était vraiment m'énerve que je ne pouvais pas copier la formule vers le bas. Sur la 2ème question, je ne suis pas au bureau à l'heure, mais avez-vous soin de me dire ce qui se passe? Merci!
Ah, si le temps total (D15) va plus de 24 heures, il se remet à 0. Vilain bug! Comment puis-je résoudre ce problème? Merci pour le B$1 truc bien, cela a parfaitement fonctionné!

OriginalL'auteur Danny Beckett | 2012-09-07

  1. 5

    Note au lecteur: cette question conduit à des solutions multiples, dont certains ont été débattus dans les commentaires. Voici un résumé de la solution trouvée.

    Première solution

    =(HOUR(D15)+MINUTE(D15)/60)/8

    Explication

    Dates et heure dans Excel sont stockées comme des numéros de série, donc 19:30 est en fait 0.8125.

    Donc, si vous le divisez par 8, vous obtiendrez 0.1015625.

    Cette dernière valeur est intéressant de 2.26 days

    OP version (merci à Danny Becket (OP)) - voir les commentaires ci-dessous.

    Cette solution gère maintenant les heures > 24.

    =((DAY(D20)*24)+HOUR(D20)+(MINUTE(D20)/60))/8

    ou mieux (crédits de Barry Houdini):

    =((INT(D20)*24)+HOUR(D20)+(MINUTE(D20)/60))/8

    L'ancienne formule a une limitation pour les grandes valeurs, peut-être pas pertinente ici, mais si D20 est de 800:00 puis, vous obtenez une réponse incorrecte (7 jours au lieu de 100 jours). C'est probablement parce que DAY fonction est de vous donner calendrier jour "reset" à 31, le meilleur à utiliser INT en place de DAY.

    L'autre facilement compréhensible version

    Diviser par la longueur de la journée comme une valeur de temps:

    =D15/"8:00"

    Plus facilement changé si la durée de la journée de travail des changements

    Devrait probablement être HOUR(D15) pour un anglais-version Excel.
    Désolé, merci @HansKesting pour la correction. Je reçois souvent des questions au moment de répondre pour les formules sur Stackovervlow parce que mon Excel n'est pas en anglais.
    merci. J'ai édité la réponse de sorte qu'elle reflets de la solution finale
    N'a pas pnuts le suggérer dans les commentaires quelque part d'autre (?), mais pourquoi pas =D15*3, ou plus compréhensible (et plus changer facilement si la durée de la journée de travail, changements), diviser par la longueur de la journée comme une valeur de temps, c'est à dire =D15/"8:00", dans les deux cas qui vous donnerait le même résultat que la accepté de répondre à
    Je n'ai pas envie de "voler" de ce qui était essentiellement pnuts suggestion.....Notez que =((JOUR(D20)*24)+HEURE(D20)+(MINUTES(D20)/60))/8 a aussi une limitation pour les grandes valeurs, peut-être pas pertinente ici, mais si D20 est de 800:00 puis, vous obtenez une réponse incorrecte (7 jours au lieu de 100 jours). C'est parce que le JOUR de la fonction est de vous donner calendrier jour "reset" à 31, mieux utiliser des INT en place de la JOURNÉE, je pense

    OriginalL'auteur

  2. 2

    Entrée:

    dans B3 8:3

    dans C3 16:3

    dans D3 =IF(B3<C3,C3-B3-1/3,2/3-B3+C3)

    Sélectionnez B3:D3, format hh:mm et copier vers le bas aussi loin que nécessaire.

    Somme ColumnD et ajouter *3 à la formule, mais comme format de Nombre.

    Ajouter des données en remplaçant les cellules dans ColumnB et/ou ColumnC comme l'exige (par défaut ne pas ajouter au total).

    • Se débrouille avec les heures supplémentaires jusqu'à la prochaine heure de début (c'est à dire y compris au-delà de minuit, le nouveau numéro de série). 1/3 parce que la journée normale de travail est de 8 heures (24 heures, c'est l'unité pour la date de série du compteur). B3 et C3 pourrait être codé en dur, mais (i) il n'est pas nécessaire et (ii) permet une plus grande flexibilité. Si d'identifier facilement les non standard de départ/arrivée, pourraient utiliser une mise en forme conditionnelle.

    • N'a pas d'adresse week-end, heures supplémentaires, mais pourrait facilement être adapté à le faire (par exemple, ajouter une colonne, indicateur jour de week-end avec 8 dans cette colonne puis ajouter que 8 [1/3] à l'heure de fin).

    Intéressant, mais pas très facile à lire. +1 pour le 24 heures de résolution de problèmes
    Pourtant, je pense qu'il est plus lisible de cette façon 🙂

    OriginalL'auteur pnuts

  3. 1

    Assurez-vous que D15 a un certain nombre de format [h]:mm
    alors D16 =somme(D15/"8:00") devrait fonctionner correctement
    c'est ce que j'ai suivi de mon congé annuel, je travaille 37h pw avec un jour de congé être classés comme 7h24m ou une demi-journée de congé comme "3:42"

    J'ai congé pris comme un chiffre cumulatif affecté en tant que [h]:mm dans la cellule K2 de ma feuille de calcul
    puis j'ai K3=SOMME(K2/"7:24") pour les jours pris formaté comme un nombre général

    vous pouvez aussi avoir besoin de changer la date donnée dans excel pour le calendrier depuis 1904 http://support.microsoft.com/kb/182247/en-gb
    pour obtenir que cela fonctionne (seulement un problème si vous avez le temps négatif comme je le fais lors du calcul de flex heures)

    OriginalL'auteur Daniel Smith