Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works

# Frustration trying to build a simple spreadsheet

### #1 Bargod Posted 06 August 2006 - 06:35 AM

Bargod

Member

• Member
• 24 posts
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.

• 0

### #2 Octagonal Posted 06 August 2006 - 04:48 PM

Octagonal

Member 2k

• Member
• 2,528 posts
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
• 0

### #3 Bargod Posted 06 August 2006 - 05:10 PM

Bargod

Member

• Topic Starter
• Member
• 24 posts
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.

• 0

### #4 Octagonal Posted 06 August 2006 - 05:45 PM

Octagonal

Member 2k

• Member
• 2,528 posts
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.
• 0

### #5 Bargod Posted 06 August 2006 - 11:19 PM

Bargod

Member

• Topic Starter
• Member
• 24 posts
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.

• 0

### #6 Octagonal Posted 06 August 2006 - 11:35 PM

Octagonal

Member 2k

• Member
• 2,528 posts
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.
• 0

### Similar Topics

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users