If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Rounding time to nearest 1/4 hour
I am setting up a time schedule and need to calculate time served during a
month. If I have: b1:b31 = begin time (cells formatted as hh:mm) c1:c31 = end time (cells formatted as hh:mm) d1:d31 = total time worked (formatted as hh:mm) Problem: how do I add d1:d31 to total time worked during month to look like hh.mm with minutes converted to 100 units per hour and displayed as quarter hours. B1 = 7:15 C1 = 12:15 D1 = 7.00 B2 = 7:15 C2 = 12:30 D2 = 7.25 B3 = 7:15 C3 = 12:45 D3 = 7.50 Total hours worked = 21.75 Given: Time is entered only in quarter hours as above. Examples: If time worked was 120 hours 45minutes, result shoud be displayed as 120.75 and 95 hours 15 minutes should be displayed as 95.25. In the end, minutes needs to be converted to 100 units per hour and displayed in the quarter hour unit (25, 50, 75 or 00) Thanks in advance. Hank-B |
#2
|
|||
|
|||
XL times are stored in fractional days, so to get fractional hours,
multiply the times by 24: D1: =(C1-B1)*24 Format D1 as General or another number format, if necessary. However, you must be *much* more efficient than I am. I only can do 5 hours of work between 7:15 and 12:15, not 7. Or are you a lawyer? In article , Hank-B wrote: I am setting up a time schedule and need to calculate time served during a month. If I have: b1:b31 = begin time (cells formatted as hh:mm) c1:c31 = end time (cells formatted as hh:mm) d1:d31 = total time worked (formatted as hh:mm) Problem: how do I add d1:d31 to total time worked during month to look like hh.mm with minutes converted to 100 units per hour and displayed as quarter hours. B1 = 7:15 C1 = 12:15 D1 = 7.00 B2 = 7:15 C2 = 12:30 D2 = 7.25 B3 = 7:15 C3 = 12:45 D3 = 7.50 Total hours worked = 21.75 Given: Time is entered only in quarter hours as above. Examples: If time worked was 120 hours 45minutes, result shoud be displayed as 120.75 and 95 hours 15 minutes should be displayed as 95.25. In the end, minutes needs to be converted to 100 units per hour and displayed in the quarter hour unit (25, 50, 75 or 00) |
#3
|
|||
|
|||
Thanks Jim - that's easier than I'd imagined. I work around lawyers so it
must be wearing off - I'll have to check my sources more carefully!! Start times are really 5:51am!! "JE McGimpsey" wrote: XL times are stored in fractional days, so to get fractional hours, multiply the times by 24: D1: =(C1-B1)*24 Format D1 as General or another number format, if necessary. However, you must be *much* more efficient than I am. I only can do 5 hours of work between 7:15 and 12:15, not 7. Or are you a lawyer? In article , Hank-B wrote: I am setting up a time schedule and need to calculate time served during a month. If I have: b1:b31 = begin time (cells formatted as hh:mm) c1:c31 = end time (cells formatted as hh:mm) d1:d31 = total time worked (formatted as hh:mm) Problem: how do I add d1:d31 to total time worked during month to look like hh.mm with minutes converted to 100 units per hour and displayed as quarter hours. B1 = 7:15 C1 = 12:15 D1 = 7.00 B2 = 7:15 C2 = 12:30 D2 = 7.25 B3 = 7:15 C3 = 12:45 D3 = 7.50 Total hours worked = 21.75 Given: Time is entered only in quarter hours as above. Examples: If time worked was 120 hours 45minutes, result shoud be displayed as 120.75 and 95 hours 15 minutes should be displayed as 95.25. In the end, minutes needs to be converted to 100 units per hour and displayed in the quarter hour unit (25, 50, 75 or 00) |
#4
|
|||
|
|||
Hi
see: http://www.xldynamic.com/source/xld.Rounding.html#time -- Regards Frank Kabel Frankfurt, Germany "Hank-B" schrieb im Newsbeitrag ... I am setting up a time schedule and need to calculate time served during a month. If I have: b1:b31 = begin time (cells formatted as hh:mm) c1:c31 = end time (cells formatted as hh:mm) d1:d31 = total time worked (formatted as hh:mm) Problem: how do I add d1:d31 to total time worked during month to look like hh.mm with minutes converted to 100 units per hour and displayed as quarter hours. B1 = 7:15 C1 = 12:15 D1 = 7.00 B2 = 7:15 C2 = 12:30 D2 = 7.25 B3 = 7:15 C3 = 12:45 D3 = 7.50 Total hours worked = 21.75 Given: Time is entered only in quarter hours as above. Examples: If time worked was 120 hours 45minutes, result shoud be displayed as 120.75 and 95 hours 15 minutes should be displayed as 95.25. In the end, minutes needs to be converted to 100 units per hour and displayed in the quarter hour unit (25, 50, 75 or 00) Thanks in advance. Hank-B |
#5
|
|||
|
|||
John, not Jim.
(I'm getting pretty good on these followups vbg.) Hank-B wrote: Thanks Jim - that's easier than I'd imagined. I work around lawyers so it must be wearing off - I'll have to check my sources more carefully!! Start times are really 5:51am!! "JE McGimpsey" wrote: XL times are stored in fractional days, so to get fractional hours, multiply the times by 24: D1: =(C1-B1)*24 Format D1 as General or another number format, if necessary. However, you must be *much* more efficient than I am. I only can do 5 hours of work between 7:15 and 12:15, not 7. Or are you a lawyer? In article , Hank-B wrote: I am setting up a time schedule and need to calculate time served during a month. If I have: b1:b31 = begin time (cells formatted as hh:mm) c1:c31 = end time (cells formatted as hh:mm) d1:d31 = total time worked (formatted as hh:mm) Problem: how do I add d1:d31 to total time worked during month to look like hh.mm with minutes converted to 100 units per hour and displayed as quarter hours. B1 = 7:15 C1 = 12:15 D1 = 7.00 B2 = 7:15 C2 = 12:30 D2 = 7.25 B3 = 7:15 C3 = 12:45 D3 = 7.50 Total hours worked = 21.75 Given: Time is entered only in quarter hours as above. Examples: If time worked was 120 hours 45minutes, result shoud be displayed as 120.75 and 95 hours 15 minutes should be displayed as 95.25. In the end, minutes needs to be converted to 100 units per hour and displayed in the quarter hour unit (25, 50, 75 or 00) -- Dave Peterson |
#6
|
|||
|
|||
I'm often working at 4:00 am - my client in Hong Kong usually holds off
until then before calling me... In article , Hank-B wrote: I work around lawyers so it must be wearing off - I'll have to check my sources more carefully!! Start times are really 5:51am! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I change the default meeting time scale from 1/2 hour to 1. | sbb214 | Calendar | 0 | November 12th, 2004 05:20 PM |
Use first record found in expression? | CASJAS | Running & Setting Up Queries | 17 | July 22nd, 2004 09:21 PM |
Time off by 1 hour on two computers only in Outlook | John Schmidt | General Discussion | 2 | June 23rd, 2004 07:54 PM |
Excel Formula Help please | Seventh Day is The Sabbath | Worksheet Functions | 3 | May 22nd, 2004 10:54 AM |
24 hour time | Blair | Worksheet Functions | 4 | November 25th, 2003 07:40 PM |