Check IN/OUT box

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.

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

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

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.