Text Time Coversion Arithmatic

[ZeroWayCool]

Hi Guys

I am having trouble with this one and it is slightly different and
trickier than the normal time arithmatic.
Part 1
In Cell Q2 I have a rank 1 to 27 which represents a schedule, in cell
R2 I have a value 05/03/2006 16:44 this is an actual, In cell T2 I wish
to put a value that is based on another value (Cell AA has a value
02/03/2006 16:50 - I am only after the 16:50 part as this is a
scheduled time for arrival regardless of date). So In T2 in want to get
the date part of the value in R2 and then concatenate in the plan
scheduled time from Cell AA - effectively creating a planned time in T2
of 05/03/2006 16:50. There are 27 slots in AA with planned date time
values and a rank 1 to 27, I was going to try and split them and have 2
columns one with the slot value and the other with the planned time
value - I was then going to create a lookup table that I could then use
to concat values into the T2 column.
Part 2
The other problem I have is in Column U2 I was going to take the
difference between them in hh:mm and then create a countif function in
another couple of columns to determine the amount of times that values
appeared between in certain ranges eg.. less than 0, 0 - 15, 15-30 (
all in mins ).
The problem is I can't get the U2 value to come out correctly when the
actual time is greater than planned ( creates negative) and I think it
is because of my concat value in T2. The formula I used in T2 is
=DAY(R2)&"/"&MONTH(R2)&"/"&YEAR(R2)&" "&"16:50". The &"16:50" will
eventually become the lookup value unless so what can give me a better
idea. I have formatted this col to [hh]:mm.

Thank you to all who attempted to look at this before but the results
would not have solved the problem.

Any suggestion or help is most appreciated.

Thanks in advance.

 

[daddylonglegs]

I saw your other thread and one suggestion seemed to me to be valid. You
don't need to concatenate anything.

To get just the date from R2

=INT(R2)

to get just the time from AA2

=AA2-INT(A2) or MOD(AA2,1) so to get the combination you just add
these, i.e.

=INT(R2)+MOD(AA2,1)

format as dd/mm/yy hh:mm

If AA2 is the result of some sort of LOOKUP formula then you can just
amend this to

=INT(R2)+MOD(formula,1)