| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Add Seconds To Date
I have two columns, one has Date and the other has seconds (they aren't
necessarily related). Example B2 has the value 7/12/2003 6:43:47 PM and cell C2 has the value 58. I had hoped that I could use the formula =B2+C2 and get the response 7/12/2003 6:44:45 PM. Unfortunately that did not work. To make things more complicated the column C will have values not only 58 but 3622176 and 780814 and so on (but they are always to denote seconds). Anyone have any ideas? I think it should be fairly simple but I'm missing it. Any assistance would be great. Thanks! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add Seconds To Date
=B2+C2/(24*60*60)
Just adding 58 adds 58 DAYS -- Gary''s Student - gsnu200755 "FrankM" wrote: I have two columns, one has Date and the other has seconds (they aren't necessarily related). Example B2 has the value 7/12/2003 6:43:47 PM and cell C2 has the value 58. I had hoped that I could use the formula =B2+C2 and get the response 7/12/2003 6:44:45 PM. Unfortunately that did not work. To make things more complicated the column C will have values not only 58 but 3622176 and 780814 and so on (but they are always to denote seconds). Anyone have any ideas? I think it should be fairly simple but I'm missing it. Any assistance would be great. Thanks! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add Seconds To Date
with the date and time in A1 try this to add 30 seconds:-
=A1+TIME(0,0,30) Mike "FrankM" wrote: I have two columns, one has Date and the other has seconds (they aren't necessarily related). Example B2 has the value 7/12/2003 6:43:47 PM and cell C2 has the value 58. I had hoped that I could use the formula =B2+C2 and get the response 7/12/2003 6:44:45 PM. Unfortunately that did not work. To make things more complicated the column C will have values not only 58 but 3622176 and 780814 and so on (but they are always to denote seconds). Anyone have any ideas? I think it should be fairly simple but I'm missing it. Any assistance would be great. Thanks! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add Seconds To Date
Try
=B2+(C2/24/60/60) format it the same way as B2 -- Regards, Peo Sjoblom "FrankM" wrote in message ... I have two columns, one has Date and the other has seconds (they aren't necessarily related). Example B2 has the value 7/12/2003 6:43:47 PM and cell C2 has the value 58. I had hoped that I could use the formula =B2+C2 and get the response 7/12/2003 6:44:45 PM. Unfortunately that did not work. To make things more complicated the column C will have values not only 58 but 3622176 and 780814 and so on (but they are always to denote seconds). Anyone have any ideas? I think it should be fairly simple but I'm missing it. Any assistance would be great. Thanks! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add Seconds To Date
That was perfect!
"Mike H" wrote: with the date and time in A1 try this to add 30 seconds:- =A1+TIME(0,0,30) Mike "FrankM" wrote: I have two columns, one has Date and the other has seconds (they aren't necessarily related). Example B2 has the value 7/12/2003 6:43:47 PM and cell C2 has the value 58. I had hoped that I could use the formula =B2+C2 and get the response 7/12/2003 6:44:45 PM. Unfortunately that did not work. To make things more complicated the column C will have values not only 58 but 3622176 and 780814 and so on (but they are always to denote seconds). Anyone have any ideas? I think it should be fairly simple but I'm missing it. Any assistance would be great. Thanks! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add Seconds To Date
not quite sure here...
B2 is your date 7/12/2003 6:43:47 PM C2 is your INPUT cell for tick of seconds like 3622176 and 780814 maybe you can format cell B1 into "ss"or more "7s"'s... then try in D2 with formula A2+B2 to see if formula can recognize the format on C2 that u may like for whatever input in few seconds... dribler "FrankM" wrote: I have two columns, one has Date and the other has seconds (they aren't necessarily related). Example B2 has the value 7/12/2003 6:43:47 PM and cell C2 has the value 58. I had hoped that I could use the formula =B2+C2 and get the response 7/12/2003 6:44:45 PM. Unfortunately that did not work. To make things more complicated the column C will have values not only 58 but 3622176 and 780814 and so on (but they are always to denote seconds). Anyone have any ideas? I think it should be fairly simple but I'm missing it. Any assistance would be great. Thanks! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add Seconds To Date
Actually I was wrong. That worked great when the number of seconds is smaller
but when the number of seconds are something like 3622150 then I get an error, #NUM! ... not sure what to do with that. It looked very promising and I liked the fact that I could enter a cell for the seconds and then Fill Down and it was looking good. "FrankM" wrote: That was perfect! "Mike H" wrote: with the date and time in A1 try this to add 30 seconds:- =A1+TIME(0,0,30) Mike "FrankM" wrote: I have two columns, one has Date and the other has seconds (they aren't necessarily related). Example B2 has the value 7/12/2003 6:43:47 PM and cell C2 has the value 58. I had hoped that I could use the formula =B2+C2 and get the response 7/12/2003 6:44:45 PM. Unfortunately that did not work. To make things more complicated the column C will have values not only 58 but 3622176 and 780814 and so on (but they are always to denote seconds). Anyone have any ideas? I think it should be fairly simple but I'm missing it. Any assistance would be great. Thanks! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add Seconds To Date
Unfortunately that didn't seem to work. when B2 is 3/11/2004 1:20:06 PM and
C2 is 17545037 then the response is 9/30/2004 2:57:23 PM. "Gary''s Student" wrote: =B2+C2/(24*60*60) Just adding 58 adds 58 DAYS -- Gary''s Student - gsnu200755 "FrankM" wrote: I have two columns, one has Date and the other has seconds (they aren't necessarily related). Example B2 has the value 7/12/2003 6:43:47 PM and cell C2 has the value 58. I had hoped that I could use the formula =B2+C2 and get the response 7/12/2003 6:44:45 PM. Unfortunately that did not work. To make things more complicated the column C will have values not only 58 but 3622176 and 780814 and so on (but they are always to denote seconds). Anyone have any ideas? I think it should be fairly simple but I'm missing it. Any assistance would be great. Thanks! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add Seconds To Date
You got 2 other answers that work with thousands of seconds, TIME can only
copy with 59 seconds -- Regards, Peo Sjoblom "FrankM" wrote in message ... Actually I was wrong. That worked great when the number of seconds is smaller but when the number of seconds are something like 3622150 then I get an error, #NUM! ... not sure what to do with that. It looked very promising and I liked the fact that I could enter a cell for the seconds and then Fill Down and it was looking good. "FrankM" wrote: That was perfect! "Mike H" wrote: with the date and time in A1 try this to add 30 seconds:- =A1+TIME(0,0,30) Mike "FrankM" wrote: I have two columns, one has Date and the other has seconds (they aren't necessarily related). Example B2 has the value 7/12/2003 6:43:47 PM and cell C2 has the value 58. I had hoped that I could use the formula =B2+C2 and get the response 7/12/2003 6:44:45 PM. Unfortunately that did not work. To make things more complicated the column C will have values not only 58 but 3622176 and 780814 and so on (but they are always to denote seconds). Anyone have any ideas? I think it should be fairly simple but I'm missing it. Any assistance would be great. Thanks! |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add Seconds To Date
Unfortunately that didn't seem to work. when B2 is 3/11/2004 1:20:06 PM and
C2 is 17545037 then the response is 9/30/2004 2:57:23 PM. "Peo Sjoblom" wrote: Try =B2+(C2/24/60/60) format it the same way as B2 -- Regards, Peo Sjoblom "FrankM" wrote in message ... I have two columns, one has Date and the other has seconds (they aren't necessarily related). Example B2 has the value 7/12/2003 6:43:47 PM and cell C2 has the value 58. I had hoped that I could use the formula =B2+C2 and get the response 7/12/2003 6:44:45 PM. Unfortunately that did not work. To make things more complicated the column C will have values not only 58 but 3622176 and 780814 and so on (but they are always to denote seconds). Anyone have any ideas? I think it should be fairly simple but I'm missing it. Any assistance would be great. Thanks! |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add Seconds To Date
I think you are referring to ...
Try =B2+(C2/24/60/60) format it the same way as B2 Unfortunately that didn't seem to work. when B2 is 3/11/2004 1:20:06 PM and C2 is 17545037 then the response is 9/30/2004 2:57:23 PM. "Peo Sjoblom" wrote: You got 2 other answers that work with thousands of seconds, TIME can only copy with 59 seconds -- Regards, Peo Sjoblom "FrankM" wrote in message ... Actually I was wrong. That worked great when the number of seconds is smaller but when the number of seconds are something like 3622150 then I get an error, #NUM! ... not sure what to do with that. It looked very promising and I liked the fact that I could enter a cell for the seconds and then Fill Down and it was looking good. "FrankM" wrote: That was perfect! "Mike H" wrote: with the date and time in A1 try this to add 30 seconds:- =A1+TIME(0,0,30) Mike "FrankM" wrote: I have two columns, one has Date and the other has seconds (they aren't necessarily related). Example B2 has the value 7/12/2003 6:43:47 PM and cell C2 has the value 58. I had hoped that I could use the formula =B2+C2 and get the response 7/12/2003 6:44:45 PM. Unfortunately that did not work. To make things more complicated the column C will have values not only 58 but 3622176 and 780814 and so on (but they are always to denote seconds). Anyone have any ideas? I think it should be fairly simple but I'm missing it. Any assistance would be great. Thanks! |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add Seconds To Date
That is correct, 17545037 is 17 million 545 thousand and 37 seconds, that is
the equivalent of 4873 hours, 37 minutes and 17 seconds 4873 hours is the equivalent of 203 days and 203 days added to March 11th 2004 is 9/30/2004. Don't know what you expected -- Regards, Peo Sjoblom "FrankM" wrote in message ... Unfortunately that didn't seem to work. when B2 is 3/11/2004 1:20:06 PM and C2 is 17545037 then the response is 9/30/2004 2:57:23 PM. "Gary''s Student" wrote: =B2+C2/(24*60*60) Just adding 58 adds 58 DAYS -- Gary''s Student - gsnu200755 "FrankM" wrote: I have two columns, one has Date and the other has seconds (they aren't necessarily related). Example B2 has the value 7/12/2003 6:43:47 PM and cell C2 has the value 58. I had hoped that I could use the formula =B2+C2 and get the response 7/12/2003 6:44:45 PM. Unfortunately that did not work. To make things more complicated the column C will have values not only 58 but 3622176 and 780814 and so on (but they are always to denote seconds). Anyone have any ideas? I think it should be fairly simple but I'm missing it. Any assistance would be great. Thanks! |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add Seconds To Date
I already answered your mistaken conclusion in the post to Gary's Student,
you are wrong, it works the way it should so either you have a misconception of time or you didn't implement them correctly -- Regards, Peo Sjoblom "FrankM" wrote in message ... Unfortunately that didn't seem to work. when B2 is 3/11/2004 1:20:06 PM and C2 is 17545037 then the response is 9/30/2004 2:57:23 PM. "Peo Sjoblom" wrote: Try =B2+(C2/24/60/60) format it the same way as B2 -- Regards, Peo Sjoblom "FrankM" wrote in message ... I have two columns, one has Date and the other has seconds (they aren't necessarily related). Example B2 has the value 7/12/2003 6:43:47 PM and cell C2 has the value 58. I had hoped that I could use the formula =B2+C2 and get the response 7/12/2003 6:44:45 PM. Unfortunately that did not work. To make things more complicated the column C will have values not only 58 but 3622176 and 780814 and so on (but they are always to denote seconds). Anyone have any ideas? I think it should be fairly simple but I'm missing it. Any assistance would be great. Thanks! |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Add Seconds To Date
You are absolutely correct. I am very sorry. When I did the conversion to
check my work I only had it set to days as I didn't think it went into months but you are absolutely correct. Thank you so much for all your assistance. "Peo Sjoblom" wrote: That is correct, 17545037 is 17 million 545 thousand and 37 seconds, that is the equivalent of 4873 hours, 37 minutes and 17 seconds 4873 hours is the equivalent of 203 days and 203 days added to March 11th 2004 is 9/30/2004. Don't know what you expected -- Regards, Peo Sjoblom "FrankM" wrote in message ... Unfortunately that didn't seem to work. when B2 is 3/11/2004 1:20:06 PM and C2 is 17545037 then the response is 9/30/2004 2:57:23 PM. "Gary''s Student" wrote: =B2+C2/(24*60*60) Just adding 58 adds 58 DAYS -- Gary''s Student - gsnu200755 "FrankM" wrote: I have two columns, one has Date and the other has seconds (they aren't necessarily related). Example B2 has the value 7/12/2003 6:43:47 PM and cell C2 has the value 58. I had hoped that I could use the formula =B2+C2 and get the response 7/12/2003 6:44:45 PM. Unfortunately that did not work. To make things more complicated the column C will have values not only 58 but 3622176 and 780814 and so on (but they are always to denote seconds). Anyone have any ideas? I think it should be fairly simple but I'm missing it. Any assistance would be great. Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to convert date/time to seconds | Excel Worksheet Functions | |||
| Converting Julian Seconds with a macro to replace old seconds data | Excel Discussion (Misc queries) | |||
| Need help: convert seconds to date and time | Excel Discussion (Misc queries) | |||
| Convert "Time Interval" in "hours : minutes : seconds" to seconds | New Users to Excel | |||
| How do i convert a number of seconds to a date/time? | Excel Worksheet Functions | |||
Linear Mode
