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 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, 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.

Any suggestion or help is most appreciated.

Thanks in advance.

Hi,

Not exactly sure what you want. If you want the date from R2 and the time
from AA2 try this:
=INT(R2)+AA2-INT(AA2)

The date is the integer portion of the date-time value, the time is the
decimal portion.

I cannot understand what you are trying to do for part 2.

Hope that this helps.

Sean




"ZeroWayCool" wrote in message
oups.com...
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 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, 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.

Any suggestion or help is most appreciated.

Thanks in advance.

could you just format your cells so it only displays hours and minutes

Try this, select the cell then go to format,cells,numbers,custom,
delete all but h:mm
I tried this and it seems to work, I then referenced that cell to
another and only the time showed up, so I am sure it will work in the
formula that you are trying to do

Good Luck!!


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=528493

Myrna

If yo are around what do you reckon

No response yet???


--
davesexcel
------------------------------------------------------------------------
davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708
View this thread: http://www.excelforum.com/showthread...hreadid=528493