Subtracting Date/Time pairs
 

I need to subtract two dates in this format to get an elapsed time.

start time
12/15/2005 17:55

end time
12/19/2005 20:39


I would like to see it in either day decimal format (i.e 1.345 days)
or hours (55.3345 hours)

If there is a way to do this, please let me know.


jA

Subtracting Date/Time pairs
 

Simply subtract one from the other and format the resultant cell for time -
I did it using your values and got 98:44:00


"Jason Adolf" wrote in message
oups.com...
I need to subtract two dates in this format to get an elapsed time.

start time
12/15/2005 17:55

end time
12/19/2005 20:39


I would like to see it in either day decimal format (i.e 1.345 days)
or hours (55.3345 hours)

If there is a way to do this, please let me know.


jA

Subtracting Date/Time pairs
 

Subtract one from the other and multiply by 24.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Jason Adolf" wrote in message
oups.com...
I need to subtract two dates in this format to get an elapsed time.

start time
12/15/2005 17:55

end time
12/19/2005 20:39


I would like to see it in either day decimal format (i.e 1.345 days)
or hours (55.3345 hours)

If there is a way to do this, please let me know.


jA

Subtracting Date/Time pairs
 

"Jason Adolf" wrote in message
oups.com...
I need to subtract two dates in this format to get an elapsed time.

start time
12/15/2005 17:55

end time
12/19/2005 20:39

I would like to see it in either day decimal format (i.e 1.345 days)
or hours (55.3345 hours)

If there is a way to do this, please let me know.

Subtract one from the other, format as general (or number) and that is the
number of days; multiply by 24, that's the number of hours.
--
David Biddulph

Subtracting Date/Time pairs
 

Hi!!

I Have the same situation where I have a diffrent Time and Date Format

First date/time (this fixtime value and should be lower then the
second)
2006-03-28 15:30:00

Second date/time(this is the control value)
2006-03-29 15:30:00

This should provide 24:00:00 But I only get #VALUE or #N/A.

To start from the beginning I had two cells where the first one was the
date:
and the second one was the time. I used this formula to merge the
celles with DATA: =CONCATENATE(TEXT(I2;"YYYYY-MM-DD");"
";TEXT(J2;"tt:mm:ss"))

Now I have the Format YYYY-MM-DD tt:mm:ss in to difrrent columns where
I would like to meassure the time between the 2 dates and would also if
possible get the negative value if the the first date is greater the
the second.

Can anyone help me with this situation as it would be really helpfull?

I have tried something like this but it didn't work:
=NETWORKDAYS(TEXT(J23-L23;"YYYY-MM-DD tt:mm:ss"))


--
ajajmannen
------------------------------------------------------------------------
ajajmannen's Profile: http://www.excelforum.com/member.php...o&userid=34130
View this thread: http://www.excelforum.com/showthread...hreadid=542987

Subtracting Date/Time pairs
 

Try formatting it as [t]:mm:ss

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"ajajmannen" wrote
in message ...

Hi!!

I Have the same situation where I have a diffrent Time and Date Format

First date/time (this fixtime value and should be lower then the
second)
2006-03-28 15:30:00

Second date/time(this is the control value)
2006-03-29 15:30:00

This should provide 24:00:00 But I only get #VALUE or #N/A.

To start from the beginning I had two cells where the first one was the
date:
and the second one was the time. I used this formula to merge the
celles with DATA: =CONCATENATE(TEXT(I2;"YYYYY-MM-DD");"
";TEXT(J2;"tt:mm:ss"))

Now I have the Format YYYY-MM-DD tt:mm:ss in to difrrent columns where
I would like to meassure the time between the 2 dates and would also if
possible get the negative value if the the first date is greater the
the second.

Can anyone help me with this situation as it would be really helpfull?

I have tried something like this but it didn't work:
=NETWORKDAYS(TEXT(J23-L23;"YYYY-MM-DD tt:mm:ss"))


--
ajajmannen
------------------------------------------------------------------------
ajajmannen's Profile:
http://www.excelforum.com/member.php...o&userid=34130
View this thread: http://www.excelforum.com/showthread...hreadid=542987

Subtracting Date/Time pairs
 

Hi!!

I have tried to change the format on both the cells where I have merged
the date and time And the cells where I woul like the result for the
duration to take place but without luck. I now only get #VALUE.

The merge formula now looks like this:
=CONCATENATE(TEXT(I2;"ÅÅÅÅ-MM-DD");" ";TEXT(J2;"t:mm:ss"))
The duration formula looks like this:
=NETWORKDAYS(TEXT(J2-L2;"ÅÅÅÅ-MM-DD t:mm:ss"))
I have also formated the cells where the result is displayed to
[t]:mm:ss but still the same error.


--
ajajmannen
------------------------------------------------------------------------
ajajmannen's Profile: http://www.excelforum.com/member.php...o&userid=34130
View this thread: http://www.excelforum.com/showthread...hreadid=542987

Subtracting Date/Time pairs
 

Hi!!

I have tried to change the format on both the cells where I have merged
the date and time And the cells where I woul like the result for the
duration to take place but without luck. I now only get #VALUE.

The merge formula now looks like this:
=CONCATENATE(TEXT(I2;"ÅÅÅÅ-MM-DD");" ";TEXT(J2;"t:mm:ss"))
The duration formula looks like this:
=NETWORKDAYS(TEXT(J2-L2;"ÅÅÅÅ-MM-DD t:mm:ss"))
I have also formated the cells where the result is displayed to
[t]:mm:ss but still the same error.


--
ajajmannen
------------------------------------------------------------------------
ajajmannen's Profile: http://www.excelforum.com/member.php...o&userid=34130
View this thread: http://www.excelforum.com/showthread...hreadid=542987

Subtracting Date/Time pairs
 

By using the CONCATENATE formula to merge the date and time together,
you are converting it to text, and you cannot do any arithmetic with it
(which is why you are getting the #VALUE error). Use this to join the
date and time together:

=I2+J2

and format the cell as you would like it to appear. You could then just
subtract one date_time from the other to get the difference. If you
want negative times to be displayed, you will have to work in the 1904
date system.

Hope this helps.

Pete

Subtracting Date/Time pairs
 

HI!!

Thanks It worked like a charm......imaging that it was so easy:)


--
ajajmannen
------------------------------------------------------------------------
ajajmannen's Profile: http://www.excelforum.com/member.php...o&userid=34130
View this thread: http://www.excelforum.com/showthread...hreadid=542987

Subtracting Date/Time pairs
 

Thanks for the feedback.

Pete