Archives par étiquette : Date/Heure

Excel – Créer un planning avec mise en forme conditionnelle

Contexte

Vous souhaitez créer un tableau mensuel avec les absences (congé, cours, …) des membres de votre équipe. Chaque début de mois, vous aimeriez pouvoir rapidement créer un nouveau mois et souhaiteriez que les week-ends apparaissent automatiquement en couleur :

planning

Remarquez dans mon exemple que la cellule B3 contient une date saisie 01.04.2016 mais affiche un format personnalisé de type Ven 1 (jjjj j).

Solution : utiliser la mise en forme conditionnelle

  • Dans l’exemple ci-dessus, placez-vous sur la cellule B4 (sur la 1ère case en face de la première personne)
  • Cliquez sur le bouton Mise en forme conditionnelle de l’onglet Accueil > Nouvelle règle
  • Choisissez le style Classique > option Utiliser une formule pour déterminer à quelles cellules la mise en forme sera appliquée
  • Tapez la formule ci-dessous :

=OU(JOURSEM(B$3;2)=6;JOURSEM(B$3;2)=7)

Qu’il faut comprendre ainsi :

JOURSEM = formule qui renvoie un chiffre entre 1 et 7 désignant le jour de la semaine

B$3 = adresse de la cellule qui contient la 1ère date (Ven 1). La colonne n’est pas rendue absolue puisque nous souhaitons pouvoir recopier cette formule sur les autres jours du mois; lors de la recopie sur la droite les adresses des colonnes doivent donc s’adapter. Par contre, le numéro de la ligne est rendu absolu puisque les dates du mois sont situées sur la ligne 3 (et jamais sur la 4, la 5…); les adresses des lignes ne doivent donc pas s’adapter

;2 = cet argument permet d’indiquer à Excel que la semaine chez nous commence un lundi (les américains considèrent que le premier jour de la semaine est dimanche). 1 = dimanche, 2 = lundi…

=6 et =7 : indiquent que le 6ème jour de la semaine devront être mis en forme, le 6ème étant le samedi, le 7ème étant le dimanche… chez nous

OU en début de formule indique que les 6ème et 7ème jours de la semaine doivent être mis en forme de manière conditionnelle (en langage courant nous disons ET mais pour Excel il s’agit bien d’un OU)

  • Choisissez ensuite la mise en forme souhaitée et validez
  • Recopiez ensuite la cellule B4 avec la poignée de recopie en glissant sur la droite jusqu’au dernier jour du mois
  • Recopiez ensuite cette plage de cellules sur le bas jusqu’au dernier collaborateur

Beaucoup de travail pour pas grand chose me direz-vous ? Certes, mais si maintenant vous souhaitez créer en 5 secondes chrono le planning du mois prochain :

  • En B3, saisissez la date du 1er jour du mois (01.05.2016) et recopiez cette information jusqu’au dernier jour du mois et Tadaaaannnn ! les week-end de votre nouveau planning se sont mis à jour.

FileMaker – Calculer l’âge d’une personne

Contexte

Vous utilisez FileMaker pour gérer des personnes (employés, doctorants..). Vous avez créé une rubrique « Date de naissance » de type Date et aimeriez que FileMaker calcul l’âge de la personne dans une autre rubrique.

Solution

  • Créez une rubrique appelée Age de type Calcul
  • Indiquez pour cette rubrique le calcul suivant :
    Ent ( (Obtenir ( DateActuelle ) – Date de naissance) / 365.25)
  • Indiquez si nécessaire que le résultat du calcul doit être de type Nombre
  • Insérez cette nouvelle rubrique dans votre modèle

Ent = cette fonction supprime les décimales et renvoie la partie entière du nombre sans arrondi
Obtenir (Date actuelle) = Renvoie la date du jour en fonction du calendrier du système
365.25 = représente la longueur moyenne d’une année

Excel – Calculer le nombre de jours de travail

Contexte

Vous planifiez un projet dans Excel et souhaiteriez calculer le nombre de jours de travail (ouvrés) situés entre deux dates, par exemple entre aujourd’hui (le 17.09.2014) et la fin de l’année (le 31.12.2014). Or, entre ces deux dates se trouvent des jours fériés et des week-ends durant lesquels les ressources ne travaillent pas.

