Time Calculations Help

[DonW]

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+(A1>B1)-A1)*24>=6.5,((B1+(A1>B1)-A1)*24)-0.5,(B1+(A1>B1)-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

 

[Ron Rosenfeld]

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.

 

[joeu2004]

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.

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

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

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

By the way, I would write A1>=B1 instead of A1>B1. 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))

 

[CellShocked]

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.

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

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

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

By the way, I would write A1>=B1 instead of A1>B1. 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/t...-expenditure-log-job-tracker-TC030008309.aspx

 

[joeu2004]

=(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.

 

[drwilcox]

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