Discussion
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!
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!
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?
=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 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:I've had a look at that formula - I wouldn't even know where to put that or use it!
Highlighted cells are results of that formula. It's the same for every cell
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.
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.
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!
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!
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff