[SOLVED] Attempting to track late times by referencing start times in another tab

Hi there,

I have a report that I pull that shows login times for specific employees on multiple dates. I have their names and start times in the 'Names and Start Times' tab, and the report in the 'Report' tab. In the 'Report' tab I would like a formula in the Late column that checks each name in the Agent Name column and compares the name in the 'Names and Start Times' tab and then calculates the amount of time they are late. Is this possible? I would imagine that if an IF statement were to be used it might be rather large and not sure if there is an efficient way to do this.

Thank you!

FYI the names in the sheet are not real and are only used for this example.

In D3:

Please Login or Register  to view this content.

As far as I can see, there may be an issue with bebo021999's solution.
All late logins, for example D4, result in a display of "########"

One way around this is to modify bebo021999's formula as follows:
=1440*(VLOOKUP(A3,'Names and start times'!$A$2:$B$7,2,0)-C3)
1440 is the number of minutes in a day.

Now format column-D as "Number" with zero decimal places. Column-D now provides time early or late in units of minutes.

Thank you for dropping in to help guys!

I have a few questions to try to understand how this works.

Here are the steps that the formula evaluator is showing me, and we start with on row D3:

Please Login or Register  to view this content.

Questions:

1. Is there a way to show the result as a negative, ie -6 since she was early, and the late times (when they arrive late) as a positive number?
2. Whole numbers are great, but it would be even more useful to show it in actual hours and minutes, ie :06, or if they're an hour late '1:00' - does that make sense??
3. Secondly, why do we take the returned value from the VLOOKUP which is technically supposed to be a later, ie a greater/larger time than their start time? Wouldn't we want to have the start time subtracted from the VLOOKUP return vs. what you provided above since it would be [larger value (minus) smaller value]? I am trying to understand the logic there.
4. Why do we need to multiply 1440 against the result? If the column is formatted in 24h time shouldn't it return a time value of :06?

Thank you guys!!

I am a bit pressed for time so that I can't answer your questions, however based on #4 try the following in D3:

Formula:

Please Login or Register  to view this content.

Note that one drawback may be the that negative times (early) are text outputs which will make it difficult to work with if you want to do further calculations such as average of early (or early and late).
Let us know if you have any questions.

Here's an attempt at the post #4 questions:


Answers in the same order as your questions . . .

1. Sure - instead of:
   =1440*(VLOOKUP(A3,'Names and start times'!$A$2:$B$7,2,0)-C3)
   do
   =1440*(C3 - VLOOKUP(A3,'Names and start times'!$A$2:$B$7,2,0))
2. Yes, the result can be expressed as h:mm - See JeteMc's comment above but also his caveat.
3. I think this related to (1). You want positive values in col-D to mean late and negative values to mean early, which is the opposite way round to the originally supplied formula.
4. Yes you are right. If the column is formatted in 24h time then the subtraction will return a time value of 00:06:00 The catch is that Excel doesn't deal with negative times - hence in this case Excel displays "#####" which doesn't help you.
   
   You ask "Why do we need to multiply 1440?": The key thing to remember when dealing with times is that Excel simply treats a time as a number where the number 1 = 24 hours. Everything else (how times are actually displayed) is just formatting. So where you have a time, say 8:00:00, then Excel represents that as 8/24 of a day = 0.33333 so to convert that to minutes it's 8/24 * minutes in a day = 8/24 * 60*24 = 8/24 * 1440. In general, one good way to see what's going on is to change the formatting of, say, report!c3 to "number" - you will then see 0.32943. Similarly NamesAndStartTimes!B2 will be 0.33333. Subtracting gives 0.0039. Convert this fraction of a day to minutes: 0.0039*1440 = 5.62

Hope this helps rather than confuses further!

Thanks for the feedback and reputation points. If you search for "Chip Pearson time arithmetic" you will find clear and comprehensive info!

So I've run into a bit of a snag that I thought I had figured out. In my example I converted Mark Nadler's PM login times to the 24h time. However in the report I pull where I get this data they come as '4:00 PM' or '5:00 PM' in some cases. In the attached file I already used text to columns to extract the time, however it does not convert it to 16:00, 17:00 and so on. How can this formula still work for times 1:00 PM and greater? Is there a way to convert those values to military time so the calculations can still work?

In the attached file I took the liberty of changing them from 16:00 to 4:00 under Mark Nadler's times.

Thank you!!

You need to share a file that shows the problem, not one that you have changed in some way.

It may help if we could see a sample of the source data as it appears before using the text to columns.
I have a feeling that you may get better results using Get & Transform than text to columns.