Forum Discussion
Solved
Excel formula assistance
Countif / Countifs have issues due to the time format and pivot table options have not worked very easily either. Looking to create a list which other workbooks can use at the same time for statisti...
=COUNTIFS($C$2:$C$10000,">="&$G2,$C$2:$C$10000,"<"&$G3,$E$2:$E$10000,I$1)I'd apply COUNTIFS as shown in the screenshot.
Below is an example of the emails copied (ctrl c from outlook) and pasted in excel (ctrl v) but I have modified emails and categories. Appreciate if you can offer any suggestions to make the count process easier. Think the issue relates to the time format as suggested COUNTIF / COUNTIFS has had problems and think it is due to the time format
So, that's only time, not datetime? Anyway, I guess PivotTable shall work.
- No, it doesn't as we need times grouped (08:00-10:00, 10:01-12:00, etc).
Tried using a pivot table but couldn't get the columns to group for the required time periods.
Was thinking an Access option, but will need to have a go if I can't get anything to work in Excel for what I have been asked to create.Why not? Adding data to data model and creating calculating column Frame like
=FORMAT( INT(Table1[Time]*24/2)*2/24, "hh:mm") & " - " & FORMAT( 2/24 - 1e-99 + INT(Table1[Time]*24/2)*2/24, "hh:mm")In Power Pivot that is
result will be like
It could be split by users if we have another column in the sample.