I have a column in a table that contains the date and time. This is the form

15.06.2020 09:43:24
17.06.2020 08:21:04
18.06.2020 12:11:34

or also

2021-01-21 11:57:34
2021-01-21 11:58:02
2021-01-21 11:58:26

Now I am trying to count specific time periods via COUNTIFS, which unfortunately I am not able to do.

It does not matter if I use




take, it does not produce a result that is correct (either all entries are listed or only 1 or 0.

Any idea how I can solve this with COUNTIFS without splitting the timestamps into two columns since this is a report that updates automatically.

1 Answer 1


You mention two different formats, so chances are that some of the values will be numeric datetime values and some will be text strings that just look like datetimes. The easiest way to cope with this is to convert them all to text strings and extract the time of day part with something like this:

    timevalue( regexextract(trim(A2:A), "[\d:]+$") ); 

The string literal "15:00:00" gets automatically coerced to a numeric time value, but only when the spreadsheet locale uses colons as time separators. Adjust these separators per your locale.

See this answer for an explanation of how date and time values work in spreadsheets.

  • Thanks for your help. I used your example but I always get an "error parsing this formula" I used this array formula (Have to use the German words here but those are the correct ones) =ARRAYFORMULA(ZÄHLENWENNS(ZEITWERT(REGEXEXTRACT(GLÄTTEN(ticket_data_and_metrics!C2:C),"[\d:]+$")),">=15:00:00"))
    – Marcel
    Apr 28, 2021 at 11:36
  • That is correct — you can replace the comma , with a semicolon ; in the formula to make it work in a locale that uses commas as decimal separators, such as German. Apr 28, 2021 at 16:56
  • Thanks, that worked well!
    – Marcel
    Apr 29, 2021 at 6:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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