Please help! 2 parts..Calculate correct start/end times from missing numbers

Ok i have been racking my brain for the past 20 hrs trying to figure this problem out. I have uploaded a sample set of the data. I have a punch date, the punch in time and punch out time and total hours. The only thing that can be assumed by the data is that the hours and the punch date are correct. The start/end times could be wrong. What is happening is the system is leaving of an extra digit... An example is on line 9 in the file i have provided. it says 0:0:am for punch in and 3:30:pm for end time. the 0:00:am should be 10:00:00 AM as this can be determined by the total hours in column "D" of 5.5. 5.5 hours back from the 3:30PM mark would be a punch in time of 10AM. it is not only the start time that is leaving off a number but some of the end times as well. Again the hours is the only thing that is correct.

2nd part of the problem.

Need to determine which day their hours belong to.
Our work day is from 5:00 AM on a day (6/4/12) to 4:59:59 the next day (which can be indicated if the punch out time is past the 5 am. if there is time after 5 am then that has to go on the 6/5/12 work day. If a person clocks IN before 5am on 6/4 those hours up until 5 am belong on 6/3

If you need more information please ask.

I need to get this done by tonight. Please help. I will be monitoring this thread closely so i can answer any other questions you may have.

Alright I have some observations and some comments.

(1) Time format
Your times are in the format " h:mm:AM/PM ", which excel doesn't recognize as "time" and is treating as "text"; this is immediately obvious because it defaults to left alignment, while time (as a number) defaults to right alignment.

So you need to "fix" the time formatting to give a good value.

You also need to figure out a way to distinguish between 1:00 & 11:00; you might have issues with 2, 12, and 10 all mingling too. But this "slicing off the first hour digit" thing will require some clever logical function use.

When you say the times might not agree, is it because of that, or might there actually be entry errors that you need to look at?

(2) Date & Time
Date in excel is rendered as an integer that counts from a start date (Jan 1, 1900).
Time in excel is a rendered as a number that counts from 0 to 1.
What this means is you can simply ADD a cell with a date and a cell with a time and it will give both. This was an intentional feature from the programmers.
A1 = date
B1 = time
C1 = A1 + B1 // will give date & time info in one cell!

So you can probably just add the date cells to the time cells. You might have to do some logic like IF(punch out < punch in, time + date + 1, time + date) to handle an overnight shift.

Of course it might be the other way, where you assume the date matches the punch out, and then you check whether punch in happened the day before. That depends on your input though, I couldn't tell you which way to go just looking at your spreadsheet.

Anyway,

I've posted an attachment that should demo what I've described. BUT, I haven't handled the "chop off the leftmost hour digit" thing. I'd like some feedback from you first.

Cheers!

Thanks for the post. Im sorry i didn't mention i had corrected the time format issue, just having trouble making the formula to decide if there should be a 1 or not on the clock in time or the clock out time or if it is actually correct.

As far as i have gotten is to take the new reconstructed times (correct format) and see what those hours come to. IF they are different then what is on the actual sheet then do a formula to add in the 10 extra hours to determine if that comes out. It will not only add 10 to the start time but 10 to the end time in another column and then check again against the correct hours on the original.

APPRECIATE YOUR HELP TREMENDIOUSLY!!!

I've also been working on this - see attachment.

I've changed the punch times to Excel times in column F and G, and in column H compared the difference with the hours column. Columns J and K then contain the corrected times, by adjusting the start-time if necessary. I missed the things about the dates, but hope this helps.

Pete

Thanks Pete....not attachment though

Added now.

Pete

Was able to figure it out...not sure how but i got it working. Thanks for your guys help