Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |