Counting rows based on time created

Suresh Seetharaman

I am looking to build a report based to count the no of rows created between a certain time, say 0800-20:00 for today with one condition. Tried all possible combination but no luck. Any help please?

Bassam Khalil

Hi @Suresh Seetharaman 

Hope you are fine, you need to add the following helper columns to your sheet with a related formula to calculate and if you like you can merge the formula in 2 columns but i do it in this way to simplify the solution: ( you can hide the helper columns )

1- system column "Created"

2- AM/PM Column =MID(Created@row, 17, 4)

3- Time = =MID(Created@row, 11, 6)

4- Hours = =IF(MID(Time@row, 2, 1) = ":", MID(Time@row, 1, 1), MID(Time@row, 1, 2))

5- 24 Hours = =IF([AM/PM]@row = "PM", VALUE(Hours@row) + 12, Hours@row)

6- Extracted Time = =MID(Created@row, 12, 7)

please convert all formulas to column formulas.

then you can create a report group the data using the column of 24 Hours and add summary counting using this column "24 Hours"

the following screenshot will show you the details:

and this is the final report screenshot:

Bassam Khalil

Hi @Suresh Seetharaman 

Hope you are fine, you need to add the following helper columns to your sheet with a related formula to calculate and if you like you can merge the formula in 2 columns but i do it in this way to simplify the solution: ( you can hide the helper columns )

1- system column "Created"

2- AM/PM Column =MID(Created@row, 17, 4)

3- Time = =MID(Created@row, 11, 6)

4- Hours = =IF(MID(Time@row, 2, 1) = ":", MID(Time@row, 1, 1), MID(Time@row, 1, 2))

5- 24 Hours = =IF([AM/PM]@row = "PM", VALUE(Hours@row) + 12, Hours@row)

6- Extracted Time = =MID(Created@row, 12, 7)

please convert all formulas to column formulas.

then you can create a report group the data using the column of 24 Hours and add summary counting using this column "24 Hours"

the following screenshot will show you the details:

and this is the final report screenshot:

Suresh Seetharaman

Thank you. I got the data I wanted.

Bassam Khalil

@Suresh Seetharaman

You are welcome, please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!