Excel formula (Time calculation)?

Question

I need to create a formula in excel that can calculate the exact hours and/or minutes between two times, lets say that i begin to work at 9:00 pm and i end my work at 2 am so will be 5 hours, so i need a formula that can calculate this kind of data from two different cells in my spreadsheet (cell 1 the time when i enter to work, cell the time when i finished my work), hope you understand what i'm looking.

Lets say that in the cell b3 i put 22:00 (that represents 10 pm) en the cell c3 i put 03:00 (that represent 3 am) and what i want is that in the cell d3 the computer will calculate that between 22:00 (b3) and 03:00 (c3) you have 5 hours

Answer 1

Set column A as Start Time; Column B as End Time;
Insert time in this form: 6/8/2006 10:35 AM (as start time) and 6/8/2006 3:30 PM (as end time)

In C, your formula is: =TEXT(B2-A2, "h:mm:ss")

EDIT: If you don't want the seconds, make your formula: =TEXT(B2-A2, "h:mm"). Also, you can select the cells with times in it, right click, go to Format Cells, Time, and choose your preference. Email me if you need anything else or if this helps.

Answer 2

The only way I've found to do it involves using military time. The formula (entered into cell C1, in this case) is as follows...

=24*(B1-A1)

B1 is the time you clock out...A1 is the time you clock in. Let's say you work 9-5. Your beginning time should entered as 9:00 and your end time should be 17:00 (you'll need to use the colon).

If you want to account for a lunch break, use the following formula...

=24*((B1-A1)+(D1-C1))

Cell A1 is the time you clock in, Cell B1 is the time you clock out for lunch, Cell C1 is the time you clock in after lunch, Cell D1 is the time you leave. The results will be in decimal form. So 8 hours and 15 minutes will appear as 8.25.

Hope that helps. Feel free to email me if you have any questions.

Answer 3

I have been fooling around with Excel, and it looks like you can get it to recognize time formats, but adding or subtracting the times gives you an incorrect number...I would try using something other than Excel, such as an online timesheet. If you do a search for one you should be able to find it.

Answer 4

It would depend on how the data is entered into the two cells. Can you be specific on how the cells are being filled?

Answer 5

Well if you have 9 in A1 and 2 in A2, you can then click the next or second to next, box after that, then at the top in the big long empty box, you can type A1-A2 and click the check mark. I think that's what you're talking about. Hope I helped.

Answer 6

Microsoft has an on line message board for each specific application. They're sometimes difficult to find but start at microsoft.com