Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 column with both start time and end time so that I can display it within a table or tooltip.
example:
original columns:
Date_Time_Start: 8:00:00 AM
Date_Time_End 8:30:00 AM
New column:
Time: 8:00am-8:30am
Solved! Go to Solution.
Hi @KGuyton,
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
Hi @KGuyton,
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He
Hi @KGuyton,
Based on my test, you could refer to below formula:
Time = CONCATENATE('Table1'[Date_Time_Start],CONCATENATE("~",'Table1'[Date_Time_End]))
Result:
Regards,
Daniel He
Now my only issue is that the time start and time end columns were originally in Date/time format so in the new column it is showing me both the dates and times as text, I cannot change the column data type without receiving an error and I cannot change the edit the data because the date is used for a relationship. How may I further format this new column to hide the dates and only show time?
Hi @KGuyton,
I could not understand the format of your data, you have posted your original data as below:
original columns:
Date_Time_Start: 8:00:00 AM
Date_Time_End 8:30:00 AM
Could you please post what your data look like in your data view?
Regards,
Daniel He
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
Hi @KGuyton,
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
Thank you.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |