Forum Discussion

nenandi's avatar
nenandi
Regular Visitor
2 years ago
Solved

Create date column from 6AM to 5:59AM

Hi,

How to create new column from column Created date where I have date and time to new AdjustedDate colum from 6AM to 5:59AM?

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    Try this:

    let
    Source = SharePoint.Tables("https://vtts.sharepoint.com/sites/beltteam", [Implementation="2.0", ViewMode="Default"]),
    #"44405e26-d3b2-481a-ab18-0a9091600df9" = Source{[Id="44405e26-d3b2-481a-ab18-0a9091600df9"]}[Items],
    ChangedType = Table.TransformColumnTypes(#"44405e26-d3b2-481a-ab18-0a9091600df9",{{"BaD", type number}}),
    Ad_AdjustedDate = Table.AddColumn(ChangedType, "Adjusted Date", each 
        [ v_date = Date.From([Created]),
          v_time = Time.From([Created]),
          v_result = if v_time >= #time(0,0,0) and v_time <= #time(5,59,59) then Date.AddDays(v_date, -1) else v_date
        ][v_result], type date)
in
    Ad_AdjustedDate

7 Replies

  • jennratten's avatar
    jennratten
    Super User
    2 years ago

    Hello - you can create a new column with only the date, from a date/time column using the UI as shown in the screenshot below.  For the time adjustment, are you just trying to subtract one second from the time in the date/time column?

     

     

  • nenandi's avatar
    nenandi
    Regular Visitor
    2 years ago

    I want all record eg. from 00:00 to 5:59 to be in previous day date

    • dufoq3's avatar
      dufoq3
      Community Champion
      2 years ago

      Hi nenandi, like this?

       

      Result

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzDTNzIwMlEwMLAyMFCK1UEVNLIyxhQ0tTK1xBA0w6Ld0NzK0FQpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Time" = _t]),
    ChangedTypeLocale = Table.TransformColumnTypes(Source,{{"Date Time", type datetime}}, "sk-SK"),
    Ad_AdjustedDate = Table.AddColumn(ChangedTypeLocale, "Adjusted Date", each 
        [ v_date = Date.From([Date Time]),
          v_time = Time.From([Date Time]),
          v_result = if v_time >= #time(0,0,0) and v_time <= #time(5,59,59) then Date.AddDays(v_date, -1) else v_date
        ][v_result], type date)
in
    Ad_AdjustedDate
      • nenandi's avatar
        nenandi
        Regular Visitor
        2 years ago

        Hi  dufoq3,

        I got this,

        but I need new column with date and all records and columns just to change date for records who past 00:00 till 5:59 to day before see example this 04-Jun should be 03-June (I use day from 6:00 to 5:59 if is records pass 00:00 I count it the day before).