Excel Timesheet

  Bandy 08:58 08 Nov 05
Locked

I have modified an existing time sheet and now have a timesheet with input via a swipe card reader with results imported from Notepad. I also have a form as an alternative method of input

All works well but since the Time In and Time Out appear on different rows I now need to merge some of the figures and am a little lost in that process.

Eaxample:
Row A Date - Name - Time In
Row B Date - Name - Time Out

Question
How do I effectively merge those two lines to achieve an effective Time Worked.

  PaulB2005 09:07 08 Nov 05

If Time in is in A3 and Time out is in B3 then

=sum(b3-a3)

Will give the time worked

  Bandy 09:20 08 Nov 05

Thank you PaulB2005

The problem I have is slightly more complicated than that in that there are a number of people clocking in and out at any one time.

So whilst I can do that manually for each each individual each day I would rather try to do it automatically. That would then mean finding the appropriate rows and that's where I come unstuck

Just for information the original time sheet was in Lotus Appoach and has stood me in good stead, however rather that upgrading the old programme I thought I'd try Excel. The only limiting factor is my relatively poor knowledge of Excel

  PaulB2005 09:46 08 Nov 05

Not sure what your problem is now then. The formula is basically just a subtraction formula. Adjust it for your usage by changing the Cell references.

What do you mean by "merge those two lines to achieve an effective Time Worked"

In order to make a time sheet (and I've made a few including one for a local Prison) you just need to log people in and out and then show the time difference

Example

1/11/2005 Steve Bloggs 09:00 16:00 07:00

The last column is calculated using the formula above. Once you have done it once you can then copy and paste the formula down the sheet so as extra times are added down the sheet the formula is already there and calculates the time automatically.

  Bandy 10:57 08 Nov 05

What I'm trying to say is that a number of people clock in at say the start of the day in whatever order they arrive on site.

Later that day they all clock out again. In both cases this can be in any random order

I know that I can sort the sheet so that the rows for any given individual are next to each other and then apply the sum formulas. However I would like to try to do that automatically with say a macro or vba,if I can learn enough

The rows may looke like this:

07/11/05 - Fred - Time In
07/11/05 - Enid - Time In
07/11/05 - Joez - Time In
07/11/05 - Mary - Time In
07/11/05 - Fred - - Time Out
07/11/05 - Enid - - Time Out
07/11/05 - Joez - - Time Out
07/11/05 - Mary - - Time Out

The result I would like to finish with is, ideally:
07/11/05 - Fred - Time In - Time Out
etc

I hope that makes it easier to understand, but if it doesn't then please let me know

  PaulB2005 11:02 08 Nov 05

Ah! I see. You' recieve the data in the order they clock in and out!

Way above my head - sorry.

  Monoux 11:17 08 Nov 05

Does the information you have appear in separate cells for Date, Name, Time In, Time Out or is it all joined together in one cell ?

  Bandy 12:30 08 Nov 05

Thank you PaulB2005 - I know the feeling

Monoux
Yes they are all in sepate cells. does that make it more difficult or easier?

  recap 12:42 08 Nov 05

If you use the yellow envelope and give me your email address, I can send you a timesheet that I have used in the past, it may help you.

  Monoux 13:05 08 Nov 05

Bandy

I've created timesheet that I think fulfills your needs
If you Email me using the yellow envelope I'll send it to you to try

  Bandy 14:35 08 Nov 05

recap and Monoux

Thank you gentlemen/women

I'm finding Excel more interesting than I thought it would be, and more difficult at the same time. My brain doesn't yet seem to use the same logic as Excel.

I'm also finding visual basic and form design quite challenging but an making fair progress

This thread is now locked and can not be replied to.

Surface Pro (2017) vs Surface Pro 4

Where HTML5 is headed next

MacBook Pro v Surface Pro 5