Absence Days Calculation!

Hey all, is there a way to calculate how many days a use’s been absent?

I am using check in and out (TimeStamp only) along with user profile. I would like Glide to tell if the user doesn’t check in for today, he will be marked as absent! Any method or trick to use? Thank you in advance.

Since you said you allow multiple check-ins/outs per day in the other thread, I think the most straightforward way is to have a date-only column in the Sheets for your timestamps, then have a unique function to return only unique combinations of a user’s email and a date.

Count the number of unique rows for each user (A), then use a workdays function to calculate the number of workdays from each user’s first day until today (B).

(B) - (A) should give you the answer.

I am a little bit confused. I have three sheets, user profile, check in and check out. I already set Date only in both Check in and out while User profile has “NOW” only column.

Did you mean that, In user profile sheet on Glide, I return Email address and Date of check in and out then I roll them up using count?

And one more thing, you meant I should use workdays function on Glide as a math? Or on Google sheet itself? Because I googled workdays function and it needs holidays. In fact, there is no holidays at all.

If you may kindly guide me which sheet I should bring email addresses and dates to! Highly appreciated :pray:t2:

If you don’t have holidays at your work, then you can just calculate the day difference (which you can do in Glide).

The tricky thing here is that you allow multiple checkins and checkouts per day so you must find a way to use unique in your sheet to return only unique dates for each employee, that’s how I imagine it to work.

If you only allow it once per day then it’s a simple rollup.

Yes I agree with you, multiple check ins and outs is kinda overload.

Well, I just made it once a day. Rolling it up is going to give me the total check ins and outs. But how to count absence days. For example, I need it like if the user didn’t check in for today, he must be “absent” Therefore the number of absence days increase day by day as he/she doesn’t check in at all.

You should be able to create a relation and rollup column to get a max date for a user. Do this for the Check in and Check out sheet, so 2 rollup columns. Next create a math column that subtracts the max checkout rollup date from today. If the max check in date is not today, then you can display the match column value of how many days absent.

1 Like

I followed your steps. but the result came in negative number? It somehow worked and that’s true, -1 means they were absent. The thing is, how to get rid of the negative sign? and how to roll them up to show the total of absence days? I made relation to email itself in the user profile to roll them up but didn’t work.