Hey foks,

My user has a worksheet that has 4 columns, 2 are data entry and the other 2 are calculations

A1 B1 C1 D1
nbr (to look like date) nbr (to look like date) Calc1 Calc2
1330 2300 9:30 12:30
formatted formatted should should
looks like looks like be be
13:30 23:00 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

If using regular time entry (i.e., 13:30 - entering the colon to indicate time field) 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)
However, my user doesn't want this. The user wants to enter the "time" cell as 1330.


Does anyone know how I can change the C1 Result to show up as 9.5 - or -

do the calculations in vba and display the correct numbers in the C1/D1 columns?

Thanks,
Don

On Mon, 3 Sep 2012 03:15:06 -0700 (PDT), DonW wrote:

Does anyone know how I can change the C1 Result to show up as 9.5 - or -

When I enter the times as Excel times, with the colon, your formula returns 9, not 9.5.
Without understanding what you are trying to do in your original formula, I won't make any suggestions there.

However, to enter a time as a three or four digit number, without the colon, you can convert it into an "Excel" time using:

=--TEXT(A1,"00\:00")

The double unary, which converts the TEXT value into a Number, is not necessary if the formula is used with some other arithmetic operator (e.g. + - / * ). And it may not be necessary with the comparison operators, but check that to be sure.

"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))

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

Errata.... I wrote:
=(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.

Brain fart! The formula is right, and it returns 9.5, not 10.5.

On Sep 3, 4:42*pm, "joeu2004" wrote:
Errata.... *I wrote:
=(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.

Brain fart! *The formula is right, and it returns 9.5, not 10.5.

LOL. No problem. I was making it too complicated for my own good.
Thanks to you, joeu2004, Ron Rosenfeld and Shell Shocked. DonW