Check IN/OUT box

ultimatum

I'm using the form to check in and check out persons, how do to count every day and show in dashboard how many people are check in or check out?

jmyzk_cloudsmart_jp

Hi @ultimatum

Using the system column [Created Date] can be tricky to determine if it is today.

The Created Date in Smartsheet provides a GMT-based date and time.

Therefore, you need to correct the date according to the GMT Offset of your location (e.g., GMT +9 for Japan).

If you do not care about GMT Offset, then the determination is simple and is as follows.

[Is Today] column

• IF(DATEONLY(Created@row) = TODAY(),1) 

After that, we can create a COUNTIFS function to see if Check In and Check Out are checked.

For example,

• =COUNTIFS([Check In]:[Check In], 1, [Is Today]:[Is Today], 1)

The formula to correct the date according to the location's GMT Offset is a bit more complicated.

I have written the formula in the Summary Field of demo sheet, so check it out.

https://app.smartsheet.com/b/publish?EQBCT=23dafb287d0d48cabcdede41919ffe93

In the demo published sheet, you can edit the Primary Column called Project ID, Check In, Check Out columns, and GMT Offset field of the Sheet Summary.

You can check how the formulas work.

If the users of the form span different time zones, e.g., worldwide, it will be more complicated.

You may need to use Created By to refer to the user's Location Table or GMT Offset Table and use a different Offset for each area instead of one GMT Offset as in the demo example.

ultimatum

Thank You for quick answer but on beginning starts problem, the column "Created Date Only", shows #INVALID COLUMN VALUE.

jmyzk_cloudsmart_jp

@ultimatum

Did you set the columun property of Created Date Only to Date?

ultimatum

https://community.smartsheet.com/discussion/comment/392713#Comment_392713

Correct is working now, Thanks a lot.

The second thing that I would like to have is if this day all people from my external list are Check IN or Check OUT, any idea how to do that?

Paul Newcome

Something like this should give you how many are still checked in for the day:

=COUNTIFS([Check In]:[Check In], @cell = 1, Created:Created, @cell = TODAY()) - COUNTIFS([Check Out]:[Check Out], @cell = 1, Created:Created, @cell = TODAY())

If you wanted it for other than TODAY, you would replace TODAY() with DATE(yyyy, mm, dd) making sure to use the year, month, and day you are wanting to calculate for.