Conditional column based on date column

[TechR21]

Hi,

I have following column with dates + times in it:

I want to create an extra column which gives me just the date of this start time column, with the following logic: If the date/time in start time column < 16:00 then it should give the date - 1 day, else the date mentioned.

So for example with above data

Start time                        Date (new column)

15/01/2023 18:00:00           15/01/2023

15/01/2023 02:30:00           14/01/2023

[johnt75]

You could add a custom column in Power Query something like

if Time.Hour([StartTime]) < 16 then Date.AddDays([StartTime], -1) else [StartTime]

[Syndicate_Admin]

You can create a new column using the "DATE" function and an "IF" statement to achieve this.

The "DATE" function takes a date/time value as an input and returns only the date part. The "IF" statement can be used to check if the start time is less than 4:00 PM, and if so, subtract one day from the date.

Here is the formula you can use to create the new column:

= IF(TIME(HOUR([Start time]), MINUTE([Start time]), SECOND([Start time])) < TIME(16,0,0), DATE([Start time])-1, DATE([Start time]))

You can use this formula in the "New column" option of the "Modeling" tab, in the "Add Column" section.