Attendance Sheet How to best summarise data

[ClintonL]

Hey guys, so i have an attendance sheet using excel which people scan their card into the barcode reader and it inputs what time they clock on and off. So when they scan it inputs their code into the ID column which then populates the rest.
I have 2 questions, one is there anyway i can differientate when they clock on and off. (So the first time they scan their ID it shows as clocking on and second time shows as clocking off).
And finally a "Summary" page which contains the clock in and off time and how many hours/min they worked each day.
Ideally i want something to show the total hours someone has worked each day. I'm thinking a pivot table but i'm not too sure.

https://1drv.ms/x/s!AmyrR42BZAMLjhK8Yi_dmjFHpXX8

Link to the spreadsheet

[Ash_Maverick]

Can you attach the file here

[ClintonL]

Attached, so i need a way to differentiate when the first time you enter it in as clocking on and second time is clocking off.

[Ash_Maverick]

if you are looking for pivot to refer to data then check this.............

[ClintonL]

In the main data, is there anyway to add a field which can detect that it is the first time they clocked on and label it as that and detected it is the second time they clocked off during the day.

Cheers

[TMS]

Is the clock in time always on the same day as the clock out time?

[ClintonL]

Yep clock in is always the same day as clock out.

[ClintonL]

So i got this pivot to look like kinda like what i want. Is there anyway i can add a calculation in the pivot for each date with each of the workers to calculate the hours worked on the day?

[ClintonL]

http://www.mrexcel.com/forum/excel-q...vot-table.html

So this is pretty much what i want but with this the data already has been sorted by start/end times which i need to do.

[FlameRetired]

In the main data, is there anyway to add a field which can detect that it is the first time they clocked on and label it as that and detected it is the second time they clocked off during the day.

Cheers

Yes.

Is this what you have in mind?

The formula in E4 filled down is

Please Login or Register  to view this content.

	A	B	C	D	E
3	Date	Time	ID	Name
4	8/31/2016	7:16:54.240 AM	13997	Clinton	In
5	8/31/2016	7:17:00.660 AM	13997	Clinton	Out
6	8/31/2016	7:24:03.040 AM	15000	Neal	In
7	8/31/2016	7:24:13.680 AM	15000	Neal	Out
8	8/31/2016	7:26:38.530 AM	16000	Joan	In
9	8/31/2016	7:26:40.150 AM	16000	Joan	Out

[ClintonL]

Wow that's awesome exactly what I was looking for, thanks a lot for that. Can I just ask what exactly that formula does to get this? Also I just realised the solution in the other thread won't work with me because I can't separate the check in/out into different columns. Is there anyway other way in the pivot table i can create a calculated field to figure the amount of hours worked by summarising the difference between the in/out times? I can't find a summarise time by difference option.

[ClintonL]

So with this where it says 29/08/2016 Total I want to to give me the hours worked by subtracting out by in.

[FlameRetired]

I do not know how to do that with Pivot Tables.

I can summarize differences by formula though. I changed Sheet2 to Summary. This formula goes in C3:E7

Please Login or Register  to view this content.

	A	B	C	D	E
1		IDs -->	13997	15000	16000
2		Names -->	Clinton	Neal	Joan
3	Dates-->	8/28/2016	0:00:00.000	1:00:00.000	1:00:00.000
4		8/29/2016	1:00:00.000	1:00:00.000	1:00:00.000
5		8/30/2016	1:00:00.000	1:00:00.000	1:00:00.000
6		8/31/2016	2:00:00.000	1:00:00.000	1:00:00.000
7		9/1/2016	9:00:00.000	8:50:00.000	7:00:00.000

[ClintonL]

Hey mate, can't get that working can you upload the spreadsheet so I can download. Thanks a lot! Think the cell references are a bit off on mine as I played around with mine.

Cheers!

[ClintonL]

Or can you tell me which spreadsheet above you were using when you made that formula?

[FlameRetired]

Certainly.

Here it is.

[ClintonL]

Thanks mate, just wondering is there anyway I can add the hours worked column to Sheet1 as a column somehow so I can add it to the pivot table. Just that there are about 70 workers so having it in a pivot table is a lot neater.

