Hard Time With Time Calculation/Converting To Number

[ackalbo]

Good evening everyone, I have been trying to get this working for the most part of the day and I'm sure I'm missing something.

I am working on a Lateness Penalty Spreadsheet were people are fined X amount per minute late so I am trying to calculate how many minutes an employee is late to work and then multiply this value by an number to determine the monetary value.

First things first - Here is the raw data which is exported from our Biometric Database:

Excel 2010

	A	B	C	D	E	F
6	Raw Data (Imported From Biometric Access Database)
7
8	Emp No	Name	Date	Time In	Time Out	Duration
9	3013	Susan	22-Dec-13	4:13:01 PM	12:21:02 AM	8.13
10	3013	Susan	24-Dec-13	4:18:33 PM	2:01:13 AM	9.71
11	3013	Susan	25-Dec-13	4:27:22 PM	2:04:27 AM	9.61
12	3013	Susan	26-Dec-13	5:05:19 PM	2:59:58 AM	9.91
13	3015	Michael	22-Dec-13	5:14:49 PM	2:02:50 AM	8.8
14	3015	Michael	23-Dec-13	5:10:12 PM	1:58:08 AM	8.79
15	3015	Michael	25-Dec-13	5:00:13 PM	2:03:26 AM	9.05
16	3026	James	22-Dec-13	4:20:06 PM	2:01:30 AM	9.69
17	3026	James	25-Dec-13	4:11:40 PM	10:25:48 PM	6.23
18	3026	James	26-Dec-13	4:11:33 PM	2:55:55 AM	10.73
19	3037	Evangeline	22-Dec-13	4:28:17 PM	10:38:39 PM	6.17
20	3037	Evangeline	22-Dec-13	10:38:42 PM	10:38:48 PM	0
21	3037	Evangeline	23-Dec-13	5:52:05 PM	2:02:10 AM	8.16
22	3037	Evangeline	25-Dec-13	4:48:58 PM	2:00:27 AM	9.19
23	3037	Evangeline	26-Dec-13	5:30:16 PM	2:57:01 AM	9.44
24	3049	Jackie	22-Dec-13	4:39:38 PM	2:02:22 AM	9.37
25	3049	Jackie	23-Dec-13	4:31:45 PM	2:01:28 AM	9.49
26	3049	Jackie	25-Dec-13	4:39:34 PM	2:00:46 AM	9.35
27	3049	Jackie	26-Dec-13	5:42:01 PM	2:59:30 AM	9.29
28	3058	Ian	22-Dec-13	4:30:32 PM	2:01:39 AM	9.51
29	3058	Ian	26-Dec-13	4:52:23 PM	2:58:52 AM	10.1

<tbody>
</tbody>

Late Fine Test​

I would like to mention here that Column C (Date) is formatted as "Custom: d-mmm-yy" with a xxxxx.00 value and Column D (Time In) is formatted as "Custom: h-mm-ss AM/PM" with a xxxxx.xxxxx value.


I have so far managed to manipulate the above raw data into the following table using the following formula within the Employee Name/Date intercept cells:

{=INDEX(TimeIn,MATCH(I9,IF(DateWorked=$L$8,EmpNo),0))}

Excel 2010

	I	J	K	L	M	N	O	P	Q	R	S	T	U
22	Emp No	Name	Start Time	22-Dec-13	Late (Mins)	23-Dec-13	Late (Mins)	24-Dec-13	Late (Mins)	25-Dec-13	Late (Mins)	26-Dec-13	Late (Mins)
23	3013	Susan	17:00	4:13 PM		#N/A		4:18 PM		4:27 PM		5:05 PM
24	3015	Michael	17:00	5:14 PM		5:10 PM		#N/A		5:00 PM		#N/A
25	3026	James	17:00	4:20 PM		#N/A		#N/A		4:11 PM		4:11 PM
26	3037	Evangeline	17:00	4:28 PM		5:52 PM		#N/A		4:48 PM		5:30 PM
27	3049	Jackie	17:00	4:39 PM		4:31 PM		#N/A		4:39 PM		5:42 PM
28	3058	Ian	17:00	4:30:32 PM		#N/A		#N/A		#N/A		4:52:23 PM

<tbody>
</tbody>

Late Fine Test​

The times in Columns L, N, P, R & T are formatted as "Custom: h:mm AM/PM" with a value of xxxxx.xxxxx - The Start Time (Column K) on the other hand is formatted as "Time: 13:30" with a value of .xxxxx (In my mind I am sure this where the problem at least starts)

Using Row 24 as an example, Michael is 14 minutes late so I am trying to get "14" into cell M24 (if the employee is early then "0"). I tried

=(L24-INT(L24))-K24

but this gave a result of "12:14 AM" with an numerical value of 0.01029.

I understand the concept that Excel looks at date/time as a serial number with the date to the left of the decimal point and the time to the right, I just can't seem to achieve the results that I would like i.e. have the number "14" displayed in cell M24 and be able to multiply this by an integer (2) to come to the end result of "28" (if the employee was 55 mins late then the result would need to be 110 and not 1hr 40mins).

Any help would be greatly appreciated guys and gals as I've reached the "pulling my hair out" stage hehehe.

 

[alvin-chung]

Hi, make sure that cell format for M24 is "General" and try M24=(((HOUR(L24)-HOUR(K24))*60)+(MINUTE(L24)-MINUTE(K24)))*2

 

[ackalbo]

Alvin-chung, thank you very much - your solution works perfectly. I shall spend some time tomorrow looking at your solution and how it works

 

[alvin-chung]

You're welcome, thank you for your feedback

 

[Rick Rothstein]

Using Row 24 as an example, Michael is 14 minutes late so I am trying to get "14" into cell M24 (if the employee is early then "0"). I tried

=(L24-INT(L24))-K24

but this gave a result of "12:14 AM" with an numerical value of 0.01029.

Do you get the correct answer using this modified version of your formula?

=1440*(L24-INT(L24)-K24)

where you will probably need to manually set the cell's format to General after you enter the formula.