Calculating excel formula for total time within a

问题:

I am trying to calculate a formula for how long an employee is in a building based upon data from our security system for building entry/exit. The data generally looks like this:

Date                 In/Out 
4/3/2017 5:41 AM     In
4/3/2017 5:34 PM    Out

But there are exceptions, multiple punches in a day, multiple "In" punches in a row etc. My main concern is the first and last punches of the day. I know how to subtract the two, but I need to know how to use a formula that will return the first entry, and the last entry, to be subtracted from one another.

My best attempt so far turned the day into an integer, then waited till the adjacent days were not equal to return a value.

i.e.

=IF(DAY(A8)<>DAY(A9),1,0)

I could do a huge if statement that says "If B8=B7=B6=B5.." then take the bottom minus the top (A8-AX) but that would take a lot and still not handle every exception. There has to be a function that returns the number of values (like a countif?) then calculates a column of that height and returns the difference of only two cells within that value (top and bottom)

回答1:

Here It is the formula which i have used is =IF(B2:B9=B2,MAX(A2:A9)-MIN(A2:A9)) , it checks for the employee id in this case it is 1 and find out the last out and 1st in time and Calculates the total Working hour of your Employee

Hope this helps

回答2:

The date and time in excel are saved as a numeric floating point values. You can use value() to see it's value. date are its integer and the floating point values are the time.

Using that as basis, assuming 1 employee, here is my sample data.

A1 = Date /time 
B1 = In/Out

A2:A9 = 
    22/7/2018 13:14:05
    22/7/2018 23:21:52
    23/7/2018 02:59:58
    23/7/2018 07:30:07
    24/7/2018 12:40:26
    24/7/2018 15:02:36
    24/7/2018 15:21:36
    24/7/2018 20:38:54

B2:B9 = 
    In
    Out
    In
    Out
    In
    Out
    In
    Out

And the formulas :

D1    =DATE(YEAR(A2),MONTH(A2),DAY(A2))
E1    =IF(D1<>D2,D2,"")
F1    =IF($B2=F$1,A2-D2,"")
G1    =IF($B2=G$1,A2-D2,"")
H1    =IF(E2<>"",F2,H1)
I1    =IF(G2="",I1,G2)
J1    =DAY(A2)
K1    =IF(J2<>J3,I2-H2,"")
L1    =IF(K2="","",D2)

select D1:L1 and drag it until L9. It's labels :

D1 = get date
E1 = new day, new date
F1 = In
G1 = Out
H1 = In2
I1 = Out2
J1 = same day check
K1 = record date
L1 = hours of the day

by the way for clarity, set column D,E,K format as dd/mm/yyyy and column L as hh:mm:ss .

Hope that helps. (: