VBA Macro to capture the First Login & Last Logout with Total Login Hours Delivered

Dear All,

Hope you all doing good!!

https://www.excelforum.com/excel-for...ml#post5325637

Raising the request in VBA Forum as advised by JeteMC. Jete was able to provide resolution to my problem, however, the challenge was that the formula wasn't working for larger data sets as excel is taking longer time than usual and then excel/system gets stuck and crashes.

I have attached the excel which JeteMC was able to provide solution. It would be grateful, if someone could help in getting VBA Macro to capture First Login Date & Last Login Date with Time along with Total Login Hours Delivered from the given data. (In attached, data from Column A to F is the raw data which i usually update. Data given in the Column G to L is the formula provided by JeteMc.

Example Scenario given below:

Employee "A" for dated 25 April 2020 works in evening shift where he first login from evening around 6:00:00 PM and last logout next day morning around 26 April 2020 3:00:00 AM.

Same Employee "A" first login for next day i.e. 26 April 2020 is in morning Shift around 9:00:00 AM and last logout the same day 26 April 2020 at 6:00:00 PM.

What i need is that First Login at around 25 April 2020 6:00:00 PM evening and last logout next day morning around 26 April 2020 3:00:00 AM should be captured for date 25 April 2020.
And First Login at around 26 April 2020 9:00:00 AM morning Shift and last logout the same day 26 April 2020 at 6:00:00 PM should be captured for date 26 April 2020.

I would be grateful, if you could have a look and help me on the VBA Macro. Above Is an example of one such evening shifts and may vary.

Let me know for any clarifications.

Regards,
Yadavagiri

Just looking at your sheet again and as always its not making much sense to me and possibly other. It could be why you have not had a reply. So lets ask some basic questions. Please answer all of them

1) The data in the Green columns A to F, Where are you getting this from?
1a) Is It an external source ?
1b) Is it Always in this format?

2) Do YOU ONLY need to workout columns G to L?
3) You have 2 date formats 4/17/2020 and 17/4/2020 which one do you want?
4) Is column F is Seconds?
4a) I see no formula to work out F so is this data come AS IS to you and F does not need working out?

Hey Sharid, Thank you so much for looking into this and getting back to me. I have provided my answers below inline to all of your questions:

1) The data in the Green columns A to F, Where are you getting this from? ( This [A to F] is coming from the reporting tool once we download the data ).
1a) Is It an external source ? ( Yes, it’s an external source from client ).
1b) Is it Always in this format? ( Yes, it’s always in this format ).

2) Do YOU ONLY need to workout columns G to L? ( These Columns [G to L] were created by JeteMc in Excel Formula Forum {link given above to post # 18} to calculate the First Login Time. Last Logout Time, Login Hours Delivered )
3) You have 2 date formats 4/17/2020 and 17/4/2020 which one do you want? ( Date format 17/4/2020 would be fine for me ).
4) Is column F is Seconds? ( Yes, this is seconds )
4a) I see no formula to work out F so is this data come AS IS to you and F does not need working out? ( This is not a formula as this is “AS IS” which comes from the reporting tool which doesn’t require any work ).

It would be great help if you could help in this. Let me know for any sort of clarifications.

Regards,
Yadavagiri

I'm Not sure what this is what you are after, but here goes.

As I can not post large file on the forum yet. You will need to do the following, after downloading my file.

1) Copy all your data 3900+ records from your sheet to mine.
2) click the button that say Run Code.

This should get rid of your code lag problem you stated in your other post. As for date issues I will need to have another look or maybe someone else can help

Can you give a actual example on a work sheet of when the dates are wrong, I have looked at your sheet and see nothing wrong with it there are a few rows highlighted in orange, but they are recorded to show that user log on the right day,

Column G = Login = 8/5/2020 6pm
lColumn H = Log out = 9/5/202 3am

The login in column J shows 8/5/2020

So I don't understand what you are after? Is this fixed?

Hey Sharid - Many Thanks. This code helped me to get rid of my lag problem which i stated in my other post. This works on my larger data set. I appreciate your help and having spent much time on getting this fixed. You are brilliant and Star!! This is resolved for me now. Thank you so much

This has been fixed by JeteMc in the formula which i believe which you have embedded in the VBA Code in the file which you have sent to me. Many thanks again

Closing this Thread as it is resolved for now