Dates, heures et chrono

Date, Heure et Chrono

Je veux dresser ici un topo rapide de ce que l'on peut faire; vous trouverez presque tout sur les sites suivants :

Des références à visiter :

En français : http://perso.wanadoo.fr/longre/excel/pages/F_DateHeure.htm, un de ses articles tout récent sur les news (26/11)
En anglais : http://www.cpearson.com/excel/, notamment la page datetime.htm
http://members.aol.com/dmcritchie/excel/, notamment les pages datetime.htm & date.htm

Liste d'articles de MICROSOFT

Q95948 XL: Using Dates and Times (XE0127) Complete Text
Q99349 XL: Working with Time Periods That Exceed 24 Hours
Q168435 XL: Maximum Times in Microsoft Excel
Q12593 XL : Equivalences US / FR des fonctions de l 'ATP
Q11267 Comment calculer le nombre de mois entre 2 dates choisies (WD 6.xx)

MANIPULATION DE DATES

Du ............. Au ...............

Du 22/06/99 au 22/09/99
="Du "&TEXTE(A1;"jj/mm/aa")&" au "&TEXTE(B1;"jj/mm/aa")

Du mardi 22 juin 1999 au mercredi 22 septembre 1999
="Du "&TEXTE(A1;"jjjj jj mmmm aaaa")&" au "&TEXTE(B1;"jjjj jj mmmm aaaa")

Connaître l'année en cours(De: Jacques G., objet: Re: Année scolaire, date : samedi 22 juillet 2000 21:17)

="Année scolaire "&ANNEE(AUJOURDHUI())-(MOIS(AUJOURDHUI())<9) &"/"&ANNEE(AUJOURDHUI())+(MOIS(AUJOURDHUI())>8)
ou
="Année scolaire "& SI(MOIS(AUJOURDHUI())<9;ANNEE(AUJOURDHUI())-1&"/"&ANNEE(AUJOURDHUI());ANNEE(AUJOURDHUI())&"/"&ANNEE(AUJOURDHUI())+1)
ou
="Année scolaire "&ANNEE(AUJOURDHUI()-240)&"/"&ANNEE(AUJOURDHUI()-240)+1

Connaître le temps écoulé entre deux dates

Ci-dessous les dates sont inscrites dans A1 & A2
=DATEDIF(A1;A2;"y")&SI(DATEDIF(A1;A2;"y")>1;" ans ";" an ")&DATEDIF(A1;A2;"ym")&" mois "&DATEDIF(A1;A2;"md")&SI(DATEDIF(A1;A2;"md")>1;" jours";" jour")

Ci-dessous on calcule le temps écoulé entre la date du jour et la date située en A1
=DATEDIF(A1;AUJOURDHUI();"y")&SI(DATEDIF(A1;AUJOURDHUI();"y")>1;" ans ";" an ")&DATEDIF(A1;AUJOURDHUI();"ym")&" mois "&DATEDIF(A1;AUJOURDHUI();"md")&SI(DATEDIF(A1;AUJOURDHUI();"md")>1;" jours";" jour")
=DATEDIF(A1;B1;"m")

=ANNEE(DATEDIF(entree;sortie;"d"))-1900&" ans "&MOIS(DATEDIF(entree;sortie;"d"))-1&" mois "&JOUR(DATEDIF(entree;sortie;"d"))-1&" jours"

Connaître un âge 'un exemple de fonction personnalisée en VBA par Pascal E. le 18 novembre 2001 sur microsoft.public.fr.excel, sujet "Re: comment afficher un texte dans une fonction"

'recopier la fonction ci-dessous dans une feuille de code et indiquer une date en A1, et créer la formule suivante en A2 : =age(A1)
Function age(dn)
age = Int((Date - dn) / 365.25)
age = IIf(age < 1, Format(age, "# an"), Format(age, "# ans"))
End Function

Déterminer le dernier jour du mois en cours (28, 30 ou 31) à partir d'une date située en A1

=JOUR(DATE(ANNEE(A1);MOIS(A1)+1;0))

Déterminer le dernier jour d'un mois :

