Custom Column - Either same date as in another column or +1 day based off of criteria

[JordanJackson]

Hi, 

I am looking for the formula for creating a custom column in power queary editor. 

I have a date column and then multiple time columns denoting the start, middle and end times of events. 

These events often pass over night so using DATEDIFF to calculate the duration isn't possible. 

My reasoning is that I need to create a custom column based off of whether the end date has passed over 12PM in the next day.

I am thinking that this can be done through having a custom column which look at the end time, if it is before 23:59:59 then is uses the date from the date column. If the time is 00.00.01 and higher then having it be the date in the date column +1 day. 

I also think I would have to do all time from say 00.01-07.00 as next day, and then 07.01 and 23.59

59 as being the same date. 

If anyone could help with what to write as the formula for this then that would be amazing!

[Anonymous · Accepted solution]

Hi @JordanJackson ,

How about this custom column?

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JordanJackson]

This worked perfectly, thankyou!!