Hello all,
I spent several hours trying to identify a solution but, I only got partial ideas which only fix partial things, not the full issue.
So I have these entries:
The 2 goals that I need to achieve are...
- Calculate the difference between dates and timestamps to see if they fit on the next 2 ranges:
"<1h" and ">1h and <2h"
- Count how many are falling in the category "<1h" and how many fall into ">1h and <2h"
Limitation, both tasks without adding a new row or column.
So as an example (adding a new column, which is not what I am looking for), calculate the time difference is easy:
I can do it with =TEXT((B1)-(A1),"hh"). As an example.
So now I have the difference between "end" and "start", in hours format, which is correct
Now I should sum those was that are falling between ">1h and <2h", that should be 2.
Ok, until here all good, the problem is: is there any way to make such calculations in one formula?
I don't want to alter the source data CSV neither add a new tab with temporal entries, I would like to reflect the values straightforward in the calculation tab.
Is there any way to do that?
I was playing around with countifs, sumifs, sumproduct... nah.
I believe it can be done buuuut I just don't see it.
Thanks in advance.
I spent several hours trying to identify a solution but, I only got partial ideas which only fix partial things, not the full issue.
So I have these entries:
Start Row A | End Row B |
13/11/2021 22:41 | 14/11/2021 00:45 |
19/10/2021 03:33 | 19/10/2021 17:02 |
11/10/2021 05:23 | 11/10/2021 07:55 |
The 2 goals that I need to achieve are...
- Calculate the difference between dates and timestamps to see if they fit on the next 2 ranges:
"<1h" and ">1h and <2h"
- Count how many are falling in the category "<1h" and how many fall into ">1h and <2h"
Limitation, both tasks without adding a new row or column.
So as an example (adding a new column, which is not what I am looking for), calculate the time difference is easy:
Start | End | |
13/11/2021 22:41 | 14/11/2021 00:45 | 02 |
19/10/2021 03:33 | 19/10/2021 17:02 | 13 |
11/10/2021 05:23 | 11/10/2021 07:55 | 02 |
I can do it with =TEXT((B1)-(A1),"hh"). As an example.
So now I have the difference between "end" and "start", in hours format, which is correct
Now I should sum those was that are falling between ">1h and <2h", that should be 2.
Ok, until here all good, the problem is: is there any way to make such calculations in one formula?
I don't want to alter the source data CSV neither add a new tab with temporal entries, I would like to reflect the values straightforward in the calculation tab.
Is there any way to do that?
I was playing around with countifs, sumifs, sumproduct... nah.
I believe it can be done buuuut I just don't see it.
Thanks in advance.