Fonctions date & heure d’Excel (b)

Dans le dernier article, nous avons passé en revue les fonctions historiques de date et heure d’Excel. Aujourd’hui, nous nous intéressons aux fonctions de date et heure ajoutées par l’Utilitaire d’analyse. 

Le tableau ci-dessous illustre leur utilisation :

Leur syntaxe est la suivante :

=fin.mois(date_départ;mois)

=fraction.annee(date_début;date_fin[;base])

=nb.jours.ouvres(date_départ;date_fin[;jours_fériés])

=no.semaine(numéro_de_série[;méthode])

=serie.jour.ouvre(date_départ;nb_jours[;jours_fériés])

Nous avons vu dans le dernier article comment fonctionnait l’argument facultatif jours_fériés

Remarque 1 – La syntaxe dans l’aide d’Excel pour ces fonctions est aussi foireuse que d'habitude. Ainsi, pour la troisième fonction, elle nous donne =NB.JOURS.OUVRES(date_début, date_fin, [jours_fériés]) : des "," au lieu des ";" et un crochet mal placé !

Remarque 2 – L’argument facultatif base fonctionne de la façon suivante : rien ou 0 pour le comptage 30/360 US, 1 pour le comptage réel, 2 pour le réel/360, 3 pour le réel/365 et 4 pour le 30/360 européen.

Fonctions date & heure d’Excel (a)

Cet article fait suite aux trois articles récents relatifs aux dates dans Excel. Si vous ne les avez pas encore lus, nous vous conseillons de commencer par les étudier avant de lire cet article.

« Saisie de dates dans Excel », le 30 juin 2014

« La grand-mère de Bill Gates », le 5 juillet 2014

« Bascule entre les bases de dates », le 11 juillet 2014

Il y a deux familles de fonctions de date et heure dans Excel : les fonctions historiques, présentes dans Excel depuis très longtemps, et les fonctions de « l’Utilitaire d’analyse » , qui est – à l’instar du Solveur – un add-in livré avec Excel mais non installé par défaut.

Or, à présent – je ne sais plus depuis quelle version –, les fonctions de date et heure qui étaient présentes dans « l’Utilitaire d’analyse » ont été intégrées à Excel et sont reconnues même quand cet utilitaire n’est pas installé.

Le tableau ci-dessus illustre l'utilisation des fonctions de date et heure historiques d’Excel, c’est-à-dire en ignorant celles ajoutées au départ par l’utilitaire d’analyse et intégrées depuis. Nous verrons ces fonctions dans le prochain article.

La colonne B affiche les formules utilisées en colonne A. Nous avons aussi mis en commentaire de la cellule A9 la véritable valeur de cette cellule, le contenu actuel de la cellule étant dû au format jjj jj/mm/aaaa hh:mm:ss.

Remarque 1 – On note en A4 que dateval() ignore l’heure en argument, et en A14 que tempsval() ignore la date en argument.

Remarque 2 – La fonction jours360() possède un troisième argument facultatif dont vous pourrez voir le mode d'emploi dans l'aide d'Excel.

Un énorme bug des tables…

Nous avons les tables depuis Excel 2007. Et pourtant, c’est tout récemment qu’un membre bien connu de la communauté des MVPs Excel, Zack Barresse, y a découvert un bug important. Il a été testé sur Excel 2010 et 2013, mais je n’ai plus de 2007 sous la main pour vérifier s’il y était déjà présent. Ceci dit, a priori, on ne voit pas pourquoi il ne serait apparu qu’avec la version 2010…

Nous avons en A1:B6 (table 1) et en F1:G6 (table 2) deux tables. Le bloc en F8:G13 (bloc 3) possède la même structure, mais n’est pas défini comme table. Dans les trois cas, seules les valeurs de la seconde colonne diffèrent.

En outre, en D2, nous avons une formule faisant référence à la table 1.

Copie des valeurs du bloc vers la table 1

Commençons par copier le bloc F8:G13, à sélectionner la table 1 et à y coller les valeurs. Tout se passe bien et la cellule en D2 actualise son résultat.

Annulons à présent cette première opération avec [Ctrl]-z.

Copie des valeurs de la table 2 vers la table 1

A présent – mais nous aurions aussi bien pu faire cela en premier – copions la table 2, en F1:G6, sélectionnons la table 1 et collons-y les valeurs. La structure de table de la table 1 disparaît aussitôt et la cellule D2 affiche le message d’erreur #REF !

Autrement dit, le collage des valeurs d’une table vers une autre table tue la table réceptrice ! C’est un bug énorme…

Remarque – Dans la première rédaction de cet article, j'avais dit que l’annulation avec [Ctrl]-z ne marchait pas avec les opérations sur les tables. En fait, cela était dû à un conflit avec un add-in que j'ai depuis désactivé...

Bascule entre les bases de dates

En référence avec l’article précédent, j’ai écrit une toute petite macro pour basculer entre les dates à la Bill Gates et les dates à la Steve Jobs. Un simple clic sur le bouton permet de basculer entre les deux modes. Voici la macro :

Sub Change_base_date()

    ActiveWorkbook.Date1904 = _

        Not (ActiveWorkbook.Date1904)

End Sub

Notez l’utilisation astucieuse de la fonction Not() qui permet d’éviter un bien plus lourd :

Sub Change_base_date()

    If ActiveWorkbook.Date1904 = True Then

          ActiveWorkbook.Date1904 = False

     Else

          ActiveWorkbook.Date1904 = True

     End If

End Sub

Nous avons entré en D1:E4 quelques dates célèbres. Nous voyons que les dates en E1:E2, antérieures à la première date possible (0 janvier 1900 ou 1 janvier 1904, selon la grand-mère) sont prises sous la forme de texte et donc cadrées à gauche par défaut.

Du coup, elles ne bougent pas quand on passe à la base 1904, contrairement aux deux dates en E3:E4.

 

Deux lectures pour mieux comprendre et gérer les dates non reconnues comme telles par Excel…

How to Work with Dates Before 1900 in Excel :

http://exceluser.com/formulas/earlydates.htm

Pre-1900 Dates in Excel :

http://www.pcmag.com/article2/0,2817,2114548,00.asp

La grand-mère de Bill Gates

La gestion des dates dans Excel est un peu particulière. Il faut savoir que – pour Excel – un jour représente une unité et une partie de jour une fraction de cette unité.

Dans le tableau ci-dessus, nous avons entré dans la colonne A, successivement, les formules que voici :

En A2 : =2/3

En A3 : 1,25

En A4 : -1

En A5 : =aujourdhui()

En A6 : =maintenant()

Pour les formules de la colonne B, c’est tout simple. Celle de B2 est « =A2 » et elle a été recopiée jusqu’en B5.

Notez enfin que nous avons attribué aux cellules A4:A5 le format « # ##0,00 ».

Les cinq cellules de la colonne B ont ensuite reçu le format personnalisé indiqué en commentaire.

La première date d’Excel est celle de la date de naissance de la grand-mère de Bill Gates. On savait déjà qu’il était un peu extra-terrestre ; il n’est donc pas surprenant que sa grand-mère soit née un 0 janvier, date originale s’il en est.

On n’a pas le droit de remonter plus haut dans la généalogie de Bill, puisque (cf. A3) , toute date négative affiche une erreur non « typée » : en effet, elle n'est pas sous la forme #VALEUR !, qui aurait été nettement plus logique en la circonstance.

Notez que c’est le seul cas où vous récolterez une série de « # » sans que la largeur de la colonne ne soit en cause.

Nous allons maintenant découvrir que la grand-mère de Steve Jobs, elle, est née le 1er janvier 1904 (lui n’est donc pas extra-terrestre). Pour cela, nous passons par la séquence Fichier – Options – Options avancées et, vers la fin de cet onglet,  nous cochons l’option « Utiliser le calendrier depuis 1904 », qui nous met en mode « Excel Macintosh ».

Voici ce que devient alors notre modèle :

On constate aussitôt une autre étrangeté. Avec ce réglage, les dates négatives aboutissent à une date normale précédée du signe « - ». Allez donc savoir pourquoi !

On remarque en revanche que les dates des lignes 5 et 6 sont toujours bonnes : en effet, ce n'étaient pas des constantes mais des fonctions...

PS – Si vous voulez surprendre un collègue, mettez discrètement son Excel dans le mode «Utiliser le calendrier depuis 1904 » : il ne comprendra plus rien aux dates affichées dans ses modèles…

Remarque – J’ai repris en partie un article publié au début de ce blog, le 14 décembre 2005.