Results 1 to 12 of 12
Thread: Time calculations (2003)

20041203, 23:09 #1
 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?

20041203, 23:52 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20041204, 00:24 #3
 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

20041204, 03:58 #4
 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

20041204, 12:18 #5
 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.

20041204, 12:36 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20041204, 12:41 #7
 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.

20041204, 13:08 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20041204, 15:03 #9
 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.

20041204, 15:13 #10
 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

20041204, 15:13 #11
 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

20041204, 15:17 #12
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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 =G2H2. To get the number of hours, change this formula to =24*(G2H2) and set the number format to General, or to Number with the number of decimal places you want to be displayed.