Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtracting Date/Time pairs
Thanks for the feedback.
Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
date/time stamp | Excel Worksheet Functions | |||
date/time | Excel Worksheet Functions | |||
Date/time range based calculations | Excel Discussion (Misc queries) | |||
How do i convert a number of seconds to a date/time? | Excel Worksheet Functions |