# Thread: Time calculations (2003)

1. ## Time calculations (2003)

I have a SS to track hours worked and overtime (defined as more than 8 hours/day). You will see in the attachment two ranges - the yellow one represents what I want to have as a final result. The white range is what I currently have and it works to some degree except that I am unable to make it do the overtime calculations. I have tried various IFs - all to no avail. Can you hep me with cells H2:H8?

2. ## Re: Time calculations (2003)

From the column with hours worked per day, you obtain the normal hours by taking the minimum of 8 and hours worked. The overtime hours are hours worked minus normal hours.

See attached version.

3. ## Re: Time calculations (2003)

Does the attached work for you?

Excel stores an hour as 1/ 24 th of a day. See formulas in attached sheet
HTH

4. ## Re: Time calculations (2003)

Personally, I would use a colon [:] instead of period(.) as the min/sec separator. A period looks too much like a decimal point and it may confuse people
At first sight is 8.30 equal to 8 hrs and 30 mins (the way you intend) or 8.30 hrs. Using 8:30 seems less confusing (even with the label hh.mm)

Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

Steve

5. ## Re: Time calculations (2003)

Thank you all for the help. I see what I was doing wrong.

I agree that ":" works better than "." but I don't know how to make that happen. In fact, I had some trouble getting the time to format correctly and when it finally did display hh.mm, I left well enough alone.

6. ## Re: Time calculations (2003)

When I look at the spreadsheet you attached to the first post in this thread, the time values are formatted with a German (Luxemburg) time format. I thought that it was intentional, so I formatted the version I posted the same way. Here is a version with US time formats applied.

7. ## Re: Time calculations (2003)

Thank you Hans... when I look at your cell formatting, it shows a custom format ([h]:mm;@). That was preceisely the problem i was having with formattting. When I selected what I deemed appropriate for my region, it never was quite right - that is why tried different time regions until something worked.

So how did you come up with this custom format or how did you choose US time format amd make it work? I feel like I am missing something here.

8. ## Re: Time calculations (2003)

If you select the Date or Time category in the Number tab of the Format | Cells... dialog, an extra dropdown list appears below the list of types. You can select a locale in this dropdown list. After selecting a type and a locale, you can switch to Custom; the "code" for the format will be displayed in the Type box. You can edit the custom format to suit your needs.

The standard time formats include two types for accumulated time: 30:55.2 (mm:ss,0;@ as a custom format) and 37:30:55 ([h]:mm:ss;@ as a custom format). Neither is exactly what you wanted, so I modified the latter to [h]:mm;@ i.e. I removed the seconds from the format. The [h] stands for accumulative hours (can be above 24), the mm for minutes with a leading zero if necessary.

9. ## Re: Time calculations (2003)

I understand, thank you. I thought I could figure out on my own how to show the overtime hours as decimal... (2:30 = 2.5 hrs), but reformatting as general or a number does not help - neither does the INT function as suggested in Excel help.

10. ## Re: Time calculations (2003)

To change a time value (2:30) to a number (2.50) multiply the time value by 24 then format the cell as number instead of time. For example, if the time value is in cell A1, then use the formula =A1*24 and format the cell a a number.

11. ## Re: Time calculations (2003)

To show them as decimal (not time) format as general or with a number and a fixed number of decimal points and multiply the time by 24.

Excel stores dates/times in units of days and there are 24 hrs/day. The "formats" do all this conversion internally to display as desired, but the number in the cell is based on the number of days.

Excel will store a value of 2.5 hrs as 0.104166667days (2.5hr /24 hrs/day = 0.104166667 days). When you multiply the excel value by 24 you are converting the days to hours.

Steve

12. ## Re: Time calculations (2003)

Excel stores times as a fraction of 1 day. For example, 6:00 is stored as 6/24 = 0.25, and 9:00 is stored as 9/24 = 0.375. It's only the number format that displays this as a time.
If you want to have the hours as a number, you must multiply by 24. For example, cell I2 in the spreadsheet I attached contains the formula =G2-H2. To get the number of hours, change this formula to =24*(G2-H2) and set the number format to General, or to Number with the number of decimal places you want to be displayed.

#### Posting Permissions

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