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?
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
After that, we can create a COUNTIFS function to see if Check In and Check Out are checked.
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.
Thank You for quick answer but on beginning starts problem, the column "Created Date Only", shows #INVALID COLUMN VALUE.
Did you set the columun property of Created Date Only to Date?
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?
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.
CERTIFIED SMARTSHEET PLATINUM PARTNER
Efficiency Live: The Ultimate PM Toolkit Workshop
Create and edit formulas in Smartsheet
Formula combinations for cross sheet references
Smartsheet functions list
I created a timesheet and a form to go along with it. When someone uses the form and flags that someone has called off, I want the flag to return zeros in the ST Hrs, OT Hrs, and DT Hrs cells. Using AI to generate the formula below, it returns a Circular Reference error when the formula is placed in the ST Hrs cell.
Hi there, I'm building out a new sheet where I utilizing the system's "Last Modified" date column. I tried to trigger a workflow off of this column that sends an update reminder if it hasn't been modified in 2 days, but the column is not showing up as an option (presumably because it's system generated). With that in mind,…
Hey All! Currently, I have a formula that if it is the Parent row (helper column shows 0) then Index multiple sheets using the SSI column. If it is a children row, use the parent SSI column to Index multiple sheets (pulling the same result). The only issue is if I have a child within a child, then the PARENT(SSI@row) no…
©2024. All Rights Reserved Smartsheet Inc.