Results 1 to 5 of 5

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

  1. #1
    Seeker christiane.latte's Avatar
    Join Date
    Feb 2018
    Posts
    12
    Articles
    0
    Excel Version
    2016

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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/pow...y-m/list-dates only yields the dates until today.

    Can anyone help please?

    Best,
    Chrissi

  2. #2
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    71
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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

  3. #3
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    132
    Articles
    0
    Excel Version
    2019
    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

  4. #4
    Seeker christiane.latte's Avatar
    Join Date
    Feb 2018
    Posts
    12
    Articles
    0
    Excel Version
    2016
    Thanks both. So, I understand from horseyride' solution that there is not command that yields all dates until the year end automatically?

  5. #5
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    71
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •