 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Mac Forum / Applications / Excel / September 2006Help req'd with formulas: how do I total up hours using 24 hr clock
Hi, I have a spreadsheet for my timesheets. I want to load in start times and finish times in 24 format and have the formula work out the number of hours, and also the number of hours of overtime (anything more than 12 hours) I can't get a formula to give me this. Any help much appreciated. ..Mike > I have a spreadsheet for my timesheets. I want to load in start times > and finish times in 24 format and have the formula work out the number > of hours, and also the number of hours of overtime (anything more than > 12 hours) Format your time cells (apple-1, if I remember correctly) to 24 hour. Easiest is to go to Custom, and format as hh:mm:ss. When you input your times, type them in the same format, e.g. 16:30:00 for "half past 4 in the afternoon". The sums should work out easily from there. So, if A1 contains 16:30:00, A2 contains 08:45:15 and A3 contains =A1-A2, then A3 will return 07:44:45. Note that negative results will return an error. Does this help? PhilD -- <>< > Note that negative results will return an error. Only if you're using the Windows XL default 1900 date system... > I have a spreadsheet for my timesheets. I want to load in start times > and finish times in 24 format and have the formula work out the number > of hours, and also the number of hours of overtime (anything more than > 12 hours) 1) You've already been given some suggestions for formatting 24-hour time. I'd suggest going a step further - if you sum these hours and the sum exceeds 24 hours, XL's formatting will "roll over" unless you put brackets around the hours in the format: [h]:mm For instance: A1: 12:00 A2: 11:00 A3: 2:00 A4: =SUM(A1:A3) When formatted as h:mm, A4 will display 1:00. When formatted as [h]:mm, A4 will display 25:00 2) To calculate the number of hours of overtime, one way is: A1: 14:00 A2: =MAX(0, A1 - TIME(12,0,0)) ===> 2:00 when A2 is formatted as a time. Since times in XL are stored as fractional days (e.g., 3:00 == 0.125), this can be more efficiently written A2: =MAX(0, A1 - 0.5) This is how i want to layout my timesheet in the excel spreadsheet (Mac version) Date start finish Hours overtime 11-Sep 08:00 21:00 1.00 So I don't need to actually calc total hours, just hours over 12. so I just need a formula for the cell with the 1.00 in it to calc the overtime hours. Sorry if this post is confusing, but I'm new at excel and this posting business too. > This is how i want to layout my timesheet in the excel spreadsheet (Mac > version) [quoted text clipped - 4 lines] > so I just need a formula for the cell with the 1.00 in it to calc the > overtime hours. You apparently want the result in decimal hours rather than an XL time. Since XL times are stored as fractional days, you just need to multiply the result of the formula I gave you earlier by 24: D2: =MAX(0, C2-B2) * 24 Format D2 as General, or a 2-decimal Number format. > This is how i want to layout my timesheet in the excel spreadsheet (Mac > version) [quoted text clipped - 4 lines] > so I just need a formula for the cell with the 1.00 in it to calc the > overtime hours. Ah, if you format your cells as stated previously, then this should be quite straightforward, as 12 hours just happens to be 0.5 days. Suppose your data is in columns A to D, and the data in row 2. Cell D2 (where you want the 1.00 to appear) should contain =C2-B2-0.5 That's it! You are stating "9 o'clock in the evening minus 8 o'clock in the morning is 13 hours. Deduct half a day leaves 1 hour". PhilD -- <>< > Ah, if you format your cells as stated previously, then this should be > quite straightforward, as 12 hours just happens to be 0.5 days. [quoted text clipped - 6 lines] > That's it! You are stating "9 o'clock in the evening minus 8 o'clock > in the morning is 13 hours. Deduct half a day leaves 1 hour". One thing worth noting: If your times could span midnight, e.g.: Date start finish Hours overtime 11-Sep 20:00 09:00 1.00 You'll need to adjust for the finish time being smaller to XL (e.g., 0.375) than the start time (e.g., 0.8). One way to do this is to use the fact that XL treats TRUE/FALSE in math formulas as 1/0: =(C2 - B2 - 0.5 + (C2 < B2)) * 24 (Again, the * 24 is necessary to get your desired decimal hours). A somewhat obscure alternative would be to use the MOD function: =(MOD(C2 - B2, 1) - 0.5) * 24 or, equivalently, and perhaps making the intent more clear: =MOD(C2 - B2, 1) * 24 - 12 Also note that, while XL will, when the cell display formats are set to "h:mm", display 0.875 as 21:00, there's nothing inherently associated with that value that determines that it's to be interpreted as 9 o'clock in the evening. It's simply a value, so XL will happily take the square root of 9:00 PM and return 10:27 PM (i.e., SQRT(0.875) = 0.935414347). |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.