How to merge 2 columns ( start date and end date ) with column of time using on if statement

[Anonymous]

Hello, 

I m having a trouble with merging dates and time based on conditions : i have 2 dates :

Start date and end dates, and i have many columns with time Format , i want to merge the columns using those conditions : 

If ( Column Time <= (00:00:00) and start date = end date then [Start Date]& " "&[TimeColumn] else [end date]& " "& [TimeColumn]

this is not working neither on calculated colmun nor on dax measure 

my final goal is to obtain for each timecolumn a merged column with the appropriate date (end date ==>if the time is equal or over 00:00:00 other wise merge with the start date!

If u have any ideas, i ve been blocked on this a week noww ! 😞 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please check the formula.

Column = IF(TableA[Column Time]<TIME(00,00,00)&&TableA[start date]=TableA[end date],TableA[start date]+TableA[Column Time],TableA[end date]+TableA[Column Time])

Best Regards,

Jay

[Anonymous]

hello, 

i just tested another case and the condition is not working correctly i think : for this date since we re at 23h PM he should choose the date 06/01 and not the 07/01

do u have an idea please how can i add more condition for midnight value ! 

thanks in advance ! 🙂 

[Anonymous]

on dax its working but i get weird dates instead of my dates values : 

[amitchandak]

@Anonymous ,

If you need in DAx

If ( [Column Time] <= time(0,0,0) && [start date] = [end date] , [Start Date]+ [TimeColumn] , [end date]+  [TimeColumn] )

in power Query

If  [Column Time] <= #time(0,0,0) and [start date] = [end date] then [Start Date]+ [TimeColumn] else [end date]+  [TimeColumn]

[Anonymous]

thank you, i just tried, i cannot apply + on date and time formats on powerquery 

for the other values i got "null values" on my custom column 

i dont know if there is another way to put condition on times and date in order to calculate the right duration because without merging the dates ( end and start) and times, the duration of 2 task between 23 PM and 1 AM is 22h instead of 2h ! 😞 

[Anonymous]

Instead of '+', try using '&'