[Bargod]

I am working on a spreadsheet for work that will be used to track the breaks that our employees take. The gist of it is that someone can input the log in/log out time for each employee, and I would like the spreadsheet to calculate how long they were logged out, ideally, it would look something like this, with out the periods for the spaces so it is legible

Employee 1

.....In...........Out...........Time Difference
4:15PM......6:30PM
6:45PM......8:30PM...............15
9:17PM......10:49PM.............47
11:05PM.....1:00AM.............16


This would show that on their first break, they were logged out for 15 minutes, out for 47 minutes for lunch, and out for 16 minutes for their 2nd break. The problem I am having is getting Excel to recognize that I am inputting time, it is trying to read the colon as a decimal or something and I keep getting really skewed numbers. Is it possible to do this or do I have to just do the math manually each day for all of our employees?

Thanks,
Tony

Edited by Bargod, 06 August 2006 - 06:42 AM.

[Advertisements]

Hi Bargod,

Yes, it is possible to have Excel perform those calculations.

Format columns A and B as Time using 1:30:55 PM as the type and column C as Custom and enter h:mm into the type box.

This assumes that column A is the start time, column B is the end time and in cell C1 enter the formula =B1-A1 to show time elapsed.

This would produce the following example

IN TIME 10:40 AM (Column A)

OUT TIME 2:45 PM (Column B)

ELAPSED 4:05 (Column C)

Remember that you must put a space between the minute and the AM/PM when entering the data in Columns A and B.

HTH

[Octagonal]

Hi Bargod,

Yes, it is possible to have Excel perform those calculations.

Format columns A and B as Time using 1:30:55 PM as the type and column C as Custom and enter h:mm into the type box.

This assumes that column A is the start time, column B is the end time and in cell C1 enter the formula =B1-A1 to show time elapsed.

This would produce the following example

IN TIME 10:40 AM (Column A)

OUT TIME 2:45 PM (Column B)

ELAPSED 4:05 (Column C)

Remember that you must put a space between the minute and the AM/PM when entering the data in Columns A and B.

HTH

[Bargod]

This gets me on the right track. I am tracking the time spent logged out, so after formatting the cells for time (duh? I completely forgot about that), when I write the formula to subtract the out time (6:30 PM) form the in time (6:45 PM), it gives me 0 in the time difference column, where idealy, i want it to say 15 or however many minutes the employee was logged out. That is with a custome format in that column. Is there a better format for this in that column?

Edited by Bargod, 06 August 2006 - 05:12 PM.

[Octagonal]

I don't quite understand whats happening here. Could you please check the formula in column C as the results should be in your example (6:30 PM and 6:45 PM) 0:15.

[Bargod]

I have been playing with it for a while and changed the formatting Column C to time and it worked. Looks like the problem was the custom format there. Thanks for your help Octagonal, you definatly got me on the right path.

Edited by Bargod, 06 August 2006 - 11:20 PM.

[Octagonal]

Hi Bargod,

Great to see that you have it working now. I didn't have any probs with the custom format though.

Good luck with the project.