Report to show late entries based on created date/time field

Lindsey.Jones

I am looking to write a report that will tell me what form entries are late using the created date/time field. My team is supposed to submit all data entries for a specific form between 6 and 8 am. I am looking for a formula that can roll up the counts on a daily, weekly, monthly basis for any entries that are submitted outside of the 6-8 window. Can anyone help?

Michael Culley

Hmm well to start I think you need a helper column to pull just the hour and the AM/PM of the date created column.

I came up with this:

=MID([Date Created]@row, FIND(":", [Date Created]@row) - 1, 1) + RIGHT([Date Created]@row, 2)

Then for the formula that counts, you can do:

=COUNTIF([Helper Column]:[Helper Column], "6AM") + COUNTIF([Helper Column]:[Helper Column], "7AM") + COUNTIF([Helper Column]:[Helper Column], "8AM")

This will count all of the entries that are in that window.

Maybe there's an easier way to do this but this is what I would do.

Let me know if that helps.

Michael Culley

You could also create a checkbox column that checks whenever an entry is on time, then use those for your counts.

In checkbox column:

=IF(OR([Helper Column]@row = "6AM", [Helper Column]@row = "7AM", [Helper Column]@row = "8AM"), 1, 0)

Paul Newcome

I was thinking it could all be combined into one without the helper column.

Today:

=COUNTIFS(Created:Created, AND(FIND("A", @cell) > 0, IFERROR(VALUE(MID(@cell, FIND(":", @cell) - 1, 1)), 0) >= 6, IFERROR(VALUE(MID(@cell, FIND(":", @cell) - 1, 1)), 9) <= 8, IFERROR(DATEONLY(@cell), TODAY(+99)) = TODAY()))

Current Week:

=COUNTIFS(Created:Created, AND(FIND("A", @cell) > 0, IFERROR(VALUE(MID(@cell, FIND(":", @cell) - 1, 1)), 0) >= 6, IFERROR(VALUE(MID(@cell, FIND(":", @cell) - 1, 1)), 9) <= 8, WEEKNUMBER(IFERROR(DATEONLY(@cell), TODAY(+99))) = WEEKNUMBER(TODAY()), IFERROR(YEAR(DATEONLY(@cell)), 0) = YEAR(TODAY())))

Current Month:

=COUNTIFS(Created:Created, AND(FIND("A", @cell) > 0, IFERROR(VALUE(MID(@cell, FIND(":", @cell) - 1, 1)), 0) >= 6, IFERROR(VALUE(MID(@cell, FIND(":", @cell) - 1, 1)), 9) <= 8, MONTH(IFERROR(DATEONLY(@cell), TODAY(+99))) = MONTH(TODAY()), IFERROR(YEAR(DATEONLY(@cell)), 0) = YEAR(TODAY())))

Lindsey.Jones

@Paul Newcome & @Michael Culley

I was able to get both of these options to work, so thank you very much! I ended up using the helper columns because I added a few criteria to my formula.

What I am thinking now is I may have another issue with this... when looking across time zones. Is there any way to account for time zones or do the formulas always calculate based on the time zone of the person viewing the sheet/report?

I appreciate any insight you may have to time zones.