HELP WITH IMPORTING CLOCKING INTO EXCEL

[Newguy1924]

Hi all, I have my clock in times on an excel workbook, in list format, I need to calculate all my hours worked. The problem is that some days I clocked out for lunch break other days I didnt, some days I clocked out for tea time, some days I didnt. Some morning I forgot to clock in and others I forgot to clock out, is there a way to calculate all this taking into consideration that some days have 6 clock times and other have only 2?

 

[Toadstool]

Hi Newguy1924,

If your clock times include an indicator as to whether it's a Clock In or Clock Out then with some assumptions as to normal start/break/end times you could calculate an approximation. Without that then any approximation with the missing data would be extremely inaccurate.

 

[Newguy1924]

Yes, they have clock in and clock out next to them

 

[Toadstool]

Then post some sample data of at least a week, preferably using XL2BB or if not then copy and paste text (NOT an image).
Supply your usual start time, break time and duration, end time and I'll put something together.

 

[Newguy1924]

Ok, will do that in a minute, does it make thigs mire difficult if my lunch times are not a set time but rather random times I had time to clock out? I hope not. Please and thank you so much!

 

[Newguy1924]

Hi, I could not do it using xl2bb as I am at work and these pc's are full of crap when downloading items, hope the below helps.




1000 1000 CHARLES MILLS 2019/06/01 07:03 1 1 0 In
1000 1000 CHARLES MILLS 2019/06/01 20:25 1 1 1 Out
1000 1000 CHARLES MILLS 2019/06/02 07:52 1 1 0 In
1000 1000 CHARLES MILLS 2019/06/02 17:00 1 1 1 Out
1000 1000 CHARLES MILLS 2019/06/03 08:45 1 1 0 In
1000 1000 CHARLES MILLS 2019/06/03 08:46 1 1 0 In
1000 1000 CHARLES MILLS 2019/06/03 21:00 1 1 1 Out
1000 1000 CHARLES MILLS 2019/06/04 08:33 1 1 0 In
1000 1000 CHARLES MILLS 2019/06/04 20:12 1 1 1 Out
1000 1000 CHARLES MILLS 2019/06/05 08:39 1 1 0 In
1000 1000 CHARLES MILLS 2019/06/05 20:09 1 1 1 Out
1000 1000 CHARLES MILLS 2019/06/06 08:25 1 1 0 In
1000 1000 CHARLES MILLS 2019/06/06 12:15 1 1 2 Break
1000 1000 CHARLES MILLS 2019/06/06 13:00 1 1 0 In
1000 1000 CHARLES MILLS 2019/06/06 20:16 1 1 1 Out
1000 1000 CHARLES MILLS 2019/06/07 07:41 1 1 0 In
1000 1000 CHARLES MILLS 2019/06/07 21:01 1 1 1 Out
1000 1000 CHARLES MILLS 2019/06/08 07:54 1 1 0 In
1000 1000 CHARLES MILLS 2019/06/08 18:27 1 1 1 Out
1000 1000 CHARLES MILLS 2019/06/10 08:29 1 1 0 In
1000 1000 CHARLES MILLS 2019/06/10 20:09 1 1 1 Out

 

[Dave Patton]

T202104a.xlsm
	A	B	C	D	E	F
1	1-Jun-19	07:03	In
2	1-Jun-19	20:25	Out
3	2-Jun-19	07:52	In		Rate	$10.00
4	2-Jun-19	17:00	Out		Time	104.53
5	3-Jun-19	08:46	In		Total	$1,045.33
6	3-Jun-19	21:00	Out
7	4-Jun-19	08:33	In
8	4-Jun-19	20:12	Out
9	5-Jun-19	08:39	In
10	5-Jun-19	20:09	Out
11	6-Jun-19	08:25	In
12	6-Jun-19	12:15	Out Break
13	6-Jun-19	13:00	In
14	6-Jun-19	20:16	Out
15	7-Jun-19	07:41	In
16	7-Jun-19	21:01	Out
17	8-Jun-19	07:54	In
18	8-Jun-19	18:27	Out
19	10-Jun-19	08:29	In
20	10-Jun-19	20:09	Out
1f

Cell Formulas
Range	Formula
F4	F4	=(SUMPRODUCT(--(LEFT(C1:C20,3)="Out"),B1:B20)-SUMPRODUCT(--(LEFT(C1:C20,3)="In"),B1:B20))*24
F5	F5	=F3*F4

This was completed with assumptions including
-data is in a Text field
- you edited out the anomalies
I used Data Text to Columns
You could complete time calculation for each date change in a separate column and then sum the time.

We all volunteers on the forum. People have included the following to help posters.


One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data).

Is the data in one column or several columns?
Which data is text and which parts are real numbers?
Are the dates/times text or real numbers N.B. today is April 14 2021; Excel recognizes this as number 44300.
Are you removing the anomalies before the calculation.
What is the expected result with the data (indicative example)?
It is very helpful if the post is done with XL2BB.

 

[Newguy1924]

@Dave Patton, thank you so so much!!! This formula seems to be working! Thank you VERY VERY much!

 

[Dave Patton]

Thanks for the feedback

You may want to read a recent post

Weird Time Format

Below is a Time format I copied from our company's system in cell first cell B2 Time is 102 hours and 30 minutes which is correct but when I see in formula bar it is showing 1/4/1900 6:30:00 AM which is not correct I want to extract hours and minutes like 102:30 (102 hours and 30 minutes) and...

www.mrexcel.com