PatCatNat’s, site de Patrice MOREL
Vous êtes ici : Accueil » Inform@tic » Excel ou Calc » Date, heure

Date, heure

D 7 novembre 2006     H 15:37     A Patrice MOREL    


 Bogue à l’exportation de dates de Access vers Excel


Les cellules sont bien formatée en Date, mais les calculs ne se font pas !
Il faut multiplier par 1 la cellule pour obtenir un bon Format.

Exemple : En A1 la date ne fonctionne pas, en A2 mettre =A1*1 et faire Copier en A2 puis collage spécial/Valeur sur cette même cellule.


 Calendrier perpétuel avec mois sur plusieurs onglets


(en téléchargement en bas de page)


 Trouver les dates mobiles (jours fériés)


(Astuces et code VBA pour Excel)

Les jours fériés sont de deux sortes :
Jours à date fixe (comme le 14 juillet, le 11 novembre et le 25 décembre)
Jours mobiles, dont la date varie d’une année sur l’autre. Ces jours fériés mobiles sont calculés d’après la date du dimanche de Pâques.
Dès lors que l’on connaît la date du dimanche de Pâques, on peut en dériver les dates de tous les jours fériés mobiles par simple addition.
Le dimanche de Pâques est le premier dimanche qui suit la première pleine lune après l’équinoxe de printemps (21 mars).
La formule suivante, signée Norbert Hetterich, renvoie la date du dimanche de Pâques pour l’année A (entre 1900 et 2078) :
=FRANC((JOUR(MINUTE(A/38)/2+55)&"/4/"&A)/7;)*7-6
Les jours fériés mobiles en France et en Belgique sont le Lundi de Pâques (Dimanche de Pâques (« DP ») + 1),
le Jeudi de l’Ascension (DP + 39) et le Lundi de Pentecôte (DP + 50).
En partant de la formule de N. Hetterich, on peut donc obtenir facilement les dates de ces trois jours fériés pour une année donnée (entre 1900 et 2078) :

Dimanche de Pâques
=FRANC((JOUR(MINUTE(A/38)/2+55)&"/4/"&A)/7;)*7-6

Lundi de Pâques
=FRANC((JOUR(MINUTE(A/38)/2+55)&"/4/"&A)/7;)*7-5

Jeudi de l’Ascension
=FRANC((JOUR(MINUTE(A/38)/2+55)&"/4/"&A)/7;)*7+33

Lundi de Pentecôte
=FRANC((JOUR(MINUTE(A/38)/2+55)&"/4/"&A)/7;)*7+44


 Les numéros de semaines

Particularité du choix de numéro de semaine :

Pour la norme américaine : la semaine 1 est celle qui inclut le 1er janvier. Le logiciel informatique Microsoft Excel© est américain !
Pour la norme européenne (ISO [1]), la semaine 1 est la première semaine qui comporte au moins 4 jours dans l’année : il faut que le 1er janvier soit au plus tard un jeudi pour qu’il soit en semaine 1.
Certaines années les semaines 1 des deux normes ne sont donc pas les mêmes (par exemple en 2006, le 1er janvier était, selon la norme européenne, en semaine 52 de l’année 2005...).

Une formule informatique spécifique est appliquée pour obtenir un numéro de semaine au sens « ISO » :

La formule est de Daniel Maher (trouvée sur http://excelabo.net/excel/semaines.php
) pour obtenir à partir d’une date le numéro de semaine à la norme « européenne » (la date étant en F$3) :

=1+ENT(MIN(MOD(F$3-DATE(ANNEE(F$3)+{-1;0;1};1;5)+JOURSEM(DATE(ANNEE(F$3)+{-1;0;1};1;3));734))/7)


A partir du n° de semaine trouver la date de début et la date de fin :

=DATE(ANNEE(AUJOURDHUI());1;-6)+D6*7
D6 est la cellule du numéro de semaine.
-6 est le jour de la semaine.


ou

=DATE(A6;1;-6)+D6*7
D6 est la cellule du numéro de semaine.
A6 est la cellule de l’année.
-6 est le jour de la semaine.


 Temps écoulé entre 2 heures

=MOD(A1-B1;1)

  • A1 = 22:00 et B1 = 20:00 le résultat sera 2:00
  • A1 = 1:00 et B1 = 21:00 le résultat sera 4:00

 Format mois

  • En A1 12 sept 2002

=TEXTE (MOIS("A1");"00")
a pour résultat : 09

=TEXTE (A1;"mm")
a pour résultat : 09

=TEXTE (A1;"m")
a pour résultat : 9

=TEXTE (A1;"mmmm")
a pour résultat : septembre

=MAJUSCULE(TEXTE (A1;"mmmm"))
a pour résultat : SEPTEMBRE

=NOMPROPRE(TEXTE (A1;"mmmm"))
a pour résultat : Septembre

=NOMPROPRE(TEXTE (A1;"mmm"))
a pour résultat : Sept

=SI(JOURSEM(A1)=7;"SA";SI(JOURSEM(A1)=1;"DI";SI(JOURSEM(A1)=2;"LU";SI(JOURSEM(A1)=3;"MA";SI(JOURSEM(A1)=4;"ME";SI(JOURSEM(A1)=5;"JE";"VE"))))))
a pour résultat : JE (le 12 septembre est un Jeudi).


- Autres :

=TEXTE ("12 septembre 2002";"mm")
a pour résultat : 09

=TEXTE (MOIS("MAINTENANT()");"00")
si nous sommes en décembre, a pour résultat : 12

