List.Dates until a certain time (e.g. end of year)

[christiane.latte]

Hei,

need some quick help: I am looking for the command to give me all dates until the end of the year. The command https://docs.microsoft.com/en-us/powerquery-m/list-dates only yields the dates until today.

Can anyone help please?

Best,
Chrissi

 

[horseyride]

From today through year end

let Source = List.Generate(()=> DateTime.Date(DateTime.LocalNow()), each _ <= DateTime.Date(Date.EndOfYear(DateTime.LocalNow())), each Date.AddDays(DateTime.Date(_), 1)),
Converted = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in Converted

from first of year until today

let Source = List.Generate(()=> DateTime.Date(Date.StartOfYear(DateTime.LocalNow())), each _ <= DateTime.Date(DateTime.LocalNow()), each Date.AddDays(DateTime.Date(_), 1)),
Converted = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in Converted

 

[alansidman]

I know that you posted in Power Query, but here is an alternative solution using VBA that lets you pick a start date and how many days you wish to fill in as well as select the column you wish to fill.

Option Explicit


Sub AutoDateFill()
    Dim x As Date
    Dim rn As Long
    Dim cn As String, sn As Long
    cn = InputBox("Which Column to fill?")
    
    
    rn = InputBox("How many rows to fill?")
    sn = InputBox("Which row to start fill?")


    Application.ScreenUpdating = False
    Application.StatusBar = "Macro Running"
    x = InputBox("What is the starting Date? mm/dd/yyyy")
    Range(cn & sn).Select
    Do Until ActiveCell.Row = rn + sn
        If ActiveCell.EntireRow.Hidden = False Then
            ActiveCell.Value = x
            x = x + 1
        End If
        ActiveCell.Offset(1).Select
    Loop
    Application.ScreenUpdating = True
    Application.StatusBar = "Completed"
End Sub

 

[christiane.latte]

Thanks both. So, I understand from horseyride' solution that there is not command that yields all dates until the year end automatically? ray:

 

[horseyride]

If so, let me know. I have a bunch of work that I'd love to be able to have done automatically. Would save a bunch of time

 

[p45cal]

If so, let me know. I have a bunch of work that I'd love to be able to have done automatically. Would save a bunch of time

Well, maybe:

let
    tday = DateTime.Date(DateTime.LocalNow()),
    dtesList = List.Dates(tday,Number.From(Date.EndOfYear(tday)-tday)+1,#duration(1,0,0,0))
in
    dtesList

So you could make it into a function called fnToEndOfYear:

()=>
let
    tday = DateTime.Date(DateTime.LocalNow()),
    dtesList = List.Dates(tday,Number.From(Date.EndOfYear(tday)-tday)+1,#duration(1,0,0,0))
in
    dtesList

then invoke it, say with add column, thus:

#"Invoked Custom Function" = Table.AddColumn(#"Previous Step", "Dates to End of Yr", each fnToEndOfYear())