Create date column from 6AM to 5:59AM

[nenandi]

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?

[nenandi]

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

[dufoq3]

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]

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).

[dufoq3]

It will work but you haven't applied my query to your data. If you don't know how to do it, read note below my post. Keep in mind that your column name is "Created" and I used "Date Time" in sample data. You have to change it in my query.

[nenandi]

yes I read your notes and also replaced column names

here sorce:

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],
#"Changed Type" = Table.TransformColumnTypes(#"44405e26-d3b2-481a-ab18-0a9091600df9",{{"BaD", type number}})
in
#"Changed Type"

[dufoq3 · Accepted solution]

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

[jennratten]

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?