=TEXTE (A1;"00")&"/"&TEXTE (A2;"00")&"/"&TEXTE (A3;"0000")
en A1 le jour (exemple 2)r, en A2 le mois (exemple 11), en A3 l’année (exemple 2020), a pour résultat : 02/11/2020

Avertissement : il faut absolument que le format soit de date entière. Si il n’y a que un chiffre pour signifier la date ce la ne fonctionne pas ; par exemple si il il y a une cellule pour le jour, une autre pour le moi et une autre pour l’année, en ne prenant que la cellule du mois, cela ne retournera pas le mois en lettres ! Il faut alors simuler une date (par exemple pour retrouver le mois de mars à partir du chiffre 3 en A2, on peut faire :

=TEXTE("01/"&A2&"/1900";"MMMM")

Pour aller plus loin :
La problématique des dates quand les années , mois et jours sont chacun dans une cellule est d’assembler tout çà de façon cohérente ! Vous trouverez les exemples concret dans le fichier (Libre Office, mais cela fonctionne aussi sous Excel...) ci-dessous en téléchargement.
Bien entendu il faut tenir compte qu’il peut manquer un des éléments (sauf l’année qui, en cas de doute, peut être mise entre crochet - norme archivistique).

Détails (A1=l’année - B1=le mois - C1=le jour - D1 = Le journal) :

  • Si c’est la date complète, pas de problème, il faut concaténer les 3 cellules, la formule est :


    Donne : 24/05/2021.

  • Si il manque le jour, il faut que le mois soit en lettres avec un article partitif (Le journal de mai 2021), la formule est :
    =D1&" de "&TEXTE("1/"&B1&"/"&A1;"MMMM")&" "&A1
    Donne : Le journal de mai 2021
    _Donc à encadrer avec la condition qu’il n’y ait pas de jour connu :

par contre il y a des mois avec une voyelle en début, donc il faut un « d’ » comme article partitif ; comme avril(4), août (8) et octobre (10) !
La condition de mettre de ou d’ est donc définie comme telle :

SI(OU(B1=4;B1=8;B1=10);" d'";" de ")</code). Nous remplaçons le «de» isolé de la formule par cette nouvelle formule comme suit :
<cadre>=D1&SI(OU(B1=4;B1=8;B1=10);" d'";" de ")&SI(C1="";TEXTE("1/"&B1&"/"&A1;"MMMM")&" "&A1;C1&"/"&B1&"/"&A1)</cadre>
_ Donne : {Le journal {{de}} mai 2021} ou suivant le mois {Le journal {{d'}}avril 2021}.

-* Si il manque le jour et le mois, il ne faut que l'année d'affichée toujours avers son article partitif ({Le journal de 2021}), la formule simple est <code>=SI(ET(B1="";C1="");D1&" de "&A1)

. En cours de développement...


 Nombre de jours dans le mois

=JOUR(DATE(ANNEE(F12);MOIS(F12)+1;0))
F12 étant la cellule où se trouve la date (dans mon boulot : j/m/aaaa)


 Ajouter un nombre de mois à une date

En D18 : 21/01/2022
En B18 le nombre de mois : 12

Le -1 permet d’afficher moins 1 jour

Le résultat renvoie : 20/01/2023


 Ajouter un nombre d’années à une date

En C3 : la date initiale
En B3 : le nombre d’années

=DATEVAL(JOUR(C3)&"/"&MOIS(C3)&"/"&ANNEE(C3)+B3)-1

Note : le « -1 » en fin de ligne enlève 1 jour afin d’être plus juste.

Exemple d’utilisation : faire un tableur avec le dernier jour de garantie d’un achat.



[1Qu’est-ce qu’un numéro de semaine au sens « ISO » ?
Selon cette définition, la semaine 1 d’une année donnée est la première semaine de cette année qui comporte au moins 4 jours, c’est à dire qui commence au plus tard un jeudi.

  • Si le jour de l’an est antécédent à jeudi ou jeudi, le jour de l’an se situe dans la semaine 1
  • Sinon il se situe dans la dernière semaine de l’année précédente, et la semaine 1 de l’année en cours est la semaine qui suit.

Exemple :
Le premier numéro est en europe, l’autre aux USA
Num semaines | Europe | USA |

  • jeudi 1 janvier 2004 | 1 | 1 |
  • samedi 1 janvier 2005 | 53 | 1 |
  • dimanche 1 janvier 2006 | 52 | 1 |
  • lundi 1 janvier 2007 | 1 | 1 |
  • mardi 1 janvier 2008 | 1 | 1 |

On voit tout de suite que la donnée de base de ce calcul est la date du lundi qui précède le premier jeudi de l’année. En effet, c’est ce lundi-ci qui est le « jour de l’an » au sens « semaine ISO ».

Dans la même rubrique

12 mai 2021 – Un lien entre les cellules

12 novembre 2019 – Formatages divers

4 janvier 2018 – Incrémentation de lignes avec un classeur lié.

3 août 2017 – Nom de fichier dans une cellule

14 mars 2012 – Date par défaut

Rechercher

Traducteur



Les plus lus

1.  Notices diverses

2.  Le jeu de palets

3.  Expos et réalisations

4.  Import tous fichiers Txt d’un répertoire

5.  Au fil de l’eau


5 articles au hasard

1.   Cérémonie commémorative à Souge

2.  L’Homme parle - La Crise

3.  La date du jour

4.  Affiche « Affiche rouge 50 otages Nantes-Châteaubriant-Mont Valérien »

5.  Politique routière


Les plus populaires

1.  Multiplier des artichauts !

2.  Supprimer la pub de PDF Architecte

3.  Référencement

4.  Réflexion !

5.  En toute transparence !