Calculation based on multiple criteria

I am trying to figure out how to calculate the amount of time it takes to work a claim based on date, time, and the username. I am trying to figure out a user's time spent going from claim to claim based on the day they worked it and the time stamp associated with it. The column I am trying to complete is the "Time to Work" column (Column E). Attached is the spreadsheet I am working from.

Time Stamp.xlsx

Thank you for the code, I have almost got what I need. The math seems to be correct, except the calculation provided is assuming the prior claim is an "open" time stamp. I need the flip side of that, where, if MDLegran closed a claim at 8:48:42 AM and the prior claim that day was closed at 7:49:53 AM, then the row with 8:48:42AM should show the 0:58:49 as the "Time to Work". As it stands with the provided code, it's showing 7:48:06 AM as 58:49. Does that make sense? Below is what I'm seeing and below that will be what I want to see.

Current provided code result:
95 11319BZ4349 5/1/2013 7:49:53 AM 0:58:49 30 MDLegran
814 12291980048 5/1/2013 8:48:42 AM 0:57:53 645 MDLegran

What I need it to look like:
95 11319BZ4349 5/1/2013 7:49:53 AM 0:25:30 30 MDLegran
814 12291980048 5/1/2013 8:48:42 AM 0:58:49 645 MDLegran (as you can see, it took 58 min to work this claim).

Thank you again for providing the code. I look forward having a solution to my issue

Hi xladept,

Thank you for responding so quickly again. To answer your question, the time stamp reflects when the user closed the claim. If the user closes claim 1 on the unique day, the time should technically be null because there is no starting point for the first claim. The 2nd claim for that day should be the time stamp for claim 2 minus the time stamp for claim 1. The 3rd claim should be the time stamp for claim 3 minus the time stamp of claim 2. The repetition continues throughout the spreadsheet based on the user and the day. The reason for this is because the time stamp shows when the user closed the claim. If the time stamp was when the user "opened" the claim, I believe your code would be spot on perfect. However, because the time stamp is based on when the claim was closed, we have to essentially "work backwards". Does that make more sense?

Originally Posted by xladept

Hi ExcelAudio,

I thought that we would know how much time was spent on the earlier project by when the next project was started - I don't get attributing 0:58:49 to 8:48:42 because at that line the next project was just started


Or, are the times logged the finishing times?

To answer your question, the logged times are the finishing times. That's probably simpler than my prior explanation.

This solved my question! Thank you so much! I am eternally grateful!

Originally Posted by xladept

Hi ExcelAudio,

This yields the 58:49

Please Login or Register  to view this content.