Cheers

[FlameRetired]

Yes.

In Sheet1 I included this formula filled down in column F.

Please Login or Register  to view this content.

Only cells corresponding to "Out" times will return times. The "In"s will be blank. Otherwise those would show "times" with negative values.

[FlameRetired]

As an alternative you could use this formula in column F instead.

Please Login or Register  to view this content.

Then in the pivot table choose Sum for Value settings. See the Pivot sheet in the attached.

[ClintonL]

Got it all working mate, thanks a lot for the help really appreciate it! Excel never ceases to amaze me.

[FlameRetired]

You're welcome. Thanks for the feedback and the rep.

Please take time to mark this thread SOLVED.

[ClintonL]

Noted, once again thanks again saved me from endless googling.

[FlameRetired]

Try custom formatting those times 'Sum of hours worked' as [h]:mm:ss or [h]:mm if seconds aren't relevant.

If you aren't aware of it the [h] shows hours greater than 24. Without that you get the remainder or MODulo of hours/24 ... which is not what you want.

[ClintonL]

Yep that worked, sorry for being a pest but putting everything into practice at the moment and run into what i hopefully think is the last hiccup. So see where on sheet 1 the last entry on 1/9/2016 the hours worked came up with the check out time instead of the hours worked. Not sure why this is.

[ClintonL]

So it's something to do with the date formula, when I entered the dates manually it worked. And even when all I did was drag down the date formula which changed nothing in the cell the hours worked column came up correctly? Not too sure what's going on.

[ClintonL]

Really strange, when I input something into ID the formula works and it brings up the correct date. then when I double click it to 0/01/1900 as well.

[FlameRetired]

1. Yep that worked, sorry for being a pest but putting everything into practice at the moment and run into what i hopefully think is the last hiccup. So see where on sheet 1 the last entry on 1/9/2016 the hours worked came up with the check out time instead of the hours worked. Not sure why this is.

2. So it's something to do with the date formula, when I entered the dates manually it worked. And even when all I did was drag down the date formula which changed nothing in the cell the hours worked column came up correctly? Not too sure what's going on.

3. Really strange, when I input something into ID the formula works and it brings up the correct date. then when I double click it to 0/01/1900 as well.

