On Mon, 3 Sep 2012 09:37:15 -0700, "joeu2004" wrote:

"DonW" wrote:
A1 B1
nbr (to look like date) nbr (to look like date)
1330 2300
formatted formatted
looks like looks like
13:30 23:00

C1 D1
Calc1 Calc2
9:30 12:30
should should
be be
9.5 60.5 (70-C1)

C1 formula:
=TIME(LEFT(B1,SEARCH(":",TEXT(B1,"0"":""00"))-1),RIGHT(B1,2),0)
-TIME(LEFT(A1,SEARCH(":",TEXT(A1,"0"":""00"))-1),RIGHT(A1,2),0)
C1 Result : 9:30
[....]
Does anyone know how I can change the C1 Result to show up as 9.5

Ostensibly, just parenthesize the expression and multiply by 24 at the end
and format as Number. To wit:

=(TIME(LEFT(B1,SEARCH(":",TEXT(B1,"0"":""00"))-1),RIGHT(B1,2),0)
-TIME(LEFT(A1,SEARCH(":",TEXT(A1,"0"":""00"))-1),RIGHT(A1,2),0))*24

But that is unduly complex. Despite the formatted appearance of A1 and B1,
they really contain just the numbers 1330 and 2330. So you would write
simply:

=(TEXT(B1,"00\:00")-TEXT(A1,"00\:00"))*24

formatted as Number. Of course, that returns 10.5, not 9.5, with the
example times of 1330 and 2300.


"DonW" wrote:
If using regular time entry [....] then I have a formula
calc that works well
=IF((B1+(A1B1)-A1)*24=6.5,((B1+(A1B1)-A1)*24)-0.5,
(B1+(A1B1)-A1)*24)

Of course, that is not the same formula as the one above. And it could be
written more succinctly, to wit:

=(B1+(A1B1)-A1)*24 - 0.5*(B1+(A1B1)-A1 = TIME(6,30,0))

By the way, I would write A1=B1 instead of A1B1. Thus, if A1 and B1 are
the "same" time, it will be interpreted as 24 hours instead of zero.

Putting all this ideas together, I would write the following to fit the data
entry (no colon):

=(TEXT(B1,"00\:00")-TEXT(A1,"00\:00")+(A1=B1))*24
- 0.5*(TEXT(B1,"00\:00")-TEXT(A1,"00\:00")+(A1=B1) TIME(6,30,0))

I do not know, but there are several time sheet templates on
Microsoft's site for templates.

Mine works pretty good, and you may find what I did with 24 hour time
calcs.

http://office.microsoft.com/en-us/te...030008309.aspx