=FIN.MOIS(AUJOURDHUI();0) (au format date)
=TEXTE(FIN.MOIS(AUJOURDHUI();0);"jj mm aa") (au format texte)
=date(annee(AUJOURDHUI();1+mois(AUJOURDHUI();0)

Convertir un nombre de jours (avec décimale) en années, trimestres, mois , jours??

Années : =Nb_jours/365,25
Trimestres : =Nb_jours/91,3125
Mois : =Nb_jours/30,4375
Jours... : =Nb_jours (???)

Calculer le nombre de cellules pour lequel le mois est le 6ième

Par exemple, pour le mois de juin (6) : =SOMME(N(MOIS(Plage)=6))
formule matricielle à valider par la combinaison de touches Ctrl-Maj-Entrée.

Afficher le mois en lettres dans la cellule B1(où A1 contient le numéro du mois)

=CHOISIR(A1;"Janvier";"Février";"Mars";"Avril";"Mai";"Juin";"Juillet";"Août";"Septembre";"Octobre";"Novembre";"Décembre") =DATE(1999;A1;1), formater B1 comme suit : mmmm
=TEXTE(DATE(1999;A1;1);"mmmm")

Retourner le premier jour de l'année pour une date donnée

"Re: date :1er jour de l'année", le 22/07/2000 par Manuel D.
=date(annee(A1)+100;1;1)
=date(annee(A1);1;1)

Si A1 = dimanche ou samedi, alors, sinon! =SI(OU(A1={"Samedi";"dimanche"});"";B1)

Numéro de séries d'une date : news
Une date à partir d'un numéro de série : =DATE(ANNEE(A1);MOIS(A1);JOUR(A1)) ou =TEXTE(A1;"jj.mm.aaaa")
Un jour donné est-il chômé : news
Heures ouvrables entre deux dates : news
Nombre de jours sans les week-end (installer le complément analyse) =NETWORKDAYS(start_date, end_date)

Date de fin selon jours ouvrés

D'un stage sachant qu'il commence le et qu'il dure 1400h à raison de 37h/semaine et que ce stage sera interrompu du 22/12/2001 au 06/01/2002 pour les vacances de noël. Soluce par ChrisV, sujet : "Re: Date de fin de stage", le samedi 12 mai 2001 14:13
=SERIE.JOUR.OUVRE("06/09/2001";1400/(37/5)+NB.JOURS.OUVRES ("22/12/2001";"06/01/2002"))

Nombre de jours entre une date et une autre

Proc an_2000()
rep = DateVal("01/01/2000") - DateVal(Maintenant())
Message "Il reste " & rep & " jours avant l'an 2000."
Fin Proc

Date d'un précédent jour à partir de la date du jour

for i =1 to 7
if WeekDay(Datevalue(Now - i)) = 3 then 'trouver par exemple le dernier mercredi
dernier_mercredi= Datevalue(Now - i)
Exit for
end if
next

Date en anglais dans excel français : news
Ou encore, cette formule à coucher dehors pour le fun. Pour écrire "August, Friday 17 th" [excellent pour s'entraîner au "si", "ou" & choisir], fait par moi-même

=CONCATENER(CHOISIR(MOIS(AUJOURDHUI());"January";"Februar";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December");",";" ";CHOISIR(JOURSEM(AUJOURDHUI());"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday";"Sunday");" ";JOUR(AUJOURDHUI());" ";SI(OU(JOUR(AUJOURDHUI())=1;JOUR(AUJOURDHUI())=21;JOUR(AUJOURDHUI())=31);"st";SI(OU(JOUR(AUJOURDHUI()=2);JOUR(AUJOURDHUI()=22));"nd";SI(JOUR(AUJOURDHUI()=3);"rd";"th"))))

Date selon le calendrier républicain : news

'Tester si une année est bissextile

'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
Public Function bissextile(Mavaleur)
If IsDate(Mavaleur) = True Then
If Month(DateSerial(Year:=Year(Mavaleur), Month:=2, Day:=29)) = 2 Then
bissextile = True
Else
bissextile = False
End If
Else
bissextile = "La valeur en entrée doit etre de type date"
End If
End Function
If MOD((Year(Date);4) = 0 And Month(Date)=2 then
If ([A1] Mod 4 = 0 And [A1] Mod 100 <> 0) Or [A1] Mod 400 = 0 Then [A2] = "bissex"

Saisir la date comme suit 01012000 (avec 4 chiffres pour l'année), puis la retranscrire en format date

de Ritchi 92, sujet "Re: entrer une date en évitant le /", le jeudi 28 septembre 2000 06:18
=DATE(DROITE(A1;4);DROITE(GAUCHE(A1;4);2);GAUCHE(A1;2))

Sélection de la date la plus récente

'cet exemple suppose que les dates sont en C4 et CF4
Sub Tester3()
Set rng = range("C4","CF4")
res = Application.Match(Application.Max(rng), rng, 0)
If Not IsError(res) Then
rng(res).EntireColumn.Select
End If
End Sub

Trouver des dates

Set FoundCell = Range("A1:A100").Find (what:="7/18/1998")
Set FoundCell = Range("A1:A100").Find (what:=DateValue("July 18, 1998") ,lookin:=xlFormulas)

Sélectionner la feuille du mois en cours : Worksheets(Format(Date,"mmmm")).Select

PROBLEMES LORS DE MANIPULATION DE DATES

Excel ne reconnaît pas les dates antérieures à 1900 :

voir site Laurent pour explication
voir cette macro complémentaire de John Walkenbach, qui propose différentes fonctions destinées à compenser ce manque:
http://www.j-walk.com/ss/excel/files/xdate.htm
Voir ces macros complémentaires chez Microsoft :
1. Excel 97 : http://officeupdate.microsoft.com/downloadDetails/Xdate.htm
2. Excel 2000 : http://officeupdate.microsoft.com/2000/downloadDetails/Xdate.htm
J'ai un problème de formule : =SI(AUJOURDHUI()>"31/5/2000";VRAI;FAUX) fonctionne complétement aléatoirement et donne des résultats bizarres si je change des paramètres dans la formule (<, "31/5/2001",>, etc...). J'ai mis la date dans une cellule et là ça marche. Mais tout cele ne m'explique pas les étrangetés de la formule de départ. Solution apportée par Laurent : news

N° de semaine

!!!! =(AUJOURDHUI()-"1/1/1999")/7
Aller sur le site de Laurent, y a presque tout sur le sujet
Une fonction de Laurent
Function NOSEM(D As Date) As Long
D = Int(D)
NOSEM = DateSerial(Year(D + (8 - Weekday(D)) Mod 7 - 3), 1, 1)
NOSEM = ((D - NOSEM - 3 + (Weekday(NOSEM) + 1) Mod 7)) \ 7 + 1
End Function
'retourne le numéro de semaine selon la norme ISO française
'ce numéro est différent de celui retourné par la fonction no.semaine de l'utilitaire d'analyse [qui correspond plus aux calendriers américains]

Dans la norme ISO, si le 1er janvier tombe un vendredi, la semaine 1 commence le lundi suivant (4 janvier), et aux US c'est déjà la semaine 2... Cenla entraîne donc certaines années une confusion dans les rapports clients fournisseurs, si tu vois ce que je veux dire

Créer un calendrier avec excel :

saisir la date dans une cellule, sélectionner celle-ci et des cellules en dessous, puis, edition, recopier, série => chronologique, pas 1 par exemple, puis le type de séries concernés :
=> Mois, Année, Jours, Jours ouvrés
saisir la date, à l'aide du bouton droit de la souris, cliquez sur la croix en bas de la cellule, faites glisser vers le bas, et en relâchant le clic droit, le menu contextuel apparît vous proposant d'incrémenter la date selon les types de séries énoncées ci-dessus.

Il existe évidemment une syntaxe en VBA pour ces créations. Un exemple :
Créer un calendrier de jours ouvrés (sans les week-end). Je ne sais pas comment cela se comporte avec les jours fériés français !

range("A2").FormulaR1C1 = "1/1/00" : range("A2").Select
Selection.AutoFill destination:=range("A2:A13"), Type:=xlFillWeekday

CALCUL DES HEURES :

Passer de 15,75 heures à un format de type 15:45

Si A1 contient 15,75, diviser par 24 en A2 et formater A2 ainsi "h:mm"
ActiveCell = ActiveCell.Value / 24 : ActiveCell.NumberFormat = "h:mm"

Passer de 0::30:00, soit trente minutes, à un format de type pourcentage

Si A1 contient 15:45:00, en B1 tu entres =A1*24 et tu formates en %

Retourner un nombre de minutes là où le temps est saisit en heure (13:20) : =HEURE(A1)*60 + MINUTE(A1)
voilà comment faire l'inverse : =ENT(A1/60)&":"&ENT(MOD(A1;60))

Formule qui calcule un taux horaire (de: Frédéric S., Objet: Re: ??? , date : vendredi 7 juillet 2000 01:41)

Si en A1 tu as le temps et en B1 le prix,
en C1, pour obtenir le prix de l'heure : =60/A1*B1/1440
en D1, pour obtenir le prix de la minute : =60/A1*B1/86400
B1, C1 et D1 étant au format monétaire.

Heure Atomique : news
Autre message sur le calcul en milliseconde : news (même auteur)
Utiliser la fonction DateFormat d'XL 2000 sur XL3 & autres : news

Bien formater la date par VBA & dans les objets

Date dans le nom du fichier (date in filename example) : news
Formater une cellule au format date & y insérer la date de création du fichier

With Range("Feuil1!A1")
.NumberFormat = "dd/mm/yyyy hh:mm:ss"
.Value = ActiveWorkbook.BuiltinDocumentProperties("Creation date")
End With

Contrôler la date saisie dans une textbox :

If not isdate(MaTextbox) then msgbox "Date Invalide", Vbokonly,"Erreur de saisie"
If isdate(TextBox) then
TaCell=format(cdate(TextBox),"dd/mm/yyyy")
else
Toto=Msgbox("C'est pas une date",Vbokonly,"Erreur") :exit sub
end if
Contrôle de la saisie d'une date dans un inputbox : news

Une astuce pour transformer les valeurs d'une plage A1:A2000 en date

With [A1:A2000]
.TextToColumns destination:=.Cells(1), DataType:=xlDelimited, FieldInfo:=Array(1, xlYMDFormat)
End With

Fonction Jours360 : MsgBox Application.WorksheetFunction.Days360("11/11/2000", "12/12/2001")

Déclenchement à un moment donné

On peut déclencher un évènement à une heure donnée

application.QuandHeure HeureVal("17:00:00"); "wakeup" 'procédure wakeup déclenchée
application.OnTime Timevalue("16:04:00"), msgbox("wakeup")

Si on est lundi, Alors .... : if WeekDay(Now) = 1 then"

Durée d'utilisation d'un classeur ou d'une feuille (par Stefan Piotr) : news
Depuis combien de temps excel est-il ouvert :

=TEXTE(MAINTENANT();"hh:mm:ss")-TEXTE(ABS(REGISTRE.NUMERO("Kernel32";"GetTickCount";"J!")/3600);"hh:mm:ss")
Dim xy As Range:Set xy = [A1]
xy.FormulaR1C1 = "=TEXT(NOW(),""hh:mm:ss"")-TEXT(ABS(REGISTER.ID(""Kernel32"",""GetTickCount"",""J!"")/3600),""hh:mm:ss"")" MsgBox xy.Value
=> la fonction "registre.numero" est documenté dans l'aide. Pour excel 95, regarder la fonction "registre"

Depuis combien de temps Windows est-il ouvert ? (temps en milliseconde)

Function funTicker() As Double
'Use either 16-bit or 32-bit API call
If Application.OperatingSystem Like "*32*" Then
funTicker = wapiGetTickCount32 / 1000
Else
funTicker = wapiGetTickCount16 / 1000
End If

End Function

Sub test()
MsgBox Format(funTicker() / 3600, "hh:ss") 'affiche le temps écoulé depuis le démarrage de windows.
End Sub

Dans une cellule, vous pouvez écrire : =funTicker()/3600 et la formater comme vous voulez !

Create timeclock function in Excel (par tom Ogivly) : news
On time method (lien microsoft) : news

Filtrer selon dates

il faut mettre la date dans un format date valide entre les dièses #27 Janvier 1993#

ou bien utiliser une variable ; voici un exemple avec un boite de dialogue

Dim DateDeb As Double, DateFin As Double
DateDeb = Format(InputBox("Entrer DateDeb"), "0")
DateFin = Format(InputBox("Entrer DateFin"), "0")
Selection.AutoFilter Field:=11, Criteria1:=">=" & DateDeb, Operator:=xlAnd, _
Criteria2:="<=" & DateFin