• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help on calculating login hours and sign in days for employees

Kapoor_Nitin

New Member
Hi All,

I have a spreadsheet that needs to determine the first time a user logged in to the system and the last time the user logged out.

The login and logout report comes from another system from which I copy and paste the data to my spreadsheet. .

Basically, since the users use the system to login and logout, they will have multiple records of logging in and out on a daily basis. I need to find the very first login record and last logout record per user. One big challenge is that people do night shifts as well where the login date and logout date can be different. For e.g. employee A login at 10:30 pm on Oct 21st and logout at 7:30 am on Oct 22nd.

I have attached the sample file.
 

Attachments

  • Hours Calculation.xlsx
    53.9 KB · Views: 25
Kapoor_Nitin
Have You checked Your data Yourself?
eg Shalini
1st Sign In 01/10/2019 00:02
1st Sign Out 18/10/2019 09:04 ... seems to be a looong shift or how? Ideas?
between those there are 'some' other datas ...

eg. 'Break' - when it ends?
 
Sorry for the confusion. Actually I have created a live attendance tracking on Google Forms and this is the output from it. Since its new to the company, employees were not using it properly initially. Even till now they forget to plot their break in and break out. Kindly ignore the first few days data, I have removed the first 5 days data and attached the file again.

I need to calculate two things here
1. Attendance for each date for every employee (simple pivot does not work here as few employees do night shifts where the date changes when they logout.
2. Login hours for each date for every employee.


Thank You!
 

Attachments

  • Hours Calculation.xlsx
    44.4 KB · Views: 13
Last edited:
Kapoor_Nitin
Did You answer to my questions?
Before clear answers, I would give some comments:

Terms:
> 'forget' ... that would continue as with that the 1st file.
It won't help nor change, if You or someone else will clean any row from data, including 'breaks'
if 'missing' data then there should be clear rules - how to handle those cases

> 'simple'
There are possible things ... if something would be as Your used therm then You would be solved it.

> 'night shift'
As You have needed daily hours, then for those 'night shifts' there would be 'early morning and late evening hours' per day!
 
These are the challenges I see in the data as of now. I am working to fix these as appropriate feedback is being given to the employees. However, I need to calculate Attendance and login hours, wherever possible that I have to give to HR team for the month of October.

To answer your questions,
- few employees have not used sign out option, feedback already shared with them - basis their sign in for each date we have to calculate employee attendance

- to end the break time, employees have to sign in again
10/09/19 23:40Sign InShalini
10/10/19 4:44BreakShalini
10/10/19 5:21Sign InShalini


I agree with you that there should be clear rules for missing data but not sure on these as of now.

> 'simple'
There are possible things ... if something would be as Your used therm then You would be solved it. (didn't understand this one)

> 'night shift'
As You have needed daily hours, then for those 'night shifts' there would be 'early morning and late evening hours' per day! (yes, but how to calculate hours for each date for those employees)

Thanks!
 
Kapoor_Nitin
As long as there are ... many challenges ... You cannot solve hours from Your data! Or ... You will get ONLY few data to give to HR!
You could see those from below snapshot ... those which has BOTH I (as Sign In) & O (as Sign Out).
You wrote that 'few employee haven't sign out' ... I would write ... that 'few employee' HAS sign out.
Users given data (information) cannot fix manually - or can - but always the original data also have to be there!
Screenshot 2019-10-24 at 18.01.16.png
'simple': I don't know anything when could use that term! Many things are possible ... some cases needs more work!
'night shift': as long as data is ... then You won't need to take care those!

You've written that You've have created 'a live attendance tracking on Google Forms' - okay.
Do it also give information for users as 'my version' of tracking? Of course, this tracks something else than Yours.
Basic - user fills max five information (left bordered area) and press LOG or SAVE.
User can see latest logs two way all the time.
Screenshot 2019-10-24 at 18.05.32.png
 
I know hours is a challenge right now and I am gonna let the HR now about this. But can you help me with the formula of calculating login hours on a daily basis if everyone adhere to the tool diligently.

Currently, only attendance of the employees should be okay. How to calculate number of days present for each employee in this case.

Below is the snapshot of the Live Tracker I have created. The idea was to show everyone on real time basis who all are available. Their status can be Sign in, Break or Busy.

63614

First Login is the first sign in for the day
Latest status is the latest sign in
Duration is Difference between Current time and Latest Status Time
Total Login Hrs is the difference between Current Time and first sign in

The employee name is removed from this tracker when they select for Sign out option in the Attendance Google Forms I have created. The backend data is what I have shared with you.
 
Kapoor_Nitin
Seems that You didn't get my point - okay.
If that layout is for user, then I'm understand Your given data.
Is there possible to 'Sign In' without previous 'Sign Out'?
There needs to have also previous days data! as now; 30 Sep (5 Oct) to solve night shifts!
... as well as max shift length!

Anyway ... I continued to figure, what could do with Your data - not ready!
Left side - names. Top row give dates.
Yellow cells has 'unknown' data.
Values present daily hours (not nightshifts).
From every value cell could see timestaps from comment. If value is bolded then there is also 'break'.
I don't have time to verify those calculations now.
... if someday has 'only' break ... do those also needs?
Screenshot 2019-10-24 at 22.27.25.png
 
Kapoor_Nitin
Here is a sample of calculations version 2.
Upper table shows hrs and below table shows breaks.
In both cases, comments shows details.
I found some 'nightshifts' ... those seems to work.
... and of course --- I didn't verify all results.
Without answers, I gotta guess!
... and a sample means as a sample!
 

Attachments

  • Hours Calculation.xlsb
    68.5 KB · Views: 25
Thank You vletm. This looks good. How did you do that ?

Answers to your questions:
Is there possible to 'Sign In' without previous 'Sign Out'? – Yes, as every submission is independent of the previous one.

There needs to have also previous days data! as now; 30 Sep (5 Oct) to solve night shifts!
... as well as max shift length! – we started this from Oct 1st (refer my first file). Max shift can be of 12 hours.

... if someday has 'only' break ... do those also needs? - No, we will not count that day for attendance
 
Kapoor_Nitin
So far, it's a sample ... I'm doing it step-by-step with guesses.
> 'Sign In ---- Sign Out' ... this should be one shift. So far, daily values could get ONLY-and-ONLY if there is 'Sign Out'.
> prev days data ... okay ... then in Your case there cannot be night shift from 30-Sep to 01-Oct ... not a challenge!
> if shift seems to be over 12hrs - what do rules say? There are few over 12hrs dates.
There is ONE cell (X11) which has 4:25 hrs, but there are ... 11:53 for break.
Maybe You should sit and think ... You cannot change employees data!
> 'only' break ... as You could see from lower table ... there are a lot of breaks ... also there are many 'Sign In's but normally NO full shift!

>>> If employee could see something as with my shown layout, there would be less mistakes! <<<

Any ideas?
 
if shift seems to be over 12hrs - what do rules say? There are few over 12hrs dates. - we will only take shift up to 12 hours
There is ONE cell (X11) which has 4:25 hrs, but there are ... 11:53 for break. - there has been few instances where people plotted breaks but forgot to sign in or sign out thereafter..i will be giving feedback to them

>>> If employee could see something as with my shown layout, there would be less mistakes! <<< I liked what you shared earlier...if all the employees punch their status properly....your solution will work perfectly. Can you advise/share how did you get the below table. This should work for me.

63661



I did not understand the above chart!
 
Kapoor_Nitin
max 12hrs per day - modified
> if needed to CUT then there will be mark in that days comment and cell will be yellow

breaks more than hours
> yellow cells and breaks have NOT noticed!

>>> If employee could see something as with my shown layout, there would be less mistakes! <<<
Did You refer to this #6 reply or to where?
Screenshot 2019-10-28 at 18.29.43.png

Can you advise/share how did you get the below table.
> If You refer to Your sent snapshot (#13 reply) then
refresh data > press [ Solve ]
Screenshot 2019-10-28 at 18.31.59.png
If You refer to #6 reply then that's different story.

I did not understand the above chart!
> Did You refer to Your snapshot #13 reply or
My #12 reply?
... if #12 reply then that chart shows Anands data with graph - above breaks -- center all signs -- below working
... can see eg at once is it night shift or not ... all signs in every day ... and so on
 

Attachments

  • Hours Calculation.xlsb
    92.8 KB · Views: 11
>>> If employee could see something as with my shown layout, there would be less mistakes! <<< - this view is not feasible for me

Can you advise/share how did you get the below table. - yes i was referring to #reply 13

I am thinking I should ignore break hours as not all employees plot breaks since most of them take their lunch while working. So would need attendance for the month and total login hours only.

I used your file and its working fine. Just saw one issue, data is not correct for employees who are logging in around mid-night (+/- 30 mins).
For e.g. See Ritika's data. Basis the results from your file, she has an attendance of 28 days, however, it should be 26 days as she had weekly offs on
Oct 5, Oct 12, Oct 19 and Oct 26. Additionally, Oct 15th is not counted in your file since there was no sign out for that day. We need to count first sign in as attendance.

How can we fix this?

Please note - The time in my file is Canadian Eastern TIME, whereas most of our employees are based out of India.
 

Attachments

  • Hours Calculation (5).xlsb
    167.2 KB · Views: 8
Kapoor_Nitin
1) from Your: >>> If employee could see something as with my shown layout, there would be less mistakes! <<< I liked what you shared earlier...if all the employees punch their status properly....your solution will work perfectly.
... and Your comment is this view is not feasible for me ... and just You used verb 'like' ... hmm? Do it mean for You same as 'not feasible'?

2) Can you advise/share how did you get the below table. - yes i was referring to #reply 13
You got an answer - okay?

3) Breaks ... if someone mark breaks then those should notice!

