Excel data manipulation - advice please!

[DavePa]

I appreciate that this is asking a lot but I would really appreciate some advice- even if its that what I am trying to do is not possible

 

I'm trying to extract information from an Excel spreadsheet without having to go through it by hand. The sheet in question has dates/times on it and I'm trying to establish the earliet/latest on each individual date. The data looks like this:

 

[TABLE=width: 294]

[TR]

[TD]02/01/2014[/TD]

[TD]07:44:40[/TD]

[/TR]

[TR]

[TD]02/01/2014[/TD]

[TD]12:22:30[/TD]

[/TR]

[TR]

[TD]02/01/2014[/TD]

[TD]12:47:01[/TD]

[/TR]

[TR]

[TD]02/01/2014[/TD]

[TD]14:17:33[/TD]

[/TR]

[TR]

[TD]02/01/2014[/TD]

[TD]17:21:55[/TD]

[/TR]

[TR]

[TD]03/01/2014[/TD]

[TD]07:51:44[/TD]

[/TR]

[TR]

[TD]03/01/2014[/TD]

[TD]12:13:16[/TD]

[/TR]

[TR]

[TD]03/01/2014[/TD]

[TD]13:09:20[/TD]

[/TR]

[TR]

[TD]03/01/2014[/TD]

[TD]16:45:28

[/TD]

[/TR]

[/TABLE]

 

There are a couple of problems that I'm coming up against, the 1st is that the data is actually numbers formated as dates so both columns in the 1st row are identical when formated as numbers:

 

[TABLE=width: 294]

[TR]

[TD]41641.3226876968[/TD]

[TD]41641.3226876968[/TD]

[/TR]

[/TABLE]

 

I believe that 41641 represents the date and 3226876968 the time. I've not yet looked at it but would be confident I could split that into date and time if necessary.

 

The 2nd problem is that the data is inconsistent, somedays there is only a single entry, other days there may be multiple entries so I'm not looking for the difference between 1 & 2, it could be 1 & 4 or 16 & 22. On days where there is only a single entry it won't be anything at all!

 

The data represents my clocking in and out times, I'm trying to calculate how much unpaid overtime I have done since 2014 - if I can't do it programatically I'll need to go through it manually but in that case I will only go back over the last few months. I'm happy to use Access or Excel to try and work through it but want to end up with data that looks like:

 

Date In Out Time

02/01/14 07:44 17:21 9:37

03/01/14 07:51 16:45 8:54

.

.

09/01/14 07:39 07:39 0:00 (Where only a sinlge time exists for a day)

 

Is this even possible without having to take a reporting/data manipulation course? I have very limited knowledge of Excel/Access but can copy/paste like a boss

[pcstru]

The easiest way would be to write a bit of VBA. However, you can do it without :

 

You should be able to sort the data so that the dates and times are in temporal sequence going down the sheet. This seems to be how they are displayed.

 

Insert a column C and blat in an integer sequence, 1..n, running down the sheet.

 

In column D, from the third row enter the forumla =IF(TEXT(A2,"mm/dd/yyyy")<>TEXT(A1,"mm/dd/yyyy"),"D","S"). In the second row just hardcode S. This tells us whether the date has just changed compare to the cell above.

 

In column E on the second row enter 1 and the third row the formula =IF(D3="S",E2+1,1). Copy that down the column.

 

In Column F starting on the second row enter the forumla =TEXT(A2,"dd/mm/yyyy")&E2 . Copy that down the column.

 

In Column G simply enter the formula =B2 (a copy of the time). Copy that down the column.

 

 

On a new sheet, in column A row 2., enter the first applicable date. A3 will then be =A2+1, so that you have a column of dates in sequence.

In column B, convert the date to text with =TEXT(A2,"dd/mm/yyyy")

 

On the first row, starting in column C and ending in (say) column L, enter another sequence from 1 to 10.

 

In cell C2 the forumla should be = IFERROR(VLOOKUP($B2&C$1,Sheet1!$F$2:$G$10,2,FALSE),0). Copy this across and down the grid.

 

Here we are looking up the first time for that day and if there is no time found we substitute 0. Because we sorted the data, the first time should always be the earliest time.

 

In column 0, copy the data from A, in column P copy the data from C (the earliest start time). In column Q enter the forumla =MAX(D2:M2), which should be the latest time it encounters (use TEXT function to convert to readable time or force a format.

 

Columns O,P and Q should now contain the data as you want it. Working out the hours should now be just arithmetic.

Edited November 29, 2016 by pcstru

[TechMonkey]

I'm not sure I quite understand the problem but can you not just use the MAX and MIN functions? As Excel handles dates and times as numbers all you have to do is add the date to the time to get date time as a whole and then do MIN/MAX on the resulting column.

 

Or is the issue you need the max and min for day 1, day 2, day 3?

[DavePa]

@pcstru - Thanks, I'll give that a go @TechMonkey - I want to find the min/max for each date - that's the poblem!

[pcstru]

Here's my prototype to hopefully show how it works. Thinking about it, the initial sequence could be eliminated but ...

datetest.xlsx

[DavePa]

Thanks @pcstru that worked like a dream! Mind you, I'm not sure I really needed to know the answers:

 

In 2014 I worked an average of 61 minutes a day unpaid

In 2015 this was 57 minutes a day

In 2016 it has dropped to 50 minutes a day overall - but taking just the last 3 months it has been 75 minutes

 

All of these are calculated using 8.5hrs as the day length (7.5hrs plus an hour for lunch) and I'm not even going to consider the number of short lunch breaks I take!

Edited November 29, 2016 by DavePa
Typo