Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Combining Date columns

I have two date columns.  Specifically, one date column is the "start date" the other is the "end date" both are formatted to display only the time.  I was to combine these columns so that I have one...
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

     Hi Anonymous,

    Based on my test, you could refer to below ways:

    You could use TIMEVALUE function to fetch the time data, refer to below formula:

    Time = CONCATENATE(TIMEVALUE('Table1'[Date_Time_Start]),CONCATENATE("~",TIMEVALUE('Table1'[Date_Time_End])))

    Result:

    Or you could use the RIGHT function to fetch the time value:

    Time2 = CONCATENATE(RIGHT('Table1'[Date_Time_Start],11),CONCATENATE("~",RIGHT('Table1'[Date_Time_End],11)))

    Result:

     

    You could also download the pbix file to have a view.

     

     

    Regards,

    Daniel He

     

     

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

I had forgotten that I formatted the data beforehand.  The original data is in the format 'MM/DD/YYYY HH/MM/SS am/pm"  So when using the code given here I get a format of date and time in text.  I am unable to remove the date from the new colum

v-danhe-msft's avatar
v-danhe-msft
Microsoft Employee
7 years ago

 Hi Anonymous,

Based on my test, you could refer to below ways:

You could use TIMEVALUE function to fetch the time data, refer to below formula:

Time = CONCATENATE(TIMEVALUE('Table1'[Date_Time_Start]),CONCATENATE("~",TIMEVALUE('Table1'[Date_Time_End])))

Result:

Or you could use the RIGHT function to fetch the time value:

Time2 = CONCATENATE(RIGHT('Table1'[Date_Time_Start],11),CONCATENATE("~",RIGHT('Table1'[Date_Time_End],11)))

Result:

 

You could also download the pbix file to have a view.

 

 

Regards,

Daniel He