4) eg Ritika. I can notice ONLY data which You've sent. For me, it's ONLY correct information which CANNOT modify afterwards!
Below is used data for Ritika. As I've written ... there have to be Sign Out to get hours. I cannot know when Ritika should work!
5th, 12th, 19th & 26th - there are hours for those day! 15th - if no sign Out then no hours!
Screenshot 2019-10-31 at 15.46.02.png
Those 'weekends' has hours - no fix.
15th - if could notice 'someway' eg as I've shown for You - then there should be possible to fill form for add missing data ... and so on.
>> What did You mean?

5) Times are those times which use for calculations.
 
1) from Your: >>> If employee could see something as with my shown layout, there would be less mistakes! <<< I liked what you shared earlier...if all the employees punch their status properly....your solution will work perfectly.
... and Your comment is this view is not feasible for me ... and just You used verb 'like' ... hmm? Do it mean for You same as 'not feasible'?

I was talking about the below table for above comment.
63744


Below option is not feasible for me.
63745


2) Can you advise/share how did you get the below table. - yes i was referring to #reply 13
You got an answer - okay?
No, as i wanted to learn/know how did you make this table


3) Breaks ... if someone mark breaks then those should notice! - Yes, but if its creating issues in calculations then we can ignore it as primary motive is to calculate attendance and login hours for the day.

