Excel Help

[loosemarbles]

Hoping for some collective PH help ...

I have a list of dates & times (staff in & out) as follows:

25/08/2016 16:28 Out
25/08/2016 13:08 In
25/08/2016 12:06 Out
25/08/2016 08:16 In
24/08/2016 16:00 Out
24/08/2016 12:59 In
24/08/2016 11:56 Out
24/08/2016 08:06 In

Is there an easy Excel formula/work around to calculate the number of hours worked? Obviously we can pull out the time, but then we would have to manually work out the dates side of it.

Help appreciated!

[AndrewEH1]

Is all the data in the same column or three different columns?

[Ste1987]

I used to use a spreadsheet that monitored my hours. It was just a widget on my phone which sent data to Google Drive whenever I tapped it. Just brought it up and the formula used to caluculate the number of hours was:

=IF(ISEVEN(ROW()),ROUND(((DATEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4)), "\w+ \d{2}, \d{4}")) + TIMEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW(),COLUMN()-2,4)), "\d{2}:\d{2}[A|P]M$"))) - ( DATEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW()-1,COLUMN()-2,4)), "\w+ \d{2}, \d{4}")) + TIMEVALUE(REGEXEXTRACT(INDIRECT(ADDRESS(ROW()-1,COLUMN()-2,4)), "\d{2}:\d{2}[A|P]M$")))) * 24, 2),"")

Not simple, I know, but see if it works?

[loosemarbles]

The date & time are in the same column, with the corresponding in/out in a separate column.

I've had a look at that formula - I wouldn't even know where to put that or use it!

[AndrewEH1]

loosemarbles said:

The date & time are in the same column, with the corresponding in/out in a separate column.

Are they always in pairs corresponding to the same person?

[Ste1987]

loosemarbles said:

The date & time are in the same column, with the corresponding in/out in a separate column.

I've had a look at that formula - I wouldn't even know where to put that or use it!

Ah sorry, see below for a snippet of my spreadsheet:



Highlighted cells are results of that formula. It's the same for every cell

[kennydies]

A simple way I would do it is make a unique key for the users in and out time eg:


Key Name InOut Time
BobIn Bob In 25/08/2016 16:28
BobIn Bob out 25/08/2016 13:08

Then just do a vlookup on it to make a grid:

in out Time worked
Bob 25/08/2016 13:08 25/08/2016 16:28 3.33

Time worked it the diffbetween the 2 times, multipleied by 24 to get hours worked.

[louiebaby]

=IF(B1="Out",A2-A1,"")

With the timestamps in A and In/Out in B.

Formatted as time.

(As a place to start, at least.)

[loosemarbles]

Thanks all.

Having gone through the data, it is dependent on people actually logging in and out correctly ... argh ... some days apparently they never left!

I think if the data is complete, then any of the above might work, but given I have to correct the lines etc (signing in back from lunch, but not out beforehand etc), it might be easier to do it manually!