# Thread: Calculating Hours for 24 hour roster

1. I need to be able to calculate hours worked when on a 24 hour roster.

For example in

A1 is start time - 6:00 (cell format shows 06:00;00 AM); B1 is finish - 18:00 (cell format shows 06:00;00 PM); C1 contains =B1-A1 give 12:00, this is correct
A2 is start time - 20:00 (cell format shows 06:00;00 PM); B1 is finish (cell format shows 06:00;00 AM) - 06:00; C1 contains =B2-A2 give ############, but should what that means.

Any thoughts on how how to sort out a formula to provide the correct number of hours worked.

I hope this makes sense

2. One option would be to enter both the date and the time.

If you prefer to enter just the time, use the following formula in C1:

=B1-A1+(B1<A1)

Format C1 as a time, and fill down.

3. Hans - not sure if your suggested calculation is working the way I need. I have attached an example of the speadsheet being use.

Any other thoughts would be greatly appreciated

Originally Posted by HansV
One option would be to enter both the date and the time.

If you prefer to enter just the time, use the following formula in C1:

=B1-A1+(B1<A1)

Format C1 as a time, and fill down.

4. You didn't use my suggestion! It does work the way you want, see this version:

[attachment=86433:time test.xls]

5. Originally Posted by HansV
You didn't use my suggestion! It does work the way you want, see this version:

[attachment=86433:time test.xls]
The formula HansV provided does work, In your first post you mentioned calculating hours worked. Do you want to take the results and multiply the total amount by 'say an hourly rate'?

In order for me to do this, I had to tweak it a bit:

=(B2-A2+(B2<A2))*24-----and then change the format in the "C" column to number format instead of time format.

6. Hans/Stan

Calculation working OK, however is a problem with calculating total hours (see attached)

Any thoughts?

7. Times are displayed as time-of-the-day by default, i.e. they don't go above 23:59. To display cumulative time, select the cell and set a custom number format

[hh]:mm

This will show 34:00 in your example.

8. Thanks Hans - you have done it again. Very much appreciated

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•