Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
add/sub time/hour tracking in 24 clock format
I am trying to write a formula to track time and hours based on a 24 hour
clock format (without the bloody ":" 's). My problem is, I've a formula that does what I need, but when I try to input that to my workbook, it doesn't work. In my "scratch sheet" doc, it works, when I write it into my actual book, it doesn't. Same formula. Same formatting. Doesn't work. =(S12-R12) with cell formatting of 24 hour clock, (hr:min). Also, =(F14-D14)/100. (fraction, 1/2's). . . How do I get this to give me 15 minute increments vice 30. I know, I know, format is 1/2's, but if I select quarters, it doesn't calculate/display the correct time/hours. PLEASE, can somebody help me with this? I've been banging my head against a wall on this for a bloody month!!!!!!!!! Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
add/sub time/hour tracking in 24 clock format
Try in custom format the following [h]:mm:ss The "[h]" allow for time greater than 24 hours. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=178840 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
add/sub time/hour tracking in 24 clock format
From your description, I'm assuming that your "time" values are whole
numbers representing a time. E.g, 1230 means 12:30:00 and 715 = 07:15:00. Suppose you have these times in cells A1:A6. To sum A1:A6, use the following array formula: =SUM(TIME(IF(LEN(A1:A6)=4,LEFT(A1:A6,2),LEFT(A1:A6 ,1)),RIGHT(A1:A6,2),0)) This is an array formula, so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }. You do not type in the braces -- Excel puts them in automatically. The formula will not work properly if you do not enter it with CTRL SHIFT ENTER. For much more information about array formulas, see http://www.cpearson.com/Excel/ArrayFormulas.aspx. The result of the formula above will be in standard Excel time, so if you format the cell with a custom number format of [hh]:mm (the square brackets prevent Excel from "rolling over" at 24 hours), you'll get a time like 67:25:00. With that result in, say,B14, you can convert it back to your pseudo time with the formula in B16 of =(TRUNC(B14*24)*100)+MINUTE(B14) This will change the time from 67:25:00 to 6725. If you want to round that result in B16 to the nearest half hour, use MROUND. (MROUND is built into Excel 2007. In 2003 and earlier, go to the Tools menu, choose Options, and check the item for Analysis Tool Pak). For example, in B18, use =MROUND(B16,50) This will return the integer 6750. You can wrap up all of these steps into one long array formula. With times like 1240, 715, etc in A1:A6, enter all on one line =(ROUND((TRUNC(SUM(TIME(IF(LEN(A1:A6)=4,LEFT(A1:A6 ,2),LEFT(A1:A6,1)),RIGHT(A1:A6,2),0))*24)*100)+ MINUTE(SUM(TIME(IF(LEN(A1:A6)=4,LEFT(A1:A6,2),LEFT (A1:A6,1)),RIGHT(A1:A6,2),0))),-2))+ MROUND(((TRUNC(SUM(TIME(IF(LEN(A1:A6)=4,LEFT(A1:A6 ,2),LEFT(A1:A6,1)),RIGHT(A1:A6,2),0))*24)*100)+ MINUTE(SUM(TIME(IF(LEN(A1:A6)=4,LEFT(A1:A6,2),LEFT (A1:A6,1)),RIGHT(A1:A6,2),0)))-ROUND((TRUNC(SUM(TIME(IF(LEN(A1:A6)=4, LEFT(A1:A6,2),LEFT(A1:A6,1)),RIGHT(A1:A6,2),0))*24 )*100)+MINUTE(B14),-2)),30) So, suppose A1:A6 has 1230 1545 1155 1200 800 715 the result of the formula will be 6730, indicating 67:30:00. Yes, this is a whole lot of hoops to jump through. You're probably better off just using real times. Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Fri, 12 Feb 2010 06:30:01 -0800, JsGuero wrote: I am trying to write a formula to track time and hours based on a 24 hour clock format (without the bloody ":" 's). My problem is, I've a formula that does what I need, but when I try to input that to my workbook, it doesn't work. In my "scratch sheet" doc, it works, when I write it into my actual book, it doesn't. Same formula. Same formatting. Doesn't work. =(S12-R12) with cell formatting of 24 hour clock, (hr:min). Also, =(F14-D14)/100. (fraction, 1/2's). . . How do I get this to give me 15 minute increments vice 30. I know, I know, format is 1/2's, but if I select quarters, it doesn't calculate/display the correct time/hours. PLEASE, can somebody help me with this? I've been banging my head against a wall on this for a bloody month!!!!!!!!! Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter time (12-hour clock) & have it display it? | Excel Discussion (Misc queries) | |||
counting cells within certain time frames - 24 hour clock | Excel Discussion (Misc queries) | |||
24 hour clock formula for elapsed time between 1215 and 1630 | Excel Worksheet Functions | |||
How do i use a 24 hour clock format on my timesheet? | Excel Worksheet Functions | |||
time diffrence with 24 hour clock | Excel Worksheet Functions |