|
Calculate difference in Times
How can I get the difference in time from 10:35:00 to 11:45:00.
I have a program to exports the following to excel: 2/9/2007 12:45:00 PM [start time] 2/9/2007 4:15:00 PM [end time] How can I create a formula to give me the diffence in these times? |
Calculate difference in Times
A1 start time
B1 end time C1 =B1-A1 format as hh:mm "Darren" wrote: How can I get the difference in time from 10:35:00 to 11:45:00. I have a program to exports the following to excel: 2/9/2007 12:45:00 PM [start time] 2/9/2007 4:15:00 PM [end time] How can I create a formula to give me the diffence in these times? |
Calculate difference in Times
This did not work. A1 = 4/2/2007 11:50:00 AM
B1 = 4/2/2007 1:15:00 PM When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting #Value. Suggestions? "Toppers" wrote: A1 start time B1 end time C1 =B1-A1 format as hh:mm "Darren" wrote: How can I get the difference in time from 10:35:00 to 11:45:00. I have a program to exports the following to excel: 2/9/2007 12:45:00 PM [start time] 2/9/2007 4:15:00 PM [end time] How can I create a formula to give me the diffence in these times? |
Calculate difference in Times
The suggestion has already been given by Toppers. Use that formula.
-- David Biddulph "Darren" wrote in message ... This did not work. A1 = 4/2/2007 11:50:00 AM B1 = 4/2/2007 1:15:00 PM When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting #Value. Suggestions? "Toppers" wrote: A1 start time B1 end time C1 =B1-A1 format as hh:mm "Darren" wrote: How can I get the difference in time from 10:35:00 to 11:45:00. I have a program to exports the following to excel: 2/9/2007 12:45:00 PM [start time] 2/9/2007 4:15:00 PM [end time] How can I create a formula to give me the diffence in these times? |
Calculate difference in Times
Thanks David, but as I have already said, that formula did not work...
Besides, how would hh:mm work when you not only have times, but also dates in the same box. like I said, I am trying to find the difference in the following: A1 = 4/2/2007 11:50:00 AM B1 = 4/2/2007 1:15:00 PM If you claim this works, then I guess I'm out of luck. I am running Excel 2003 SP2. I don't think you guys are reading my entire posting. My cell contains mm/dd/yyyy hh:mm:ss AM/PM. Thats all in one cell. Not just time. Yes, hh:mm will work if I just had time. Now, does anyone know if I can find the difference when A1 and B1 have the data shown above? Thank you. "David Biddulph" wrote: The suggestion has already been given by Toppers. Use that formula. -- David Biddulph "Darren" wrote in message ... This did not work. A1 = 4/2/2007 11:50:00 AM B1 = 4/2/2007 1:15:00 PM When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting #Value. Suggestions? "Toppers" wrote: A1 start time B1 end time C1 =B1-A1 format as hh:mm "Darren" wrote: How can I get the difference in time from 10:35:00 to 11:45:00. I have a program to exports the following to excel: 2/9/2007 12:45:00 PM [start time] 2/9/2007 4:15:00 PM [end time] How can I create a formula to give me the diffence in these times? |
Calculate difference in Times
Darren,
It DOES work ... I have just tried it with your data and get a result of 1:25 (hh:mm). And you get #VALUE because (if) you have A1-B1 which is NEGATIVE: Try B1-A1 !!!!!! "Darren" wrote: Thanks David, but as I have already said, that formula did not work... Besides, how would hh:mm work when you not only have times, but also dates in the same box. like I said, I am trying to find the difference in the following: A1 = 4/2/2007 11:50:00 AM B1 = 4/2/2007 1:15:00 PM If you claim this works, then I guess I'm out of luck. I am running Excel 2003 SP2. I don't think you guys are reading my entire posting. My cell contains mm/dd/yyyy hh:mm:ss AM/PM. Thats all in one cell. Not just time. Yes, hh:mm will work if I just had time. Now, does anyone know if I can find the difference when A1 and B1 have the data shown above? Thank you. "David Biddulph" wrote: The suggestion has already been given by Toppers. Use that formula. -- David Biddulph "Darren" wrote in message ... This did not work. A1 = 4/2/2007 11:50:00 AM B1 = 4/2/2007 1:15:00 PM When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting #Value. Suggestions? "Toppers" wrote: A1 start time B1 end time C1 =B1-A1 format as hh:mm "Darren" wrote: How can I get the difference in time from 10:35:00 to 11:45:00. I have a program to exports the following to excel: 2/9/2007 12:45:00 PM [start time] 2/9/2007 4:15:00 PM [end time] How can I create a formula to give me the diffence in these times? |
Calculate difference in Times
|
Calculate difference in Times
So toppers, if I had
A1 = 02/04/2007 10:30:00 B1 = 02/05/2007 10:30:00 C1=A1-B1 Will I get 24:00:00 for an answer? In your test, what happened to the dates? "Toppers" wrote: Darren, It DOES work ... I have just tried it with your data and get a result of 1:25 (hh:mm). And you get #VALUE because (if) you have A1-B1 which is NEGATIVE: Try B1-A1 !!!!!! "Darren" wrote: Thanks David, but as I have already said, that formula did not work... Besides, how would hh:mm work when you not only have times, but also dates in the same box. like I said, I am trying to find the difference in the following: A1 = 4/2/2007 11:50:00 AM B1 = 4/2/2007 1:15:00 PM If you claim this works, then I guess I'm out of luck. I am running Excel 2003 SP2. I don't think you guys are reading my entire posting. My cell contains mm/dd/yyyy hh:mm:ss AM/PM. Thats all in one cell. Not just time. Yes, hh:mm will work if I just had time. Now, does anyone know if I can find the difference when A1 and B1 have the data shown above? Thank you. "David Biddulph" wrote: The suggestion has already been given by Toppers. Use that formula. -- David Biddulph "Darren" wrote in message ... This did not work. A1 = 4/2/2007 11:50:00 AM B1 = 4/2/2007 1:15:00 PM When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting #Value. Suggestions? "Toppers" wrote: A1 start time B1 end time C1 =B1-A1 format as hh:mm "Darren" wrote: How can I get the difference in time from 10:35:00 to 11:45:00. I have a program to exports the following to excel: 2/9/2007 12:45:00 PM [start time] 2/9/2007 4:15:00 PM [end time] How can I create a formula to give me the diffence in these times? |
Calculate difference in Times
|
Calculate difference in Times
A B
c 1 02/04/2007 12:30:00 02/04/2007 12:56:03 =B1-A1 all 3 columns have been formated to Category Custom type [hh]:mm Am I getting it all right? I still get #Value. I gotta start banging my head on a wall :) Darren "Toppers" wrote: For hours = 24 format as [hh]:mm "Darren" wrote: If you get this to work, could you email me a copy of the spreadsheet: Thanks Darren "Toppers" wrote: Darren, It DOES work ... I have just tried it with your data and get a result of 1:25 (hh:mm). And you get #VALUE because (if) you have A1-B1 which is NEGATIVE: Try B1-A1 !!!!!! "Darren" wrote: Thanks David, but as I have already said, that formula did not work... Besides, how would hh:mm work when you not only have times, but also dates in the same box. like I said, I am trying to find the difference in the following: A1 = 4/2/2007 11:50:00 AM B1 = 4/2/2007 1:15:00 PM If you claim this works, then I guess I'm out of luck. I am running Excel 2003 SP2. I don't think you guys are reading my entire posting. My cell contains mm/dd/yyyy hh:mm:ss AM/PM. Thats all in one cell. Not just time. Yes, hh:mm will work if I just had time. Now, does anyone know if I can find the difference when A1 and B1 have the data shown above? Thank you. "David Biddulph" wrote: The suggestion has already been given by Toppers. Use that formula. -- David Biddulph "Darren" wrote in message ... This did not work. A1 = 4/2/2007 11:50:00 AM B1 = 4/2/2007 1:15:00 PM When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting #Value. Suggestions? "Toppers" wrote: A1 start time B1 end time C1 =B1-A1 format as hh:mm "Darren" wrote: How can I get the difference in time from 10:35:00 to 11:45:00. I have a program to exports the following to excel: 2/9/2007 12:45:00 PM [start time] 2/9/2007 4:15:00 PM [end time] How can I create a formula to give me the diffence in these times? |
Calculate difference in Times
NO ...only C BUT even if they are formatted as C it will still wotk.
A & B are date/times CUSTOM= mm/dd/yyyy hh:mm (I assume US format) Are you sure the fields are dates not text as if the fields are displayed as you show in your posting they CANNOT be formatted as [HH]:MM as the date would not be present? If you want more help send sample to: toppers at REMOVETHISjohntopley.fsnet.co.uk "Darren" wrote: A B c 1 02/04/2007 12:30:00 02/04/2007 12:56:03 =B1-A1 all 3 columns have been formated to Category Custom type [hh]:mm Am I getting it all right? I still get #Value. I gotta start banging my head on a wall :) Darren "Toppers" wrote: For hours = 24 format as [hh]:mm "Darren" wrote: If you get this to work, could you email me a copy of the spreadsheet: Thanks Darren "Toppers" wrote: Darren, It DOES work ... I have just tried it with your data and get a result of 1:25 (hh:mm). And you get #VALUE because (if) you have A1-B1 which is NEGATIVE: Try B1-A1 !!!!!! "Darren" wrote: Thanks David, but as I have already said, that formula did not work... Besides, how would hh:mm work when you not only have times, but also dates in the same box. like I said, I am trying to find the difference in the following: A1 = 4/2/2007 11:50:00 AM B1 = 4/2/2007 1:15:00 PM If you claim this works, then I guess I'm out of luck. I am running Excel 2003 SP2. I don't think you guys are reading my entire posting. My cell contains mm/dd/yyyy hh:mm:ss AM/PM. Thats all in one cell. Not just time. Yes, hh:mm will work if I just had time. Now, does anyone know if I can find the difference when A1 and B1 have the data shown above? Thank you. "David Biddulph" wrote: The suggestion has already been given by Toppers. Use that formula. -- David Biddulph "Darren" wrote in message ... This did not work. A1 = 4/2/2007 11:50:00 AM B1 = 4/2/2007 1:15:00 PM When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting #Value. Suggestions? "Toppers" wrote: A1 start time B1 end time C1 =B1-A1 format as hh:mm "Darren" wrote: How can I get the difference in time from 10:35:00 to 11:45:00. I have a program to exports the following to excel: 2/9/2007 12:45:00 PM [start time] 2/9/2007 4:15:00 PM [end time] How can I create a formula to give me the diffence in these times? |
Calculate difference in Times
If you are struggling to understand the replies in English in this group,
you may be better with a group in your own language? Toppers suggested =B1-A1. You told us that =A1-B1 didn't work. For the example you gave, hh:mm will be fine as a format for the answer. If you have a date & time span which will go beyond 24 hours, format the result as [h]:mm -- David Biddulph "Darren" wrote in message ... Thanks David, but as I have already said, that formula did not work... Besides, how would hh:mm work when you not only have times, but also dates in the same box. like I said, I am trying to find the difference in the following: A1 = 4/2/2007 11:50:00 AM B1 = 4/2/2007 1:15:00 PM If you claim this works, then I guess I'm out of luck. I am running Excel 2003 SP2. I don't think you guys are reading my entire posting. My cell contains mm/dd/yyyy hh:mm:ss AM/PM. Thats all in one cell. Not just time. Yes, hh:mm will work if I just had time. Now, does anyone know if I can find the difference when A1 and B1 have the data shown above? Thank you. "David Biddulph" wrote: The suggestion has already been given by Toppers. Use that formula. -- David Biddulph "Darren" wrote in message ... This did not work. A1 = 4/2/2007 11:50:00 AM B1 = 4/2/2007 1:15:00 PM When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting #Value. Suggestions? "Toppers" wrote: A1 start time B1 end time C1 =B1-A1 format as hh:mm "Darren" wrote: How can I get the difference in time from 10:35:00 to 11:45:00. I have a program to exports the following to excel: 2/9/2007 12:45:00 PM [start time] 2/9/2007 4:15:00 PM [end time] How can I create a formula to give me the diffence in these times? |
Calculate difference in Times
You are right, David, I did accidentally put A1-B1 in my question. Toppers
understood that was a mistake just by the question I had asked. Anyways, Update: toppers continued to try to help me via email, and the formula would work in his excel, but when I opened the page in my excel, the formula would not work. I did figure out, though, why this was happening. In my "Customize Regional Settings", I had my date separator as "-" rather than "/". The "-" works great for my other database work, but apparently excel does not like it. so it is fixed now, thank you for your help, Topper. As for you David, I did not like you insinuating that I do not understand the english language. You need to think outside of the box, as this example shows, the question seemed easy, but there was a quirk about getting it to work for everyone. "David Biddulph" wrote: If you are struggling to understand the replies in English in this group, you may be better with a group in your own language? Toppers suggested =B1-A1. You told us that =A1-B1 didn't work. For the example you gave, hh:mm will be fine as a format for the answer. If you have a date & time span which will go beyond 24 hours, format the result as [h]:mm -- David Biddulph "Darren" wrote in message ... Thanks David, but as I have already said, that formula did not work... Besides, how would hh:mm work when you not only have times, but also dates in the same box. like I said, I am trying to find the difference in the following: A1 = 4/2/2007 11:50:00 AM B1 = 4/2/2007 1:15:00 PM If you claim this works, then I guess I'm out of luck. I am running Excel 2003 SP2. I don't think you guys are reading my entire posting. My cell contains mm/dd/yyyy hh:mm:ss AM/PM. Thats all in one cell. Not just time. Yes, hh:mm will work if I just had time. Now, does anyone know if I can find the difference when A1 and B1 have the data shown above? Thank you. "David Biddulph" wrote: The suggestion has already been given by Toppers. Use that formula. -- David Biddulph "Darren" wrote in message ... This did not work. A1 = 4/2/2007 11:50:00 AM B1 = 4/2/2007 1:15:00 PM When I format the cells for hh:mm, C1=A1- B1 gives me #Value. I tried formatting the cells to mm/dd/yyyy h:mm:ss AM/PM, but still getting #Value. Suggestions? "Toppers" wrote: A1 start time B1 end time C1 =B1-A1 format as hh:mm "Darren" wrote: How can I get the difference in time from 10:35:00 to 11:45:00. I have a program to exports the following to excel: 2/9/2007 12:45:00 PM [start time] 2/9/2007 4:15:00 PM [end time] How can I create a formula to give me the diffence in these times? |
| All times are GMT +1. The time now is 02:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com