Solution

  • Entrez dans la cellule A1 la date de départ (par exemple la date d’aujourd’hui que vous saisissez manuellement ou à l’aide de la fonction =aujourdhui() … à vous de voir
  • Dans la cellule A2, tapez la 2ème date (par exemple le 31.12.2014)
  • Dans les cellules B1, B2 et B3, tapez les jours fériés jusqu’à la fin de l’année soit les 22.09.2014, 25.12.2014 et 26.12.2014
  • Dans la cellule A3, tapez la formule =NB.JOURS.OUVRES
  • Entre les parenthèses, indiquez la date de départ (A1), tapez un point-virgule puis désignez la date de fin (A2)
  • Dans la formule, après l’indication A2, tapez un point-virgule et sélectionnez les cellules contenant les jours fériés
  • Votre formule doit donc ressembler à ceci : =NB.JOURS.OUVRES(A1;A2;B1:B3)

Résultat obtenu : 73 jours ouvrés (en comptant aujourd’hui)…. allez, courage 😉

Excel – Planifier un projet

Contexte

Vous planifiez un projet dans Excel et avez prévu plusieurs colonnes pour les dates liées aux tâches du projet (date de début de la tâche, date de fin de la tâche et date de suivi). Étant donné que les tâches ne sont pas toutes terminées (certaines cellules « date de fin » sont vides), vous souhaitez pouvoir calculer la différence entre la date de début et la date de suivi mais seulement en l’absence d’une date de fin. Vous avez prévu d’entrer les dates manuellement; seule la différence entre les dates doit être calculée.

Solution

Utilisez la fonction* DATEDIF, sachant que dans mon exemple :

  • Le nom de la tâche est en A2
  • La date de début de la tâche est en B2
  • La date de fin (s’il y en a une) est en C2
  • La date de suivi prévue est en D2
  • La formule de calcul se trouve en E2
    =SI(C2= » »;DATEDIF(B2;D2; »d »);DATEDIF(B2;C2; »d »)) & » jours »

Autrement dit : Si la cellule C2 est vide, Excel doit calculer la différence entre B2 et D2. Cette différence doit être exprimée en jours (argument “d”). Si C2 n’est pas vide, Excel doit calculer la différence entre B2 et C2. Cette différence doit également être exprimée en jours. L’indication « & jours » à la fin de la formule fera apparaître le mot « jours » dans le résultat du calcul afin d’éviter une mauvaise interprétation du résultat :DateDifetSI

* La particularité de cette fonction est qu’elle ne figure pas dans la liste officielle des fonctions d’Excel ;)

Word – Format de date erroné

Contexte

Vous souhaitez insérer une date automatique dans un modèle de lettre dans Word (Insertion > Date et heure). Vous aimeriez un format qui inclut le mois en lettre; vous choisissez donc « 21 mai y ». Après validation, vous constatez que la date indique réellement « 21 mai y ».
Le coupable est en fait la version de votre système Mac… et non pas Word (les OS après 10.6.8 présentant ce problème).

Solution

  • Faites un clic droit sur la date automatique que vous avez insérée
  • Choisissez la commande Basculer les codes de champs;
    vous obtenez alors l’information suivante : { TIME \ @ « d MMMM y »}
  • Compléter ce code avec 3 « y » afin d’obtenir ceci : { TIME \ @ « d MMMM yyyy »}
  • Faites un clic droit sur le champ et choisissez Basculer les codes de champs
  • Refaites un clic droit sur le champ et choisissez Mettre à jour les champs
Dans le cas où le format de date est erroné dans les bulles des marques de révision ou des commentaires figurant dans la marge d’un document révisé, la solution ci-dessus ne fonctionne pas.

Excel – Calculer la différence entre deux dates

Contexte

Vous avez saisi dans Excel deux dates, par exemple la date d’entrée en service (jour.mois.année) d’un collaborateur ainsi que la date de son départ à la retraite (jour.mois.année). Vous souhaiteriez obtenir dans une autre cellule le nombre d’années séparant ces deux dates. Vous avez pensé faire une simple soustraction mais le résultat obtenu, bien que correct, ne vous satisfait pas quant à son apparence.

Solution

  • Utilisez la fonction* DATEDIF sachant que :
  • la date d’entrée en service se trouve par ex. dans la cellule C8
  • la date de départ à la retraite se trouve par ex. dans la cellule D8
  • alors votre formule de calcul doit être : =DATEDIF(C8;D8; »y »)

