Surface Pro (2017) vs Surface Pro 4
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.
Row A Date - Name - Time In
Row B Date - Name - Time Out
How do I effectively merge those two lines to achieve an effective Time Worked.
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
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
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.
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
I hope that makes it easier to understand, but if it doesn't then please let me know
Ah! I see. You' recieve the data in the order they clock in and out!
Way above my head - sorry.
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 ?
Thank you PaulB2005 - I know the feeling
Yes they are all in sepate cells. does that make it more difficult or easier?
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.
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
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.