Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #9
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Legare Coleman

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •