英文:
First event of each day, but could be after midnight
问题
以下是翻译好的内容：
我有一个相当大的数据集，格式如下：
| A列 | B列 |
|---|---|
| 16/06/2020 | 9:49:36 PM |
| 17/06/2020 | 10:15:47 PM |
| 17/06/2020 | 01:15:47 AM |
A列中的数据是有效的Excel日期字段，B列中的数据是有效的Excel时间字段。
我正在尝试获取每天的第一个通话时间。我尝试使用最小时间的数据透视表，但因为它会选择AM时间而不是PM时间，所以这并不起作用。数据通常从晚上（PM）开始，然后一直持续到凌晨（AM）。但有时直到午夜后（AM）才开始，所以我也需要考虑这种情况...有点棘手吗？
我头疼了...有任何想法/建议都非常欢迎。
英文:
I have quite a big data set in this format
| Column A | Column B |
|---|---|
| 16/06/2020 | 9:49:36 PM |
| 17/06/2020 | 10:15:47 PM |
| 17/06/2020 | 01:15:47 AM |
Data in column A is a valid Excel date field and data in column B is valid Excel time field.
What I am trying to get is the time of the first call per day. I have tried PivotTables with min time but that did not work because it would pick the AM time over the PM time. The data usually starts in the evening (PM) and goes on all night (AM). BUT, sometimes it wouldn't start until after midnight (AM) so I need it to pickup on that as well... Tricky one?
I'm getting a headache... Any ideas/suggestions are most welcome.
答案1
得分: 1
以下是已翻译好的代码部分：
Formula I have used-
D1=UNIQUE(A1:A3)
E1=TAKE(SORT(FILTER($A$1:$B$3,($A$1:$A$3>=D1)*($B$1:$B$3>=TIME(16,0,0))),{1,2},{1,-1}),1,-1)
All in one formula-
=HSTACK(UNIQUE(A1:A3),
BYROW(UNIQUE(A1:A3),
LAMBDA(x,
TAKE(SORT(FILTER($A$1:$B$3,($A$1:$A$3>=x)*($B$1:$B$3>=TIME(16,0,0))),{1,2},{1,-1}),1,-1
))))
英文:
Formula I have used-
D1=UNIQUE(A1:A3)
E1=TAKE(SORT(FILTER($A$1:$B$3,($A$1:$A$3>=D1)*($B$1:$B$3>=TIME(16,0,0))),{1,2},{1,-1}),1,-1)
All in one formula-
=HSTACK(UNIQUE(A1:A3),
BYROW(UNIQUE(A1:A3),
LAMBDA(x,
TAKE(SORT(FILTER($A$1:$B$3,($A$1:$A$3>=x)*($B$1:$B$3>=TIME(16,0,0))),{1,2},{1,-1}),1,-1
))))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论