Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
Hi folks, I have hopefully what will be a quick and easy question.
I have two colums (A & B) that hold two date and time values, a start and complete time. Jul 19 2009 08:30 Jul 19 2009 12:54 I want to be able to find out the actual time difference, for example above would show 4:24 hours. In some cases the start and complete dates could be a day or 2 apart, therefore I would want the output to be displayed in hour format, for example: Jul 17 2009 08:30 Jul 19 2009 12:54 Column C should be 52:24 hours. Is there a way to do this. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
Hi,
=B1-A1 format as [hh]:mm Mike "msbutton27" wrote: Hi folks, I have hopefully what will be a quick and easy question. I have two colums (A & B) that hold two date and time values, a start and complete time. Jul 19 2009 08:30 Jul 19 2009 12:54 I want to be able to find out the actual time difference, for example above would show 4:24 hours. In some cases the start and complete dates could be a day or 2 apart, therefore I would want the output to be displayed in hour format, for example: Jul 17 2009 08:30 Jul 19 2009 12:54 Column C should be 52:24 hours. Is there a way to do this. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
Thanks for the reply, but the equation below fails. I setup Column A,B and C
as time you suggested below and it gives me the #VALUE! sign :( Any other suggestions on what I might be doing wrong. "Mike H" wrote: Hi, =B1-A1 format as [hh]:mm Mike "msbutton27" wrote: Hi folks, I have hopefully what will be a quick and easy question. I have two colums (A & B) that hold two date and time values, a start and complete time. Jul 19 2009 08:30 Jul 19 2009 12:54 I want to be able to find out the actual time difference, for example above would show 4:24 hours. In some cases the start and complete dates could be a day or 2 apart, therefore I would want the output to be displayed in hour format, for example: Jul 17 2009 08:30 Jul 19 2009 12:54 Column C should be 52:24 hours. Is there a way to do this. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
Format the formula cell to [h]:mm so as to get around the 24 hours..
If this post helps click Yes --------------- Jacob Skaria "msbutton27" wrote: Hi folks, I have hopefully what will be a quick and easy question. I have two colums (A & B) that hold two date and time values, a start and complete time. Jul 19 2009 08:30 Jul 19 2009 12:54 I want to be able to find out the actual time difference, for example above would show 4:24 hours. In some cases the start and complete dates could be a day or 2 apart, therefore I would want the output to be displayed in hour format, for example: Jul 17 2009 08:30 Jul 19 2009 12:54 Column C should be 52:24 hours. Is there a way to do this. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
Hi
Format column C as Custom: and in the 'type' field enter: [t]:mm Now you can simply subtract the two cells with a formula like this: =B1-A1 Regards, Per "msbutton27" skrev i meddelelsen ... Hi folks, I have hopefully what will be a quick and easy question. I have two colums (A & B) that hold two date and time values, a start and complete time. Jul 19 2009 08:30 Jul 19 2009 12:54 I want to be able to find out the actual time difference, for example above would show 4:24 hours. In some cases the start and complete dates could be a day or 2 apart, therefore I would want the output to be displayed in hour format, for example: Jul 17 2009 08:30 Jul 19 2009 12:54 Column C should be 52:24 hours. Is there a way to do this. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
"Mike H" wrote:
=B1-A1 format as [hh]:mm I think that should be the custom format [h]:mm to avoid the leading zero in 4:54, per the OP's request. And that assumes that when the OP wrote Jul 19 2009 08:30, for example, he meant that the cell contains a true date serial number, perhaps entered as Jul 19, 2009 8:30 AM, which has the custom format "mmm dd yyyy hh:mm" (without quotes). Or does the cell contain text? ----- original message ----- "Mike H" wrote in message ... Hi, =B1-A1 format as [hh]:mm Mike "msbutton27" wrote: Hi folks, I have hopefully what will be a quick and easy question. I have two colums (A & B) that hold two date and time values, a start and complete time. Jul 19 2009 08:30 Jul 19 2009 12:54 I want to be able to find out the actual time difference, for example above would show 4:24 hours. In some cases the start and complete dates could be a day or 2 apart, therefore I would want the output to be displayed in hour format, for example: Jul 17 2009 08:30 Jul 19 2009 12:54 Column C should be 52:24 hours. Is there a way to do this. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
Thanks but the [t]:mm would produce and error when trying to implement. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
Thanks for the trick, but my equation of B1-A1 still produces and error.
I wonder if there is something in the format for A & B that needs to occur. The column A contains, Month, Day, Year and Time - does this need to split. ....Mike "Jacob Skaria" wrote: Format the formula cell to [h]:mm so as to get around the 24 hours.. If this post helps click Yes --------------- Jacob Skaria "msbutton27" wrote: Hi folks, I have hopefully what will be a quick and easy question. I have two colums (A & B) that hold two date and time values, a start and complete time. Jul 19 2009 08:30 Jul 19 2009 12:54 I want to be able to find out the actual time difference, for example above would show 4:24 hours. In some cases the start and complete dates could be a day or 2 apart, therefore I would want the output to be displayed in hour format, for example: Jul 17 2009 08:30 Jul 19 2009 12:54 Column C should be 52:24 hours. Is there a way to do this. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
Sorry what is OP, is it operator?
Column A & B both follow the format: MONTH DAY YEAR 24TIME Jul 19 2009 08:30 Do i need to split this out or have a customer format. "JoeU2004" wrote: "Mike H" wrote: =B1-A1 format as [hh]:mm I think that should be the custom format [h]:mm to avoid the leading zero in 4:54, per the OP's request. And that assumes that when the OP wrote Jul 19 2009 08:30, for example, he meant that the cell contains a true date serial number, perhaps entered as Jul 19, 2009 8:30 AM, which has the custom format "mmm dd yyyy hh:mm" (without quotes). Or does the cell contain text? ----- original message ----- "Mike H" wrote in message ... Hi, =B1-A1 format as [hh]:mm Mike "msbutton27" wrote: Hi folks, I have hopefully what will be a quick and easy question. I have two colums (A & B) that hold two date and time values, a start and complete time. Jul 19 2009 08:30 Jul 19 2009 12:54 I want to be able to find out the actual time difference, for example above would show 4:24 hours. In some cases the start and complete dates could be a day or 2 apart, therefore I would want the output to be displayed in hour format, for example: Jul 17 2009 08:30 Jul 19 2009 12:54 Column C should be 52:24 hours. Is there a way to do this. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
Hi,
use =+RIGHT(B3,5)-RIGHT(A3,5) and then format column c as h:mm hope this helps "msbutton27" wrote: Thanks but the [t]:mm would produce and error when trying to implement. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
"msbutton27" wrote:
it gives me the #VALUE! I wrote in my response: And that assumes that when the OP wrote Jul 19 2009 08:30, for example, he meant that the cell contains a true date serial number, perhaps entered as Jul 19, 2009 8:30 AM, which has the custom format "mmm dd yyyy hh:mm" (without quotes). Or does the cell contain text? I think your result confirms my suspicion: you have Jul 19 2009 08:30 as text. Any other suggestions on what I might be doing wrong. Ideally, you should enter your date/time data in a form that Excel recognizes and converts into a date serial number. For example, enter Jul 19, 2009 08:30 (note the comma). You can use the custom format "mmm dd yyyy hh:mm" (without quotes) to display the date without the comma, if you wish. Alternatively, you can convert the text to a numeric value. How you do that depends on how the date Jul 1 would appear: Jul 01, Jul 1 (two spaces before 1), or Jul 1 (one space before 1). If either of the first two cases, then: =(LEFT(B1,6)&","&RIGHT(B1,11)) - (LEFT(A1,6)&","&RIGHT(A1,11)) formatted with the custom format [h]:mm. If the latter (Jul 1, one space before 1), then: =(LEFT(B1,LEN(B1)-11)&","&RIGHT(B1,11)) - (LEFT(A1,LEN(A1)-11)&","&RIGHT(A1,11)) ----- original message ----- "msbutton27" wrote in message ... Thanks for the reply, but the equation below fails. I setup Column A,B and C as time you suggested below and it gives me the #VALUE! sign :( Any other suggestions on what I might be doing wrong. "Mike H" wrote: Hi, =B1-A1 format as [hh]:mm Mike "msbutton27" wrote: Hi folks, I have hopefully what will be a quick and easy question. I have two colums (A & B) that hold two date and time values, a start and complete time. Jul 19 2009 08:30 Jul 19 2009 12:54 I want to be able to find out the actual time difference, for example above would show 4:24 hours. In some cases the start and complete dates could be a day or 2 apart, therefore I would want the output to be displayed in hour format, for example: Jul 17 2009 08:30 Jul 19 2009 12:54 Column C should be 52:24 hours. Is there a way to do this. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
I actually think I know what is going on.
I am opening a TEXT file that contains 2 columns, and 900 rows. So when I convert it from text to excel for some reason Column A or B are changing to the correct format, it is getting picked up as TEXT. I can format the columns manually and it has no affect, the only way I see to do this is to enter them in manually all over - is there a better way? ....Mike "Eduardo" wrote: Hi, use =+RIGHT(B3,5)-RIGHT(A3,5) and then format column c as h:mm hope this helps "msbutton27" wrote: Thanks but the [t]:mm would produce and error when trying to implement. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
"msbutton27" wrote:
Sorry what is OP In this context, "original poster" or "original posting". Do i need to split this out or have a customer format. See my response to one of your other postings in this thread. ----- original message ----- "msbutton27" wrote in message ... Sorry what is OP, is it operator? Column A & B both follow the format: MONTH DAY YEAR 24TIME Jul 19 2009 08:30 Do i need to split this out or have a customer format. "JoeU2004" wrote: "Mike H" wrote: =B1-A1 format as [hh]:mm I think that should be the custom format [h]:mm to avoid the leading zero in 4:54, per the OP's request. And that assumes that when the OP wrote Jul 19 2009 08:30, for example, he meant that the cell contains a true date serial number, perhaps entered as Jul 19, 2009 8:30 AM, which has the custom format "mmm dd yyyy hh:mm" (without quotes). Or does the cell contain text? ----- original message ----- "Mike H" wrote in message ... Hi, =B1-A1 format as [hh]:mm Mike "msbutton27" wrote: Hi folks, I have hopefully what will be a quick and easy question. I have two colums (A & B) that hold two date and time values, a start and complete time. Jul 19 2009 08:30 Jul 19 2009 12:54 I want to be able to find out the actual time difference, for example above would show 4:24 hours. In some cases the start and complete dates could be a day or 2 apart, therefore I would want the output to be displayed in hour format, for example: Jul 17 2009 08:30 Jul 19 2009 12:54 Column C should be 52:24 hours. Is there a way to do this. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
OP = Original Poster (aka, you!)
The information that you have in A & B...do you have it entered as text, or is it a number? The fact that you are getting the #VALUE! error seems to indicate that you have text. A quick check would be to do: =ISNUMBER(A1) which should return true. Going with the assumption that you have text, you can quickly convert your values to true date serials using this method: Type the number 1 into a blank cell. Copy that cell. Select all the cells with dates & times. Right-click, paste special. Choose "Multuiply" All your data should now be numbers, and Mike's formula will work correctly for you, with a custom format of [hh]:mm -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "msbutton27" wrote: Sorry what is OP, is it operator? Column A & B both follow the format: MONTH DAY YEAR 24TIME Jul 19 2009 08:30 Do i need to split this out or have a customer format. "JoeU2004" wrote: "Mike H" wrote: =B1-A1 format as [hh]:mm I think that should be the custom format [h]:mm to avoid the leading zero in 4:54, per the OP's request. And that assumes that when the OP wrote Jul 19 2009 08:30, for example, he meant that the cell contains a true date serial number, perhaps entered as Jul 19, 2009 8:30 AM, which has the custom format "mmm dd yyyy hh:mm" (without quotes). Or does the cell contain text? ----- original message ----- "Mike H" wrote in message ... Hi, =B1-A1 format as [hh]:mm Mike "msbutton27" wrote: Hi folks, I have hopefully what will be a quick and easy question. I have two colums (A & B) that hold two date and time values, a start and complete time. Jul 19 2009 08:30 Jul 19 2009 12:54 I want to be able to find out the actual time difference, for example above would show 4:24 hours. In some cases the start and complete dates could be a day or 2 apart, therefore I would want the output to be displayed in hour format, for example: Jul 17 2009 08:30 Jul 19 2009 12:54 Column C should be 52:24 hours. Is there a way to do this. |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
msbutton27,
This is my suspicion as well. Please see my other post for a quick, painless way to convert your text values to date serials. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "msbutton27" wrote: I actually think I know what is going on. I am opening a TEXT file that contains 2 columns, and 900 rows. So when I convert it from text to excel for some reason Column A or B are changing to the correct format, it is getting picked up as TEXT. I can format the columns manually and it has no affect, the only way I see to do this is to enter them in manually all over - is there a better way? ...Mike "Eduardo" wrote: Hi, use =+RIGHT(B3,5)-RIGHT(A3,5) and then format column c as h:mm hope this helps "msbutton27" wrote: Thanks but the [t]:mm would produce and error when trying to implement. |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
In re-examining your original post, I see that you have some extra spaces
floating around. It looks like JoeU2004 has offerered sevearl good ideas on how to extract the info you need. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Luke M" wrote: OP = Original Poster (aka, you!) The information that you have in A & B...do you have it entered as text, or is it a number? The fact that you are getting the #VALUE! error seems to indicate that you have text. A quick check would be to do: =ISNUMBER(A1) which should return true. Going with the assumption that you have text, you can quickly convert your values to true date serials using this method: Type the number 1 into a blank cell. Copy that cell. Select all the cells with dates & times. Right-click, paste special. Choose "Multuiply" All your data should now be numbers, and Mike's formula will work correctly for you, with a custom format of [hh]:mm -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "msbutton27" wrote: Sorry what is OP, is it operator? Column A & B both follow the format: MONTH DAY YEAR 24TIME Jul 19 2009 08:30 Do i need to split this out or have a customer format. "JoeU2004" wrote: "Mike H" wrote: =B1-A1 format as [hh]:mm I think that should be the custom format [h]:mm to avoid the leading zero in 4:54, per the OP's request. And that assumes that when the OP wrote Jul 19 2009 08:30, for example, he meant that the cell contains a true date serial number, perhaps entered as Jul 19, 2009 8:30 AM, which has the custom format "mmm dd yyyy hh:mm" (without quotes). Or does the cell contain text? ----- original message ----- "Mike H" wrote in message ... Hi, =B1-A1 format as [hh]:mm Mike "msbutton27" wrote: Hi folks, I have hopefully what will be a quick and easy question. I have two colums (A & B) that hold two date and time values, a start and complete time. Jul 19 2009 08:30 Jul 19 2009 12:54 I want to be able to find out the actual time difference, for example above would show 4:24 hours. In some cases the start and complete dates could be a day or 2 apart, therefore I would want the output to be displayed in hour format, for example: Jul 17 2009 08:30 Jul 19 2009 12:54 Column C should be 52:24 hours. Is there a way to do this. |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
"msbutton27" wrote:
I am opening a TEXT file that contains 2 columns, and 900 rows. [....] the only way I see to do this is to enter them in manually all over - is there a better way? I don't know of a way to do this directly at import time, nor with an Excel command after importing. If no one offers a suggestion along those lines, perhaps the following macro will be useful. First, make a copy of the Excel file or worksheet, since the effects of the macro cannot be undone. Then select the cells in the 900 rows, then execute the macro: Sub doit() Dim cell As Range For Each cell In Selection cell = Left(cell, Len(cell) - 11) & "," & Right(cell, 11) cell.NumberFormat = "mmm dd yyyy hh:mm" Next cell End Sub ----- original message ----- "msbutton27" wrote in message ... I actually think I know what is going on. I am opening a TEXT file that contains 2 columns, and 900 rows. So when I convert it from text to excel for some reason Column A or B are changing to the correct format, it is getting picked up as TEXT. I can format the columns manually and it has no affect, the only way I see to do this is to enter them in manually all over - is there a better way? ...Mike "Eduardo" wrote: Hi, use =+RIGHT(B3,5)-RIGHT(A3,5) and then format column c as h:mm hope this helps "msbutton27" wrote: Thanks but the [t]:mm would produce and error when trying to implement. |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
Thanks Everyone - I found the root cause, in the field of Column A & B there
were 2 extra spaces in front of the date/time therefore it was not converting properly. Once I removed those spaces I found the equations worked - thanks folks. ....Mike "Luke M" wrote: msbutton27, This is my suspicion as well. Please see my other post for a quick, painless way to convert your text values to date serials. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "msbutton27" wrote: I actually think I know what is going on. I am opening a TEXT file that contains 2 columns, and 900 rows. So when I convert it from text to excel for some reason Column A or B are changing to the correct format, it is getting picked up as TEXT. I can format the columns manually and it has no affect, the only way I see to do this is to enter them in manually all over - is there a better way? ...Mike "Eduardo" wrote: Hi, use =+RIGHT(B3,5)-RIGHT(A3,5) and then format column c as h:mm hope this helps "msbutton27" wrote: Thanks but the [t]:mm would produce and error when trying to implement. |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
The entries in A and B need to be in the excel date/time format..
If this post helps click Yes --------------- Jacob Skaria "msbutton27" wrote: Thanks for the trick, but my equation of B1-A1 still produces and error. I wonder if there is something in the format for A & B that needs to occur. The column A contains, Month, Day, Year and Time - does this need to split. ...Mike "Jacob Skaria" wrote: Format the formula cell to [h]:mm so as to get around the 24 hours.. If this post helps click Yes --------------- Jacob Skaria "msbutton27" wrote: Hi folks, I have hopefully what will be a quick and easy question. I have two colums (A & B) that hold two date and time values, a start and complete time. Jul 19 2009 08:30 Jul 19 2009 12:54 I want to be able to find out the actual time difference, for example above would show 4:24 hours. In some cases the start and complete dates could be a day or 2 apart, therefore I would want the output to be displayed in hour format, for example: Jul 17 2009 08:30 Jul 19 2009 12:54 Column C should be 52:24 hours. Is there a way to do this. |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to I subtract two dates & times to obtain a time
"msbutton27" wrote:
Once I removed those spaces I found the equations worked That surprises me. I thought it was the lack of a comma after the day number that caused the problem. For my edification, please post the corrected form of the date/time that you are using. And please post the value of TYPE(A1), assuming the corrected timestamps is in A1. ----- original message ----- "msbutton27" wrote in message ... Thanks Everyone - I found the root cause, in the field of Column A & B there were 2 extra spaces in front of the date/time therefore it was not converting properly. Once I removed those spaces I found the equations worked - thanks folks. ...Mike "Luke M" wrote: msbutton27, This is my suspicion as well. Please see my other post for a quick, painless way to convert your text values to date serials. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "msbutton27" wrote: I actually think I know what is going on. I am opening a TEXT file that contains 2 columns, and 900 rows. So when I convert it from text to excel for some reason Column A or B are changing to the correct format, it is getting picked up as TEXT. I can format the columns manually and it has no affect, the only way I see to do this is to enter them in manually all over - is there a better way? ...Mike "Eduardo" wrote: Hi, use =+RIGHT(B3,5)-RIGHT(A3,5) and then format column c as h:mm hope this helps "msbutton27" wrote: Thanks but the [t]:mm would produce and error when trying to implement. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation Total Time With Dates/Times That Overlaps | Excel Discussion (Misc queries) | |||
How do I find elapsed time between 2 dates and times? | Excel Worksheet Functions | |||
calculate elapsed time between dates and times | Excel Worksheet Functions | |||
HOURS DIFFERENCE IN TIME BETWEEN DIFFERENT DATES AND TIMES | Excel Worksheet Functions | |||
Subtract time and dates with restrictions | Excel Worksheet Functions |