1. The hours worked Sheet1 (F25) look correct (upload post# 24). 0:58:59 = 1:54:00 PM - 12:55:00 PM What am I missing?
2. The date formula in B24 is very strange.

   Please Login or Register  to view this content.

   I don't see how this isn't returning an error as the formula references it's own cell. Do you need a formula to enter the dates?
3. About the ID part ... I am confused. What are you double clicking ... location / cell / other?

[shukla.ankur281190]

Yes.

Is this what you have in mind?

The formula in E4 filled down is

Please Login or Register  to view this content.

	A	B	C	D	E
3	Date	Time	ID	Name
4	8/31/2016	7:16:54.240 AM	13997	Clinton	In
5	8/31/2016	7:17:00.660 AM	13997	Clinton	Out
6	8/31/2016	7:24:03.040 AM	15000	Neal	In
7	8/31/2016	7:24:13.680 AM	15000	Neal	Out
8	8/31/2016	7:26:38.530 AM	16000	Joan	In
9	8/31/2016	7:26:40.150 AM	16000	Joan	Out

Hi Mr. Dave Can you let me know you make these table here in forum pls???

[FlameRetired]

@ shukla.ankur281190

There is a list of recommended Addins at MrExcel here:

http://www.mrexcel.com/forum/excel-q...ins-links.html

The one that I primarily use is no longer listed. I have not tried any that currently are. Members speak highly of them.

The one I used above is a short macro that I "borrowed" from post# 227 here:

http://www.excelforum.com/suggestion...nd-data-6.html

[shukla.ankur281190]

Thank you Mr. Dave

[FlameRetired]

You are welcome. Thanks for the feedback and rep.

[ClintonL]

So in sheet 1 where i input my data. So type in 13997 or whatever into ID and it'll fill out a time stamp with the date/time etc.
So if you add in 2 lines at b26/b27 the hours worked won't calculate properly. It'll just say the latest check out time but it won't say the difference between the hours. If you set the date in column A manually it works but when getting it from my formula it doesn't work.

[FlameRetired]

So in sheet 1 where i input my data. So type in 13997 or whatever into ID and it'll fill out a time stamp with the date/time etc.
So if you add in 2 lines at b26/b27 the hours worked won't calculate properly. It'll just say the latest check out time but it won't say the difference between the hours. If you set the date in column A manually it works but when getting it from my formula it doesn't work.

It appears the formula creating the time stamp is the culprit. It is referencing itself.

Also ... if you are not aware of it NOW() is volatile. It will not hold its value. With each action ... editing other cells, recalculation and workbook opening ... NOW() will recalculate the current time.

In order generate times in the manner you have described you will need a macro that is triggered by an event ... typing ID into a specific cell for example. Unfortunately I do not know enough VBA to make credible suggestions. Perhaps someone watching this thread can help with that.

[ClintonL]

Actually I just got it working by replacing NOW function with TODAY. Before with the NOW function and using the date format it would show the date but I think it would include the time somewhere in the background. Where when using today it would come up just with the date function. Just how you got the formula to check if it was clock in or off, what exactly does the formula do to check it?

Cheers

[ClintonL]

Problem after problem, so the hours worked while in my pivot and in my data page I've formatted as [h]:mm. Yet in the pivot it shows the time period aka 8 hours 30min still as 830AM where I need it to show 8 hours 30min as a unit of measure so I can subtract from the staff's average daily hour to see if they're short.

[FlameRetired]

TODAY() is volatile just like NOW(). It will cause problems.

If you want the time stamps automatic and static and to calculate correctly you'll need a macro.

[ClintonL]

Ahh so the time stamps work fine for me now. Only issue I have now is that the hours worked column produces 8:30..I've set it at [h]:mm in formatting. Yet still when the hours worked goes into the pivot table it goes into the pivot table as 8:30 AM instead of just 8:30 as a duration. Not sure how I can get around this might be a glitch/bug.

[Ash_Maverick]

Clinton......can you upload the latest file here please.

[Adam_2016]

Hi,

Thanks for the reply. It is coming close to what I was asking.

E F

Clinton In1 OUT1
Clinton In 2 OUT 2


How determine duration between OUT2 and IN1, which is one working shift. Is there any If formula when thousands of such shifts are below.

Thanks,
Adam

[ClintonL]

So at my last and final step (promise)...Anyway just want to say thanks again for this you've saved my work a lot of money (I work in a childrens hospital too so should feel extra proud)..anyway My last little thing is when someone forgets to clock off so they clock in at 23/11/2016 8am then they forget they clock at 24/11/16 8am on the next day to clock in it shows as they clocked off and they worked 24 hours and the next time they clock in it's all screwed.

Is there anyway =IF(D35="","",IF(MOD(COUNTIF(D$11:D35,D35)-1,2),"Out","In"))that formula can be changed or something added so that if they don't clock out on the same day then the next time they clock it says In instead of out.

Cheers

[FlameRetired]

ClintonL,,

In the latest upload there are circular references. In fact at workbook opening I got an error message indicating circular references.

For example in cells A32 B32 of the 'Data' sheet these formulas refer to themselves.

=IF(C32<>"",IF(A32="",TODAY(),A32),"")
=IF(C32<>"",IF(B32="",NOW(),B32),"")

There are other cells with like "self-referencing".

This doesn't work. It may appear that the formulas produce the desired output due to the volatiles TODAY and NOW.
Even if they do the circular references are likely to cause problems. For example a test formula in I11 and filled down report that cells A33 and A34 are not equal.
They are both 9/29/2016.

I find indications of floating point decimals (that I am unable to resolve), but even if there weren't you still need to clean up all of these circular references and the volatiles. Please re-read posts #27, 33 and 36. I won't be able to write credible formulas until those things are done.

Once that is done it appears that you will also need a macro to input the date and time stamps. When the above is completed I will see what I can do about that.