|
date and time difference
A | B | C | D |
E 1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM | 2| 3| 4| 5| Log for short and long term visits. Colums A&B being start date and time in that format Colums C&D being end date and time. Need E1 to = total duration in Format YY/MM/DD HH:MM And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM Please help, been at this problem for weeks now. |
date and time difference
E got cut off should be at the end.
"Stumped" wrote: A | B | C | D | E 1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM | 2| 3| 4| 5| Log for short and long term visits. Colums A&B being start date and time in that format Colums C&D being end date and time. Need E1 to = total duration in Format YY/MM/DD HH:MM And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM Please help, been at this problem for weeks now. |
date and time difference
e1: =c1+d1-a1-b1
Format as desired e5: = sum(e1:e4) Format as desired Regards, Fred "Stumped" wrote in message ... E got cut off should be at the end. "Stumped" wrote: A | B | C | D | E 1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM | 2| 3| 4| 5| Log for short and long term visits. Colums A&B being start date and time in that format Colums C&D being end date and time. Need E1 to = total duration in Format YY/MM/DD HH:MM And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM Please help, been at this problem for weeks now. |
date and time difference
tried what you suggested and got #value error. have tried several versions of
what u sent with no success. I really am stumped "Fred Smith" wrote: e1: =c1+d1-a1-b1 Format as desired e5: = sum(e1:e4) Format as desired Regards, Fred "Stumped" wrote in message ... E got cut off should be at the end. "Stumped" wrote: A | B | C | D | E 1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM | 2| 3| 4| 5| Log for short and long term visits. Colums A&B being start date and time in that format Colums C&D being end date and time. Need E1 to = total duration in Format YY/MM/DD HH:MM And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM Please help, been at this problem for weeks now. . |
date and time difference
Then you must have text (rather than a number) in one or more of your cells.
Do this: -- Enter 1 in an empty cell -- Copy that cell -- Highlight cells a1 through e4 -- Right click, choose Paste Special... Multiply. Do the results come through now? Regards, Fred "Stumped" wrote in message ... tried what you suggested and got #value error. have tried several versions of what u sent with no success. I really am stumped "Fred Smith" wrote: e1: =c1+d1-a1-b1 Format as desired e5: = sum(e1:e4) Format as desired Regards, Fred "Stumped" wrote in message ... E got cut off should be at the end. "Stumped" wrote: A | B | C | D | E 1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM | 2| 3| 4| 5| Log for short and long term visits. Colums A&B being start date and time in that format Colums C&D being end date and time. Need E1 to = total duration in Format YY/MM/DD HH:MM And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM Please help, been at this problem for weeks now. . |
date and time difference
On Tue, 12 Jan 2010 19:10:01 -0800, Stumped
wrote: A | B | C | D | E 1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM | 2| 3| 4| 5| Log for short and long term visits. Colums A&B being start date and time in that format Colums C&D being end date and time. Need E1 to = total duration in Format YY/MM/DD HH:MM And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM Please help, been at this problem for weeks now. Look at the formulas in these time cells... http://office.microsoft.com/en-us/te...CT101172771033 |
date and time difference
sorry bout the late response, did not git notice of new posts
Cellshocked, I did nort see any formulas is that spreed sheet that would give me the results that I am lookinf for. Fred, I double checked my formating, no text. A1 and C1 = date format, B1 and D1 = time format, E1 = custom format yy:mm:dd hh:mm Thank u both for the time but I still have not found a solution. Do you even think it is possible to get the results I am looking for? "CellShocked" wrote: On Tue, 12 Jan 2010 19:10:01 -0800, Stumped wrote: A | B | C | D | E 1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM | 2| 3| 4| 5| Log for short and long term visits. Colums A&B being start date and time in that format Colums C&D being end date and time. Need E1 to = total duration in Format YY/MM/DD HH:MM And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM Please help, been at this problem for weeks now. Look at the formulas in these time cells... http://office.microsoft.com/en-us/te...CT101172771033 . |
date and time difference
Absolutely you can get the results you are looking for. This should be an
easy two minute solution. I still believe the problem is that your cells are text. Unfortunately, checking the formatting doesn't always do the job. Do the following: -- try to change the formatting. Does the display change? If not, then you have text. -- check for text by using =istext(a1) in another cell. What's the result? -- do this for all of your cells If you find a cell with text in it, then change it to a number. Then the formula should work. Regards, Fred "Stumped" wrote in message ... sorry bout the late response, did not git notice of new posts Cellshocked, I did nort see any formulas is that spreed sheet that would give me the results that I am lookinf for. Fred, I double checked my formating, no text. A1 and C1 = date format, B1 and D1 = time format, E1 = custom format yy:mm:dd hh:mm Thank u both for the time but I still have not found a solution. Do you even think it is possible to get the results I am looking for? "CellShocked" wrote: On Tue, 12 Jan 2010 19:10:01 -0800, Stumped wrote: A | B | C | D | E 1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM | 2| 3| 4| 5| Log for short and long term visits. Colums A&B being start date and time in that format Colums C&D being end date and time. Need E1 to = total duration in Format YY/MM/DD HH:MM And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM Please help, been at this problem for weeks now. Look at the formulas in these time cells... http://office.microsoft.com/en-us/te...CT101172771033 . |
date and time difference
See my interspersed response below... On Mon, 18 Jan 2010 17:34:43 -0700, "Fred Smith" wrote: Absolutely you can get the results you are looking for. This should be an easy two minute solution. Absolutely it is obvious that you did not even examine the problem(s) involved. I still believe the problem is that your cells are text. Unfortunately, checking the formatting doesn't always do the job. Do the following: -- try to change the formatting. Does the display change? If not, then you have text. Did you bother to notice that his start time is divided into two cells, as is his stop time? Provide a formula that concatenates (not with the excel function) those two together into a proper date/time string. That has to happen first. The two cells may have to be concatenated in another cell, and then acted on by a formula to be seen as a proper timestamp, and get correct math out of it. -- check for text by using =istext(a1) in another cell. What's the result? -- do this for all of your cells You are barking up the wrong tree. The way it looks, his original cells NEED to be converted to a single date and time cell value FIRST He has more than one format just in the single line of data he gave. If you find a cell with text in it, then change it to a number. Then the formula should work. Your three second glance at this post/thread is an insult. |
date and time difference
Try this in E1
=(DATEVALUE(C1)+TIMEVALUE(D1))-(DATEVALUE(A1)+TIMEVALUE(B1)) That works for text fields. This one works if they are all indeed date and time formatted. =(C1+D1)-(A1+B1) You also need to change your cell formatting to "[hh]" instead of simply "hh" because you are adding more than 24 hour periods. As in: yy:mm:dd [hh]:mm On Mon, 18 Jan 2010 13:28:02 -0800, Stumped wrote: sorry bout the late response, did not git notice of new posts Cellshocked, I did nort see any formulas is that spreed sheet that would give me the results that I am lookinf for. Fred, I double checked my formating, no text. A1 and C1 = date format, B1 and D1 = time format, E1 = custom format yy:mm:dd hh:mm Thank u both for the time but I still have not found a solution. Do you even think it is possible to get the results I am looking for? "CellShocked" wrote: On Tue, 12 Jan 2010 19:10:01 -0800, Stumped wrote: A | B | C | D | E 1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM | 2| 3| 4| 5| Log for short and long term visits. Colums A&B being start date and time in that format Colums C&D being end date and time. Need E1 to = total duration in Format YY/MM/DD HH:MM And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM Please help, been at this problem for weeks now. Look at the formulas in these time cells... http://office.microsoft.com/en-us/te...CT101172771033 . |
date and time difference
My posted solution was:
e1: =c1+d1-a1-b1 Isn't that the same as yours? Fred "CellShocked" <cellshocked@thecellvalueattheendofthespreadsheet. org wrote in message ... See my interspersed response below... On Mon, 18 Jan 2010 17:34:43 -0700, "Fred Smith" wrote: Absolutely you can get the results you are looking for. This should be an easy two minute solution. Absolutely it is obvious that you did not even examine the problem(s) involved. I still believe the problem is that your cells are text. Unfortunately, checking the formatting doesn't always do the job. Do the following: -- try to change the formatting. Does the display change? If not, then you have text. Did you bother to notice that his start time is divided into two cells, as is his stop time? Provide a formula that concatenates (not with the excel function) those two together into a proper date/time string. That has to happen first. The two cells may have to be concatenated in another cell, and then acted on by a formula to be seen as a proper timestamp, and get correct math out of it. -- check for text by using =istext(a1) in another cell. What's the result? -- do this for all of your cells You are barking up the wrong tree. The way it looks, his original cells NEED to be converted to a single date and time cell value FIRST He has more than one format just in the single line of data he gave. If you find a cell with text in it, then change it to a number. Then the formula should work. Your three second glance at this post/thread is an insult. |
date and time difference
On Mon, 18 Jan 2010 20:11:45 -0700, "Fred Smith"
wrote: My posted solution was: e1: =c1+d1-a1-b1 Isn't that the same as yours? That yields an incorrect answer. They have to be encapsulated. Like I said, you did not even try the 4 cell problem, apparently. You were on about his fields being formatted text, even after he stated that they were not. I solved it even for text formatting, but I do not know if I included that text in my reply. No biggie. Fred "CellShocked" <cellshocked@thecellvalueattheendofthespreadsheet. org wrote in message ... See my interspersed response below... On Mon, 18 Jan 2010 17:34:43 -0700, "Fred Smith" wrote: Absolutely you can get the results you are looking for. This should be an easy two minute solution. Absolutely it is obvious that you did not even examine the problem(s) involved. I still believe the problem is that your cells are text. Unfortunately, checking the formatting doesn't always do the job. Do the following: -- try to change the formatting. Does the display change? If not, then you have text. Did you bother to notice that his start time is divided into two cells, as is his stop time? Provide a formula that concatenates (not with the excel function) those two together into a proper date/time string. That has to happen first. The two cells may have to be concatenated in another cell, and then acted on by a formula to be seen as a proper timestamp, and get correct math out of it. -- check for text by using =istext(a1) in another cell. What's the result? -- do this for all of your cells You are barking up the wrong tree. The way it looks, his original cells NEED to be converted to a single date and time cell value FIRST He has more than one format just in the single line of data he gave. If you find a cell with text in it, then change it to a number. Then the formula should work. Your three second glance at this post/thread is an insult. |
date and time difference
Your answer was: =(C1+D1)-(A1+B1)
My answer was: =c1+d1-a1-b1 These are mathematically identical. They do not "have to be encapsulated". My formula yields exactly the same result as yours. Regards, Fred "CellShocked" <cellshocked@thecellvalueattheendofthespreadsheet. org wrote in message ... On Mon, 18 Jan 2010 20:11:45 -0700, "Fred Smith" wrote: My posted solution was: e1: =c1+d1-a1-b1 Isn't that the same as yours? That yields an incorrect answer. They have to be encapsulated. Like I said, you did not even try the 4 cell problem, apparently. You were on about his fields being formatted text, even after he stated that they were not. I solved it even for text formatting, but I do not know if I included that text in my reply. No biggie. Fred "CellShocked" <cellshocked@thecellvalueattheendofthespreadsheet. org wrote in message ... See my interspersed response below... On Mon, 18 Jan 2010 17:34:43 -0700, "Fred Smith" wrote: Absolutely you can get the results you are looking for. This should be an easy two minute solution. Absolutely it is obvious that you did not even examine the problem(s) involved. I still believe the problem is that your cells are text. Unfortunately, checking the formatting doesn't always do the job. Do the following: -- try to change the formatting. Does the display change? If not, then you have text. Did you bother to notice that his start time is divided into two cells, as is his stop time? Provide a formula that concatenates (not with the excel function) those two together into a proper date/time string. That has to happen first. The two cells may have to be concatenated in another cell, and then acted on by a formula to be seen as a proper timestamp, and get correct math out of it. -- check for text by using =istext(a1) in another cell. What's the result? -- do this for all of your cells You are barking up the wrong tree. The way it looks, his original cells NEED to be converted to a single date and time cell value FIRST He has more than one format just in the single line of data he gave. If you find a cell with text in it, then change it to a number. Then the formula should work. Your three second glance at this post/thread is an insult. |
date and time difference
OK guys settle down. :)
=(C1+D1)-(A1+B1) works with 2 exceptions, No matter the dates entered (less than 1 month difference) it will show that one month has passed when in fact it may only be a day or two. Also CellShocked, when I place the brackets around hh it allways shows 12 in the hours position in E1. when I leave them off it appears to count correctly. Shold I leave them on or off?? I also discovered an error on my part. Even though the date cells were formated to just date, when I looked in the formula bar I could see that excell had entered a random time as well. I could enter the same date into the same cell and each time it would assign a apparently random time to it as well. not sure what that was about but got that fixed. So u both may have been right. But the above exceptions still exist. P.S. I am not recieving notices about updates to this post even though the box is checked. oh well another post for that. "Stumped" wrote: A | B | C | D | E 1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM | 2| 3| 4| 5| Log for short and long term visits. Colums A&B being start date and time in that format Colums C&D being end date and time. Need E1 to = total duration in Format YY/MM/DD HH:MM And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM Please help, been at this problem for weeks now. |
date and time difference
Update:
after testing various times and dates the hours seem to be counting correctly (adding 1 day for every 24) even in the E5 cell without the " [ ] "so I am leaving them out of the formating. Everthing seems to be ok except the additional 1 month when there has not been 1 month pass. In the E5 cell the "false" 1 month is not added to the sum but is still shown. let me know if I confuseing you. Thank u both for the time and effort. "Stumped" wrote: A | B | C | D | E 1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM | 2| 3| 4| 5| Log for short and long term visits. Colums A&B being start date and time in that format Colums C&D being end date and time. Need E1 to = total duration in Format YY/MM/DD HH:MM And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM Please help, been at this problem for weeks now. |
date and time difference
When you display yy/mm/dd (with or without the time), months start at 1.
There is no month zero. This is just the way months are displayed. You cannot use a date format if you want to have a month zero. If you want to get around this, give us sample results, and what you would like displayed. In particular, how many days do you expect your "months" to have. Regards, Fred "Stumped" wrote in message ... Update: after testing various times and dates the hours seem to be counting correctly (adding 1 day for every 24) even in the E5 cell without the " [ ] "so I am leaving them out of the formating. Everthing seems to be ok except the additional 1 month when there has not been 1 month pass. In the E5 cell the "false" 1 month is not added to the sum but is still shown. let me know if I confuseing you. Thank u both for the time and effort. "Stumped" wrote: A | B | C | D | E 1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM | 2| 3| 4| 5| Log for short and long term visits. Colums A&B being start date and time in that format Colums C&D being end date and time. Need E1 to = total duration in Format YY/MM/DD HH:MM And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM Please help, been at this problem for weeks now. |
date and time difference
Maybe if I explain what I am trying to do it will help you.
My job includes a lot of travel and I track the time at different locations in lump sums from arival on site to departure from site. The entervals usaually range 1 week or less but on ocasionaly over a month and even just over 1 year. the input data is excactly as in the origional post and formated as shown (date and time) I am looking for the E colum to compute total difference. yy/mm/dd hh:mm. been doing it for years on paper, that gets old!! everything apears to work as I would like except that 1 month thing. Months need to reset after 12 and add 1 to the years. Thank u for all the help "Fred Smith" wrote: When you display yy/mm/dd (with or without the time), months start at 1. There is no month zero. This is just the way months are displayed. You cannot use a date format if you want to have a month zero. If you want to get around this, give us sample results, and what you would like displayed. In particular, how many days do you expect your "months" to have. Regards, Fred "Stumped" wrote in message ... Update: after testing various times and dates the hours seem to be counting correctly (adding 1 day for every 24) even in the E5 cell without the " [ ] "so I am leaving them out of the formating. Everthing seems to be ok except the additional 1 month when there has not been 1 month pass. In the E5 cell the "false" 1 month is not added to the sum but is still shown. let me know if I confuseing you. Thank u both for the time and effort. "Stumped" wrote: A | B | C | D | E 1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM | 2| 3| 4| 5| Log for short and long term visits. Colums A&B being start date and time in that format Colums C&D being end date and time. Need E1 to = total duration in Format YY/MM/DD HH:MM And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM Please help, been at this problem for weeks now. . |
date and time difference
Give us examples of what you do on paper, then we can tell you how to do it
in Excel. A good example for you to define would be Jan 31 to Mar 1. How many months and days is this? Regards, Fred "Stumped" wrote in message ... Maybe if I explain what I am trying to do it will help you. My job includes a lot of travel and I track the time at different locations in lump sums from arival on site to departure from site. The entervals usaually range 1 week or less but on ocasionaly over a month and even just over 1 year. the input data is excactly as in the origional post and formated as shown (date and time) I am looking for the E colum to compute total difference. yy/mm/dd hh:mm. been doing it for years on paper, that gets old!! everything apears to work as I would like except that 1 month thing. Months need to reset after 12 and add 1 to the years. Thank u for all the help "Fred Smith" wrote: When you display yy/mm/dd (with or without the time), months start at 1. There is no month zero. This is just the way months are displayed. You cannot use a date format if you want to have a month zero. If you want to get around this, give us sample results, and what you would like displayed. In particular, how many days do you expect your "months" to have. Regards, Fred "Stumped" wrote in message ... Update: after testing various times and dates the hours seem to be counting correctly (adding 1 day for every 24) even in the E5 cell without the " [ ] "so I am leaving them out of the formating. Everthing seems to be ok except the additional 1 month when there has not been 1 month pass. In the E5 cell the "false" 1 month is not added to the sum but is still shown. let me know if I confuseing you. Thank u both for the time and effort. "Stumped" wrote: A | B | C | D | E 1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM | 2| 3| 4| 5| Log for short and long term visits. Colums A&B being start date and time in that format Colums C&D being end date and time. Need E1 to = total duration in Format YY/MM/DD HH:MM And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM Please help, been at this problem for weeks now. . |
date and time difference
Ok here goes:
02/07/08 10:30 AM -- 03/18/09 3:00 PM = 01/01/08 04:30 05/22/09 12:00 PM -- 05/26/09 7:00 PM = 00/00/04 07:00 06/08/09 01:00 PM -- 07/16/09 3:00 PM = 00/01/08 02:00 07/18/09 10:30 AM -- 09/28/09 3:00 PM = 00/02/12 04:30 ---------------------- total = 01/04/31 18:00 everything in E colum is yy/mm/dd hh:mm custom format. Days roll over to months after 28-31 days depending on month. months roll over to years after 12 and total E5 = sum of all differences "Fred Smith" wrote: Give us examples of what you do on paper, then we can tell you how to do it in Excel. A good example for you to define would be Jan 31 to Mar 1. How many months and days is this? Regards, Fred "Stumped" wrote in message ... Maybe if I explain what I am trying to do it will help you. My job includes a lot of travel and I track the time at different locations in lump sums from arival on site to departure from site. The entervals usaually range 1 week or less but on ocasionaly over a month and even just over 1 year. the input data is excactly as in the origional post and formated as shown (date and time) I am looking for the E colum to compute total difference. yy/mm/dd hh:mm. been doing it for years on paper, that gets old!! everything apears to work as I would like except that 1 month thing. Months need to reset after 12 and add 1 to the years. Thank u for all the help "Fred Smith" wrote: When you display yy/mm/dd (with or without the time), months start at 1. There is no month zero. This is just the way months are displayed. You cannot use a date format if you want to have a month zero. If you want to get around this, give us sample results, and what you would like displayed. In particular, how many days do you expect your "months" to have. Regards, Fred "Stumped" wrote in message ... Update: after testing various times and dates the hours seem to be counting correctly (adding 1 day for every 24) even in the E5 cell without the " [ ] "so I am leaving them out of the formating. Everthing seems to be ok except the additional 1 month when there has not been 1 month pass. In the E5 cell the "false" 1 month is not added to the sum but is still shown. let me know if I confuseing you. Thank u both for the time and effort. "Stumped" wrote: A | B | C | D | E 1| 12/09/04 | 3:45 PM | 05/15/06 | 1:00 AM | 2| 3| 4| 5| Log for short and long term visits. Colums A&B being start date and time in that format Colums C&D being end date and time. Need E1 to = total duration in Format YY/MM/DD HH:MM And E5 to = total of E1:E4 in the Format YY/MM/DD HH:MM Please help, been at this problem for weeks now. . . |
| All times are GMT +1. The time now is 05:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com