英文:
Excel issue - choose next time
问题
下午好，
我在努力解决我试图解决的问题。
基本上，我有一个包含时间值的列表。
看一个例子：
正如您所见，时间值有时可能在相同的时间间隔内。
这是我希望最终结果看起来的方式：
我使用以下公式在下一个工作表中显示开始和结束结果。
=IF(WTW!D4="", "", LEFT(TEXT(WTW!D4, "HH"), 2)&":00")
我如何才能实现最终结果仅显示开始时间和结束时间，而不重复显示结束结果。
例如：
不好的最终结果
06:00 - 07:00
06:00 - 07:00
好的结果
06:00 - 07:00
07:00 - 08:00
这一切的原因是因为开始和结束时间可以更改...
英文:
Good afternoon,
I am struggling to fix the issue I'm trying to resolve.
Basically, I have a list which includes time values.
See example:
As you can see the time values could sometimes be in the same time interval.
That's how I would like it to look at the end result :
I'm using the following formula to display the start and end result in the next sheet.
=IF(WTW!D4="","",LEFT(TEXT(WTW!D4,"HH"),2)&":00")
How I could achieve that the end result to basically only display the start and end time without duplicating the end results.
As example:
Bad end result
06:00 - 07:00
06:00 - 07:00
Good result
06:00 - 07:00
07:00 - 08:00
The reason for all this is because the start and end hours can change...
答案1
得分: 1
=IFERROR(INDEX(Sheet3!$B$3:$B$30, MATCH(0, COUNTIF($Q$1:Q2,Sheet3!$B$3:$B$30), 0)), "")
只需要在整个列表中插入某种方式，以便这也过滤一个额外的条件...
英文:
I've managed to sort the unique time by the following formula:
=IFERROR(INDEX(Sheet3!$B$3:$B$30, MATCH(0, COUNTIF($Q$1:Q2,Sheet3!$B$3:$B$30), 0)), "")
Just now need to insert somehow so that this also filter one extra criteria in the whole list...
答案2
得分: 0
=TIME(HOUR(A1),0,0)
然后右键单击所选单元格/区域，点击格式单元格。点击自定义，将文本框替换为hh:mm。
英文:
Hour function may not be insufficent. Why don't you try:
=TIME(HOUR(A1),0,0)
Than right click at the selected cell/range and click Format Cells. Click Custom and replace textbox to hh:mm
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论