计算登录数据库中总时间的Excel公式 [英] Calculating excel formula for total time within a sign in database

问题描述

我正在尝试根据来自我们用于建筑物出入安全系统的数据，计算出员工在建筑物中待了多长时间的公式. 数据通常如下所示:

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

但是有例外，一天中有多个打孔，连续有多个"In"打孔等.我主要关心的是当天的第一个和最后一个打孔.我知道如何将两者相减，但是我需要知道如何使用一个公式，该公式将返回彼此相减的第一个条目和最后一个条目.

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.

即

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

我可以做一个很大的if语句，说"If B8 = B7 = B6 = B5 .."，然后从底部减去顶部(A8-AX)，但这会花费很多，而且仍然不能处理所有异常.必须有一个函数，该函数返回值的数量(如countif?)，然后计算该高度的列，并返回该值内(顶部和底部)的两个单元格之差

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)

推荐答案

excel中的日期和时间保存为数字浮点值.您可以使用value()来查看其值.日期是其整数，浮点值是时间.

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.

以此为基础，假设有1名员工，这是我的示例数据.

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

还有公式:

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)

选择D1:L1并将其拖动到L9.它是标签:

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

为清楚起见，将D，E，K列格式设置为dd/mm/yyyy，将L列设置为hh:mm:ss.

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

希望有帮助. (:

这篇关于计算登录数据库中总时间的Excel公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！