Time Calculations Help


D

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
 
Ad

Advertisements

J

joeu2004

DonW said:
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 said:
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))
 
C

CellShocked

DonW said:
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 said:
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
 
J

joeu2004

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

Advertisements

D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top