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