4) eg Ritika. I can notice ONLY data which You've sent. For me, it's ONLY correct information which CANNOT modify afterwards!
Below is used data for Ritika. As I've written ... there have to be Sign Out to get hours. I cannot know when Ritika should work!
5th, 12th, 19th & 26th - there are hours for those day! 15th - if no sign Out then no hours!

Those 'weekends' has hours - no fix.

You had advised earlier that night shifts should not be a challenge in calculation but i see that night shifts employees results are not accurate in your file. We don't have to change the data afterwards but it should give us the desired results which failed in Ritika's case.

How can we solve for this.



15th - if could notice 'someway' eg as I've shown for You - then there should be possible to fill form for add missing data ... and so on.
>> What did You mean? - didn't understand this completely. We cannot fill form for the missing data as the form captures the current timestamp.


can we calculate attendance basis first sign in and then the hours basis the first sign in and last sign out - your file does calculate hours but how can we fix the night shift employees data.
 
1) Something like below should able to see for every user after any Signs - to know - are they In or Out ...
Screenshot 2019-10-31 at 18.06.47.png

2) Table
Collect names,
Solve date scale and
Calculate values including breaks and include some comments.

3) There are also many other Sign ... as well as ... If someone use 'Sign In' and 'Sign Out' or not - all marks should MEAN something.
There is also routine for those breaks - I call this windy - You should has a master plan - to make some changes could mean a lot of extra work!

4) Yes, nightshifts can be challenge, but if someone WORKS eg 05-Oct then those hours belongs to 05-Oct!
Have You explained ... what do You don't figure from those? ... no!

15th) The original data is something which cannot 'fix' later by changing eg Sign In -time!
Of course, sometimes someone could remember next day (...if possible to see somehow) that previous Sign is .... In.
For those mistakes, should have own 'fixing procedure'. With that, can ADD more lines to data with needed notes.

Your last long sentence ...)
What would You mean to change? ... as written in 4) or something else?
I don't have any idea, what do we calculate ...? ... nor fixing?
 
What would You mean to change? ... as written in 4) or something else? - I was referring to your comment in reply #16
4) eg Ritika. I can notice ONLY data which You've sent. For me, it's ONLY correct information which CANNOT modify afterwards!

I don't have any idea, what do we calculate ...? ... nor fixing? - I simply need attendance days and total log in hours. Its okay, i can can get from the file you shared.

Would you be able to explain how to create such file (the one you shared with me with results)?
 
I added days and total hrs values.

Remember - previous months data the last days can effect values!
I don't understand term 'simply'...

Your would You...
As written
Collect names,
Solve date scale and
Calculate values including breaks and include some comments
from the top row to the last row step-by-step.
 

Attachments

  • Hours Calculation.xlsb
    206.2 KB · Views: 28
Back
Top