-2

I have a large spreadsheet of data collected by an experimental recognition engine. It collects time started and time finished data according to facial data. The issue I have is that the varying angles and lighting cause the recognition system to think that one person is two or three (or in one particular case, 12). I have gone through and manually edited the spreadsheet to contain the correct names of workers, but I would like to calculate the hours worked by each person.

Currently, the spreadsheet looks like this:

Name        Date                    Time Started            Time Finished

Person A    17/05/2016 10:30:20 AM  17/05/2016 10:30:20 AM  17/05/2016 10:41:18 AM
Person A    17/05/2016 10:41:20 AM  17/05/2016 10:41:20 AM  17/05/2016 10:41:20 AM
Person A    17/05/2016 11:07:09 AM  17/05/2016 11:07:09 AM  17/05/2016 11:55:24 AM
Person B    17/05/2016 10:58:30 AM  17/05/2016 10:58:30 AM  17/05/2016 10:58:30 AM
Person B    17/05/2016 10:58:42 AM  17/05/2016 10:58:42 AM  17/05/2016 10:58:42 AM
Person C    17/05/2016 10:28:05 AM  17/05/2016 10:28:05 AM  17/05/2016 11:19:28 AM
Person C    17/05/2016 10:30:26 AM  17/05/2016 10:30:26 AM  17/05/2016 11:52:56 AM
Person C    17/05/2016 10:43:30 AM  17/05/2016 10:43:30 AM  17/05/2016 11:27:20 AM

This is a sample of the code, but it goes up to Person Q.

I have arranged it in order of time last seen, so basically I would like to add a column that subtracts the final time worked from the initial time worked.

For each name, the correct Hours Worked can be calculated by subtracting the first Time Started entry from the last Time Finished entry. So in this example, I can find the time worked by Person C by doing: 17/05/2016 11:27:20 AM - 17/05/2016 10:28:05 AM.

So far I have been doing this by hand, but I would much prefer a function that can look at data in the Name column, and then calculate the Hours Worked by looking for the first Time Started entry and subtracting it from the last Time Finished entry.

Can anyone provide some guidance on how to do this in Excel? If not, is such a thing possible in Excel-VBA?

Thanks in advance.

Community
  • 1
  • 1
A_toaster
  • 1,196
  • 3
  • 22
  • 50

3 Answers3

1

I tried various formula to extract Date and Time data like the effort shown inthe image given below. Full formula used in D1 was

=(DATE(MID(TRIM(C1),7,4),MID(TRIM(C1),4,2),MID(TRIM(C1),1,2))+TIME(MID(TRIM(C1),12,2),MID(TRIM(C1),15,2),MID(TRIM(C1),18,2))-=DATE(MID(TRIM(B1),7,4),MID(TRIM(B1),4,2),MID(TRIM(B1),1,2))+TIME(MID(TRIM(B1),12,2),MID(TRIM(B1),15,2),MID(TRIM(B1),18,2)))

Then Difference as shown in D1 and E1 was leading to erroneous results perhaps due to external data and system native features. Though I am able to get correct results by taking trimmed string only containing Date and Time portion but it will require adjustments for AM/PM

Sample with formulas

Looking into these problems I suggest another approach, if it is conveinient for you to adopt.

  • Copy / Paste your sheet data to Note Tab Light, a free text editor and save it as a text file.
  • Import in Excel the saved text file with fixed delimiters but remove delimiting bar between date and time portion leaving the rest as it is.
  • Put simple formula in I3 for time difference between Time Finished and Time Started i.e. =G3-E3
  • You can total by native Excel function on Person wise grouping. Sample data image as follows.

enter image description here

skkakkar
  • 2,772
  • 2
  • 17
  • 30
  • You are adding all the elapsed times. I don't think that is what he is asking for. – Ron Rosenfeld May 17 '16 at 10:37
  • @Ron Rosenfeld You are right. I think I have not gone through the question carefully. I have high regards for your experience and knowledge. I would appreciate, if you can indicate why I am getting wrong elapsed time with the formula used by me in Date function. – skkakkar May 17 '16 at 14:17
  • You are getting incorrect values because the values stored in the cells are not date strings. Excel stores dates as serial numbers usually starting at `1 Jan 1900`. So your `MID` function is returning parts of a value that might really be `42507.4377314815` and not the Date string you expect. – Ron Rosenfeld May 17 '16 at 18:50
  • @Ron Rosenfeld Thanks for your advice. I shall correct my formulas accordingly. – skkakkar May 17 '16 at 19:04
1

Add a column to your table. Call it Elapsed

In the Formulas below, note that I used Named Ranges. You can either do the same; replace them with absolute addresses; or, if your data is in a Table, replace them with the appropriate structured references.

In Excel 2016:

=MAXIFS(Time_Finished,Name,$A2)-MINIFS(Time_Started,Name,$A2)

In earlier versions you will need to enter a somewhat different formula as an array-formula. You do this by holding down ctrl+shift while hitting enter

=MAX(IF($A2=Name,Time_Finished))- MIN(IF($A2=Name,Time_Started))

This will give you an output looking like this:

enter image description here

You can then group by person in a variety of ways, depending on your requirements.

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
1

A pivot table is your friend here. I'm not sure how your date/time field is formatted, so in my screen shot I've divided into date and time fields for the start and end date/times. Pivot the table which consists of the names and the start and end date/time stamps. Row= Names Values = the end date; end time; start date; start time For each value, click on the drop-down and select 'Field Value Settings'. For the end date/time select the 'Max' values. For the start date/time select the 'Min' values. Then just put in a simple formula, and format it to be a time. Screenshot of excel pivot table showing setting

Hockney
  • 13
  • 5