0

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")

What I would like

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.