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

From today through year end

Code:
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
Code:
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
 
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.

Code:
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
 
Thanks both. So, I understand from horseyride' solution that there is not command that yields all dates until the year end automatically? :pray:
 
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
 
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:
Code:
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:
Code:
()=>
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:
Code:
#"Invoked Custom Function" = Table.AddColumn(#"Previous Step", "Dates to End of Yr", each fnToEndOfYear())
 
Last edited:
Back
Top