I am trying to create a tracking sheet.

In cell A1 I have 01/03/05 03:30 pm
In Cell B1 I have 01/03/05 03:35 pm
In cell C1 I want to get the answer 01/03/05 00hrs 05mins
So far I can get the minutes,but how do I get the date?

Right now all can it to do is give me 01/00/00 00hrs 05mins

Try with

=INT(A1)+A2-A1

or

=TEXT(A1,"mm/dd/yy") &" "&TEXT(0,"00\h")&" "&TEXT(MINUTE(A2-A1),"00\m")

--
Regards

Juan Pablo González

"Michaela" wrote in message
...
I am trying to create a tracking sheet.

In cell A1 I have 01/03/05 03:30 pm
In Cell B1 I have 01/03/05 03:35 pm
In cell C1 I want to get the answer 01/03/05 00hrs 05mins
So far I can get the minutes,but how do I get the date?

Right now all can it to do is give me 01/00/00 00hrs 05mins

So if i understand, in A1 you have a Date & Time, in B1 a Date & Time, and in
C1, you a mix of a Date and a Duration.
=TEXT(A1,"mm/dd/yyyy") & " " & TEXT(B1-A1,"hh \hr\s mm \mn\s")
the \ are escape characters.

Regards,
Sebastien

"Michaela" wrote:

I am trying to create a tracking sheet.

In cell A1 I have 01/03/05 03:30 pm
In Cell B1 I have 01/03/05 03:35 pm
In cell C1 I want to get the answer 01/03/05 00hrs 05mins
So far I can get the minutes,but how do I get the date?

Right now all can it to do is give me 01/00/00 00hrs 05mins

Hi, if you are looking for a worksheet formula:

=TEXT(DATE(YEAR(A2), MONTH(A2), DAY(A2)), "d/m/yy") & " " &
TEXT(TIME(HOUR(A2), MINUTE(A2), SECOND(A2))-TIME(HOUR(A1), MINUTE(A1),
SECOND(A1)), "hh:mm")

If you want it in VBA, let me know. HTH--Lonnie M.

Assuming both dates are always the same you could try something like

=TEXT(FLOOR(A1,1),"mm/dd/yyyy") & " " & TEXT(B1-A1,"hh:mm")

Tim




"Michaela" wrote in message
...
I am trying to create a tracking sheet.

In cell A1 I have 01/03/05 03:30 pm
In Cell B1 I have 01/03/05 03:35 pm
In cell C1 I want to get the answer 01/03/05 00hrs 05mins
So far I can get the minutes,but how do I get the date?

Right now all can it to do is give me 01/00/00 00hrs 05mins

Lonnie, This works great, except if it runs into the next day (i.e 01/02/05
10:40 and 01/03/05 8:15). it gives me a #value.

How can I add a cutoff time and start time.

This is how I modified the formula
=TEXT(DATE(YEAR(H2), MONTH(H2), DAY(H2)), "mm/dd/yy") & " " &
TEXT(TIME(HOUR(I2), MINUTE(I2), SECOND(I2))-TIME(HOUR(H2), MINUTE(H2),
SECOND(H2)), "hh"" hrs ""mm"" mins""")

"Lonnie M." wrote:

Hi, if you are looking for a worksheet formula:

=TEXT(DATE(YEAR(A2), MONTH(A2), DAY(A2)), "d/m/yy") & " " &
TEXT(TIME(HOUR(A2), MINUTE(A2), SECOND(A2))-TIME(HOUR(A1), MINUTE(A1),
SECOND(A1)), "hh:mm")

If you want it in VBA, let me know. HTH--Lonnie M.

Ok this Formula =INT(A1)+A2-A1
adds a day to the date (i.e 01/03/05 10:30 in cell A1 and 01/04/05 11:00 in
cell B1)

When I put this formula in Cell C1 it gives me this result 01/05/05 00:30.
What I need in this case is this 01days 00 hrs 30 mins

I am using this formula but I can not modify the date

=TEXT(DATE(YEAR(B30),MONTH(B30),DAY(B30-A30)),"dd")&" "&
TEXT(TIME(HOUR(B30),MINUTE(B30),SECOND(B30))-TIME(HOUR(A30),MINUTE(A30),
SECOND(A30)), "hh"" hrs ""mm"" mins""")


"Juan Pablo González" wrote:

Try with

=INT(A1)+A2-A1

or

=TEXT(A1,"mm/dd/yy") &" "&TEXT(0,"00\h")&" "&TEXT(MINUTE(A2-A1),"00\m")

--
Regards

Juan Pablo González

"Michaela" wrote in message
...
I am trying to create a tracking sheet.

In cell A1 I have 01/03/05 03:30 pm
In Cell B1 I have 01/03/05 03:35 pm
In cell C1 I want to get the answer 01/03/05 00hrs 05mins
So far I can get the minutes,but how do I get the date?

Right now all can it to do is give me 01/00/00 00hrs 05mins