oguruma
XLDnaute Occasionnel
Avant toutes choses tous mes Voeux pour 2024.
En guise... d'étrennes qui sait, un petit outillage sur les dates.
Nouvelle année ==> certainement des questions sur les dates ?
Ceci est un simple outillage qui permet de générer des listes de dates, mois, semaines, et trimestres.
Il montre également qu'il n'y pas de solution unique mais plusieurs.
Le fait de passer par PowerQuery vous évitera de charger vos feuilles en calculs.
Pour intervenir sur le paramétrage des dates à calculer onglet [Params]
Quelques exemples
Voir le fichier joint pour plus d'exemples visuels
Concernant les mois
Concernant les semaines
Concernant les trimestres
Et pour terminer une fonction pour calculer le n° de semaine exact afin de bien retomber en fin d'année avec les bons n° de semaine qui se calculent de jeudi en jeudi.
Bon... y a peut-être mieux mais en tous elle fonctionne je suppose.... où il a fallu jongler avec le complément en jours pour terminer la semaine avec l'arrondi d'une division par 7 (jours par semaine) - Test effectué sur 2023 et 2024 uniquement. A vérifier sur d'autres années.
Les requêtes de paramétrage
StartYYMMDD
EndYYMMDD
getParameters
Quelques exemples de résultats en image
Et un début de calendrier simplifié
En guise... d'étrennes qui sait, un petit outillage sur les dates.
Nouvelle année ==> certainement des questions sur les dates ?
Ceci est un simple outillage qui permet de générer des listes de dates, mois, semaines, et trimestres.
Il montre également qu'il n'y pas de solution unique mais plusieurs.
Le fait de passer par PowerQuery vous évitera de charger vos feuilles en calculs.
Pour intervenir sur le paramétrage des dates à calculer onglet [Params]
Quelques exemples
PowerQuery:
let
//---------------------------------------------------------------------------------------------------------
// Génération d'une séquence de dates avec des valeurs fixes
//---------------------------------------------------------------------------------------------------------
DebAnnee=2024,
DebMois=1,
DebJour=1,
Duree=1,
Nbr=31,
//---------------------------------------------------------------------------------------------------------
// Construction de la liste
//---------------------------------------------------------------------------------------------------------
LstDates= List.Dates(
#date( DebAnnee, DebMois, DebJour ),
Nbr,
#duration( Duree, 0, 0, 0 )
),
//------------------------------------------------------------------------------------------------------
// On aurait pu garder la liste et la présenter aisni dans Excel mais autant la formater en table.
// Ca nous permet de gérer le type date au retour et d'imposer un nom de colonne
// ----------------------------------------------------------------------------------------------------
ToTable=Table.FromList(LstDates, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
ToTableTypeDate = Table.TransformColumnTypes(ToTable,{{"Date", type date}})
in
ToTableTypeDate
PowerQuery:
let
Nbr=getParameters("TB_PARAMS","NOMBRE_SEQUENCES"),
Duree=getParameters("TB_PARAMS","DUREE"),
//---------------------------------------------------------------------------------------------------------
// Construction de la liste
//---------------------------------------------------------------------------------------------------------
LstDates= List.Dates(
StartYYMMDD,
Nbr,
#duration( Duree, 0, 0, 0 )
),
//---------------------------------------------------------------------------------------------------------
//ToTable=Table.FromList(LstDates, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
// 2ème syntaxe qui permet de préciser le nom de la colonne et son type
//---------------------------------------------------------------------------------------------------------
ToTable=Table.FromList(LstDates, Splitter.SplitByNothing(), type table[ Date = Date.Type ])
// Et là plus besoin de cette étape
// ToTableTypeDate = Table.TransformColumnTypes(ToTable,{{"Date", type date}})
in
ToTable
PowerQuery:
let
Duree=1,
//---------------------------------------------------------------------------------------------------------------
// A partir d'une date de début et de fin très simple de déterminer l'étendue de calendrier en nombre de jours
//---------------------------------------------------------------------------------------------------------------
//---------------------------------------------------------------------------------------------------------
// On récupère les paramètres construits StartYYMMDD et EndYYMMDD
//---------------------------------------------------------------------------------------------------------
LstDates= List.Dates(
StartYYMMDD,
Duration.Days( EndYYMMDD - StartYYMMDD ) + 1,
#duration( Duree, 0, 0, 0 )
),
//---------------------------------------------------------------------------------------------------------
// Construction de la table de dates
//---------------------------------------------------------------------------------------------------------
ToTable=Table.FromList(LstDates, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
ToTableTypeDate = Table.TransformColumnTypes(ToTable,{{"Date", type date}})
in
ToTableTypeDate
PowerQuery:
let
//---------------------------------------------------------------------------------------------------------
// Construction de la liste de date sur la base d'une liste
// borne de départ et borne d'arrivée
//---------------------------------------------------------------------------------------------------------
LstNbrDates = { Number.From( StartYYMMDD ) .. Number.From( EndYYMMDD ) },
//---------------------------------------------------------------------------------------------------------
// Construction de la table
//---------------------------------------------------------------------------------------------------------
ToTable=Table.FromList(LstNbrDates, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
ToTableTypeDate = Table.TransformColumnTypes(ToTable,{{"Date", type date}})
in
ToTableTypeDateVoir le fichier joint pour plus d'exemples visuels
Concernant les mois
PowerQuery:
let
//---------------------------------------------------------------------------------------------------------
// Variante et affichage des noms de mois uniquement
//---------------------------------------------------------------------------------------------------------
ListDates = List.Generate( () =>
StartYYMMDD,
each _ <= EndYYMMDD,
each Date.AddMonths( _, 1 ) ), // On passe au mois suivant
//---------------------------------------------------------------------------------------------------------
// Table des dates
//---------------------------------------------------------------------------------------------------------
ToTableDate = Table.FromList(ListDates, Splitter.SplitByNothing(), type table[Date = Date.Type], null, ExtraValues.Error),
//---------------------------------------------------------------------------------------------------------
// Table en mois
//---------------------------------------------------------------------------------------------------------
ToTableMois = Table.AddColumn(ToTableDate, "Mois", each Text.Proper(Date.MonthName([Date], "FR-fr"))),
ToTableMois_Num = Table.AddColumn(ToTableMois, "Num Mois", each Date.Month([Date]), Int64.Type),
ToTableMois_NumYYYY = Table.AddColumn(ToTableMois_Num, "Mois-Annee", each Date.ToText([Date],"MMMM-yyyy"), type text),
ToTableMois_NumAlpha = Table.AddColumn(ToTableMois_NumYYYY, "Mois-Alpha", each Date.ToText([Date],"MM"), type text)
in
ToTableMois_NumAlphaConcernant les semaines
PowerQuery:
let
ListDates = List.Generate( () =>
StartYYMMDD,
each _ <= EndYYMMDD,
each Date.AddWeeks( _, 1 ) ), // On ajoute une semaine
//---------------------------------------------------------------------------------------------------------
// Table des dates
//---------------------------------------------------------------------------------------------------------
ToTableDate = Table.FromList(ListDates, Splitter.SplitByNothing(), type table[Date = Date.Type], null, ExtraValues.Error),
//---------------------------------------------------------------------------------------------------------
// Table des semaines
// Appel d'une fonction spécifique pour calculer la semaine ISO
//---------------------------------------------------------------------------------------------------------
ToTableSem = Table.AddColumn(ToTableDate, "Num Semaine", each NumberOfWeekISO([Date])),
ToTableSemAnnee = Table.AddColumn(ToTableSem, "Annee Num Semaine", each Number.ToText(Date.Year([Date])) & "-" & Text.PadStart(Number.ToText(NumberOfWeekISO([Date])),2,"0"))
in
ToTableSemAnneeConcernant les trimestres
PowerQuery:
let
ListDates = List.Generate( () =>
StartYYMMDD,
each _ <= EndYYMMDD,
each Date.AddQuarters( _, 1 ) ), // On ajoute un trimestre
//---------------------------------------------------------------------------------------------------------
// Table des dates
//---------------------------------------------------------------------------------------------------------
ToTableDate = Table.FromList(ListDates, Splitter.SplitByNothing(), type table[Date = Date.Type], null, ExtraValues.Error),
//---------------------------------------------------------------------------------------------------------
// Table des trimestres
//---------------------------------------------------------------------------------------------------------
ToTableTrim = Table.AddColumn(ToTableDate, "Mois", each "Trim-" & Text.From(Date.QuarterOfYear([Date])))
in
ToTableTrimEt pour terminer une fonction pour calculer le n° de semaine exact afin de bien retomber en fin d'année avec les bons n° de semaine qui se calculent de jeudi en jeudi.
Bon... y a peut-être mieux mais en tous elle fonctionne je suppose.... où il a fallu jongler avec le complément en jours pour terminer la semaine avec l'arrondi d'une division par 7 (jours par semaine) - Test effectué sur 2023 et 2024 uniquement. A vérifier sur d'autres années.
PowerQuery:
//*******************************************************************************************
// Calcul du n° de semaine ISO - Calendrier Grégorien
//*******************************************************************************************
(pDate as date) as number =>
let
//---------------------------------------------------------------------------------
// Calcul de l'écart du jeudi entre deux semaines, soit l'actuelle et la suivante
// et on tente de compléter sur les 3 jours manquant pour finir la semaine
//---------------------------------------------------------------------------------
EcartJeudi = Date.AddDays(pDate, -1 * Date.DayOfWeek(pDate, Day.Monday) + 3),
//---------------------------------------------------------------------------------
// Arrondi sur les 7 jours de la semaine par rapport à l'écart calculé
//---------------------------------------------------------------------------------
NumeroSem = Number.RoundUp(Date.DayOfYear(EcartJeudi) / 7)
in
NumeroSemLes requêtes de paramétrage
StartYYMMDD
PowerQuery:
let
DateDeb=getParameters("TB_PARAMS","DATE_DEB"),
Day=Date.Day(DateDeb),
Month=Date.Month(DateDeb),
Year=Date.Year(DateDeb),
Param=#date(Year, Month, Day) meta [IsParameterQuery=true, Type="Date", IsParameterQueryRequired=true]
in
ParamEndYYMMDD
PowerQuery:
let
DateFin=getParameters("TB_PARAMS","DATE_FIN"),
Day=Date.Day(DateFin),
Month=Date.Month(DateFin),
Year=Date.Year(DateFin),
Param=#date(Year, Month, Day) meta [IsParameterQuery=true, Type="Date", IsParameterQueryRequired=true]
in
ParamgetParameters
PowerQuery:
(pTable as text, pName as text) =>
let
Source = Excel.CurrentWorkbook(){[Name=pTable]}[Content],
RowsParams = Table.SelectRows(Source, each ([PARAMETRE] = pName)),
value = RowsParams{0}[VALEUR]
in
valueQuelques exemples de résultats en image
Et un début de calendrier simplifié
Dernière édition: