I have a lot of requests coming in with the following timestamp (Created time Column) and I would like to quickly be able to count how many requests came in during each hour. I found I can get a code to work if I manually delete the date information, but if I change the format from number to date and only show the timestamp then the formula below doesn't work.
How can I get the formula to ignore the date? I don't want to have to manually update that column each day I update this report.
=countifs(C2:C,">=07:00:00",C2:C,"<=08:00:00")
Best Answer
Can try:
=sumproduct(--(trim(right(C1:C,8))>="9:00:00"),--(trim(right(C1:C,8))<="9:59:59"))Had to change the criteria slightly to 9:59:59 as it won't calculate properly with 10:00 as the number of characters would be different. i.e time before 10 display with a single digit and no leading 0. Would have thought 10:00:00 would be the start of the next hour though. Otherwise you might have to see if you can do a custom date format to include a leading 0 if the hour is a single digit.