• 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.

Issue with Date changing after 23:59 PM for login-logout report

R

Rajashekar Reddy

New Member
Hi Team,

I've been preparing a login-logout report to know if there are any late logins for the agents who come late.

Shift starts at 00:00 AM however, agents come and login at 23:55 or 23:58 or at 23:30 i.e. before their shift time.

Problem here is there login shows as previous days date once it is 00:00.

Can someone help me on how to fix this.

Regards,
Rajashekar Reddy
 
1] In Column S5 and S7, enter 24:00 instead of 12:00 AM

2] See attached file

Regards
Bosco
 

Attachments

  • Timetest(1).xlsx
    10.7 KB · Views: 1
Rajashekar Reddy said:
i did that but its not working
Click to expand...
Hi Bosco,

I Tried your logic of using 24:00 it worked for some extent but it isn't working for guys who login late i.e. after 00:00 just because of change in date.

Please refer to I12 and I13 which are highlighted in red. Please find the attached file and let me know what needs to be done.
 

Attachments

  • Timetest(1).xlsx
    11.4 KB · Views: 6
Hi Narayan,

That looks a bit complicated. Mine is a simple question. As Bosco suggested I changed to 24:00 format and it almost worked. Now the only question is about the change of date.
 
3rd time:
Date and time in same cell.
Your shift start time was wrong.
 

Attachments

  • Timetest(1).xlsx
    14.1 KB · Views: 3
Hi ,

You have given input data.

If you can now specify the rules under which an output is to be derived , the formula will be immediately available.

If you want a 0 or a 1 in column I , just specify the conditions under which the 1 is to be derived , and the conditions under which the 0 is to be derived.

It is as simple as that , unless continuing to do it manually is even simpler.

Narayan
 
Rajashekar Reddy said:
Hi Bosco,

I Tried your logic of using 24:00 it worked for some extent but it isn't working for guys who login late i.e. after 00:00 just because of change in date.

Please refer to I12 and I13 which are highlighted in red. Please find the attached file and let me know what needs to be done.
Click to expand...
1] In cell S12 and S13, use 0:00 instead of 24:00

2] 0:00 and 24:00 both show 12:00 AM, but the actual value is
difference.

3] See attached file

Regards
Bosco
 

Attachments

  • Timetest(2).xlsx
    11.8 KB · Views: 4
Hi Bosco,

If I do so, then that would be a manual work for me to change the time format to 0:00 and 24:00 when required.

Is there any other work around for the same to happen at one go
 
Rajashekar Reddy said:
Hi Bosco,

If I do so, then that would be a manual work for me to change the time format to 0:00 and 24:00 when required.

Is there any other work around for the same to happen at one go
Click to expand...
1] You just set up a "Actual Shift" Lookup Table based on Group name instead of person name.

2] Insert a new Column G with the Identified Group name in suit with the Lookup Table

3] All the formulae used remain without changing.

Regards
Bosco
 

Attachments

  • Timetest(3).xlsx
    11.9 KB · Views: 4
Hi Reddy ,

Can you explain what the entry in row 14 means ?

The actual login date : 09-07-2017

The actual login time : 11:45:00 , which means it is mid-morning , just before noon.

The actual logout date : 09-07-2017

The actual logout time : 20:45:00 , which means it is night time.

The actual shift time : 24:00:00 or even 12:00:00 if you want it displayed that way.

What would the actual shift time be , and how would the actual login date and time correlate with this ?

Narayan
 
Hi Narayan,

Please ignore the data which is above row 21 because for those rows I've received suggestions however they are not working in reality.

Request you to check from row 22 to 25 in which I gave you the data and also the conditions.

under "late login" please display 0 or 1 based on the conditions given.

I've put "???" under late login so that you can find it easy in understanding.

Regards,
Rajashekar Reddy
 
Hi Reddy ,

This is a new requirement ; are there any more after this ?

How many devices will a person logout of and login from ? Should we just consider the earliest login on any particular date ?

Going by the two samples of data , A's will not be a late login while B's will be a late login.

If you can give your actual working file consisting of hundreds of rows of data with as much variety as possible of dates and times , it will be easier to come up with a final solution , instead of revising a solution each time a new requirement is introduced or new data invalidates a solution.

Narayan
 
You must log in or register to reply here.
Back
Top