L’argument « y » indique dans quelle unité doit être calculée la différence. Si vous saisissez « m » ou « d » à la place du « y », vous obtiendrez respectivement un résultat sous forme de mois ou de jours.

* La particularité de cette fonction est qu’elle ne figure pas dans la liste officielle des fonctions d’Excel 😉

Excel – Ajouter le jour de la semaine à une date

Contexte

Vous avez créé un tableau comportant une colonne dans laquelle vous avez saisi des dates (01.05.2014, etc…) et vous souhaitez y ajouter le jour de la semaine (jeudi 01.05.2014).

Solution

  • Sélectionnez la plage de cellules dans laquelle vous avez saisi les dates
  • Allez dans Format > Cellule > Personnalisée et créez le format personnalisé suivant :
    jjjj jj mm aa
  • Validez… et c’est tout; Excel ajoutera le jour de la semaine correct, quel que soit la date saisie dans les cellules

Remarquez que des calculs sur ces dates restent possibles puisque les jours de la semaine n’ont pas été écrits « en dur » mais font partie du formatage.

Excel – Additionner des heures et des minutes

Contexte

Vous avez créé un tableau comportant une colonne dans laquelle vous avez saisi des heures et des minutes (8:30, 7:20, 9:15… pour inscrire par ex. vos horaires de la semaine). Vous prévoyez de faire une addition des heures au bas du tableau. Problème : l’addition (=Somme…) renvoie un résultat erroné.

Solution

  • Cliquez sur la cellule dans laquelle vous avez inséré la fonction Somme
  • Allez dans Format > Cellule > Personnalisée et créez le format personnalisé suivant : [h]:mm
  • Validez… et c’est tout; ce format permet à Excel de ne pas recommencer à 1 lorsqu’il dépasse la 24ème heure

PS : les symboles [ ] s’obtiennent grâce aux touches Alt + 5 et Alt + 6

Excel – Aligner les jours de la semaine dans un format de date

Contexte

Vous avez créé un tableau comportant une colonne dans laquelle vous avez saisi des dates avec un format personnalisé de type jjjj jj mm aa afin d’obtenir automatiquement les jours de la semaine en texte. Problème : vous aimeriez que les jours de la semaine soient alignés à gauche de la cellule (puisqu’il s’agit de texte) alors que les dates doivent être alignées à droite.

Solution

  • Sélectionnez la plage de cellules dans laquelle vous avez saisi les dates
  • Allez dans Format > Cellule > Personnalisée et créez le format personnalisé suivant :
    jjjj* jj mm aa. Remarquez qu’il y a un espace après le *
  • Validez

Bon à savoir : le symbole * suivi d’un espace répète l’espace autant de fois qu’il est nécessaire en fonction de la longueur du mot (lundi, vendredi)… Résultat, les jours de la semaine sont « poussés » à gauche de la cellule alors que les dates restent alignées à droite.

FileMaker – Année à 4 chiffres – Retour vers le futur

Problème

Vous avez tapé la date du 27/02/44 dans une rubrique de type Date et FileMaker l’a convertie en 27/02/2044. Vous tapez ensuite sur une autre fiche la date du 27/02/45 et FileMaker l’a convertie en 27/02/1945. Pourquoi ?!?!?

Il faut savoir qu’en l’absence d’une année à 4 chiffres saisie par l’utilisateur, FileMaker converti automatiquement l’année à 2 chiffres en année à 4 chiffres en tenant compte de l’année de la saisie et en supposant que l’année à 4 chiffres doit se situer au cours des 30 années dans le futur ou des 70 années dans le passé…. mmmhhh ?!?!?

Exemple : si le 14.04.2014 (date de rédaction de cet article), vous saisissez la date du 27.02.44, FileMaker considère que la date la plus proche est le 27.02.2044 (se situant donc dans la plage des 30 années dans le futur).
Si le même jour, vous tapez la date du 27.02.45 (se situant donc hors de la plage des 30 années dans le futur), FileMaker considère que cette date appartient au passé et lui attribue l’année 27.02.1945.

Solution

Les années à 2 chiffres sont donc ambigües et sujettes à une conversion erronée de la part de FileMaker. Moralité : il faut toujours saisir des années à 4 chiffres. Pour cela, obligez les utilisateurs à saisir une année à 4 chiffres dans les options de la rubrique Date, onglet Validation :

annee