Time and date problem

Hello
I have spreadsheet cell (B1) that contains a date and time:
05/11/2003 13:45:50
I have another series of cells (A1 to A100)  that contain an increment of 30
seconds:
30.00
I would like to add the seconds to the date and time putting the result in
column C, I have tried to do:
=B1+(A1/100000) but the answer is wrong by 4 seconds and then gets worse the
further down the column I go.

Can somebody show me how to add the seconds to the date please.

Thanks

Tom


0
11/6/2003 10:54:55 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
663 Views

Similar Articles

[PageSpeed] 39

Tom,

Right method, wrong divider! 86,400 secs in a day.. change 
your 100000 to 86400 and you're there.

Nikos Y. (nyannaco at in dot gr)
>-----Original Message-----
>Hello
>I have spreadsheet cell (B1) that contains a date and 
time:
>05/11/2003 13:45:50
>I have another series of cells (A1 to A100)  that contain 
an increment of 30
>seconds:
>30.00
>I would like to add the seconds to the date and time 
putting the result in
>column C, I have tried to do:
>=B1+(A1/100000) but the answer is wrong by 4 seconds and 
then gets worse the
>further down the column I go.
>
>Can somebody show me how to add the seconds to the date 
please.
>
>Thanks
>
>Tom
>
>
>.
>
0
anonymous (74717)
11/6/2003 11:37:17 AM
Hi

There are 24*60*60 seconds in 24 hours, so divide by 86400, not by 100000.

--
HTH. Best wishes Harald
Followup to newsgroup only please

"Tom Weston" <t.m.weston@dl.ac.uk> skrev i melding
news:bod99v$nt5$1@mserv2.dl.ac.uk...
> Hello
> I have spreadsheet cell (B1) that contains a date and time:
> 05/11/2003 13:45:50
> I have another series of cells (A1 to A100)  that contain an increment of
30
> seconds:
> 30.00
> I would like to add the seconds to the date and time putting the result in
> column C, I have tried to do:
> =B1+(A1/100000) but the answer is wrong by 4 seconds and then gets worse
the
> further down the column I go.
>
> Can somebody show me how to add the seconds to the date please.
>
> Thanks
>
> Tom
>
>


0
innocent (844)
11/6/2003 11:37:47 AM
Uh, I thought we went metric!

Harald Staff wrote:
> 
> Hi
> 
> There are 24*60*60 seconds in 24 hours, so divide by 86400, not by 100000.
> 
> --
> HTH. Best wishes Harald
> Followup to newsgroup only please
> 
> "Tom Weston" <t.m.weston@dl.ac.uk> skrev i melding
> news:bod99v$nt5$1@mserv2.dl.ac.uk...
> > Hello
> > I have spreadsheet cell (B1) that contains a date and time:
> > 05/11/2003 13:45:50
> > I have another series of cells (A1 to A100)  that contain an increment of
> 30
> > seconds:
> > 30.00
> > I would like to add the seconds to the date and time putting the result in
> > column C, I have tried to do:
> > =B1+(A1/100000) but the answer is wrong by 4 seconds and then gets worse
> the
> > further down the column I go.
> >
> > Can somebody show me how to add the seconds to the date please.
> >
> > Thanks
> >
> > Tom
> >
> >

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
11/7/2003 1:08:24 AM
Reply:

Similar Artilces:

Offline Restore Problem
I have completed the offline restore process in our lab in preparation of moving our Exchange 5.5 SP4 Server to new hardware. The original server was NT4 SP6a and the new server is Windows 2000 SP4 The process went fine and all services are started and all email data is there. However... the association between the user account and the mailbox has been lost. I have run all the DS/IS Consistency adjusters and anything else that I can think of. What am I missing? Shih Did you also restore the directory? -- Nitin Shukla Microsoft Support (nitinsh@online.microsoft.com) This posti...

formulae for dates
below formulae =TODAY()-(LEFT(C1,2)*365.25)-((MID(C1,7+FIND("",MID(C1,7,99)),2))*365.25/12) gives an output is mm/dd/yy on entering 0 years x months but if I enter 0 years 10 months or 11 months it gives me wrong date e.g if entered 0 years 10 months it gives 31 april 2006 plz advise thanks Hi Gerald You are missing the space between the quotes in your formula Try =TODAY()-(LEFT(C1,2)*365.25)-((MID(C1,7+FIND(" ",MID(C1,7,99)),2))*365.25/12) -- Regards Roger Govier "Gerald" <Gerald@discussions.microsoft.com> wrote in message news:8CF4029E-2BAA...

Time zone
When I noticed the clock on my pc was wrong, I changed the time zone from Pacific to Eastern which is my correct U.S. time zone. After doing this, all of the items inserted as "all day" appointments in my monthly calendar are displayed with an image of a clock on either side of the appointment. The option to display clocks is turned off in the settings. Why is this happening and how can I prevent it from happening. Right now, I've had to put back the Pacific time zone and adjust to the pc not telling the right time. Is there any other solution? Thanks! ddlaz No ...

Today's date as a vertical line on Bar chart
There is probably a way to do this but I do not know how. In XL 2007, on a dynamic stacked bar chart set up as a dynamic gantt chart, is there a way to make today's date appear as a dynamic vertical grid line? Jeff ...

Send/Receive continues 1000s of times
When Outlook does its regular send/receive it ends up sending and receiving thousands of times. The task pane shows an ever increasing number of tasks that it says have been completed successfully. When all it actually is doing is rechecking the mail accounts I have, over and over again. Literally thousands of times. This interferes with anything that I want to do in Outlook while this is going on. It seems to have started after I downloaded either the latest version of itunes or the latest version of Adobe (or some other application like that). Help! And thanks. Per Roady - MVP, th...

One time code
In either an SDI or MDI application, if I want to include some functionality ( such as serial port configuration and control thread) that can only be run once per application instance, where should that code go? I looked at the App class but it doesn't handle user messages ( at least by default ). Should the main code and worker thread go in the MainFrm class in the OnCreate function? Help? Ron H. ----------------- www.Newsgroup-Binaries.com - *Completion*Retention*Speed* Access your favorite newsgroups from home or on the road ----------------- If it has to do with the "wi...

Passport Sign-in Problem in 2003 Deluxe
I have trouble signing into MS Money 2003 Deluxe using my .NET Passport password. I receive the message, "The sign-in attempt for <my account name>@hotmail.com was unsuccessful. Please try your sign-in name and password again." I know that I am using the correct password because I am able to sign in to MSN Messenger using the same sign-in name (my hotmail account name) and password. Also, I checked to see if the Passport server might be down, but the MS website does not list it as being down when I attempt to sign in. In addition, even if the Passport server we...

Month Function on UK dates formats (dd/mm/yyy)
Hi, (Excel 2007) I'm in the US, created 2 tabs, 1 with US dates (formatted as DATE) and the other tab with UK dates (also formatted as DATE). File sent to UK to enter dates (dd/mm/yyyy) =MONTH works find pulling out months from US dates, but fails on some UK dates. FAILS on =month(13/1/2010) #VALUE! but ok with =month(12/1/2010). I suspect it is failing because it's "out of date range". My goal is to pull out the month from all UK dates. THANK for your help! Gene It may be for than just a formatting issue. For example in A1 enter: 1/13/2010 (US format...

cpu time
Hi, Can you tell me about good tool for vs-6 that can tell me what functions in my application take the most cpu time? Thanks! In article <u$YZa3o9EHA.2680@TK2MSFTNGP09.phx.gbl>, dbg@012.net.il says... > Hi, > Can you tell me about good tool for vs-6 that can tell me what functions in > my application take the most cpu time? VS 6 includes a perfectly good profiler, which does exactly this. If you're working inside the environment, go to the link tab in the project settings, and click the "enable profiling" check box, then do a 'rebuild all' to gene...

Date, Weekdays
Hi! Could you please help me with date formula. I need to enter 2 days before but it must be a weekday. e.g 12 Sep 2005 must show 9 Sep 2005 and also 13 Sep 2005 must show 9 Sep 2005. What I have is NOW()-2 , but it shows 10 Sep 2005 which is wrong. -- Heckstein ------------------------------------------------------------------------ Heckstein's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27153 View this thread: http://www.excelforum.com/showthread.php?threadid=466653 "Heckstein" wrote: > > Hi! > > Could you please help me wi...

Date + 10 days?
I have a field that shows the date I sent a notice to someone. I want to create a field that automatically shows the date the notice is due back, which is 10 days from the sent date. I would also like the field to show up in red when the due date is past. =?Utf-8?B?YXJjaDI1Mg==?= <arch252@discussions.microsoft.com> wrote in news:9F8D2485-C1BB-41DB-8B94-E5DA949F87C2@microsoft.com: > I have a field that shows the date I sent a notice to someone. > I want to create a field that automatically shows the date > the notice is due back, which is 10 days from the sent date. >...

Book problems and solution for windows 7 09-23-10
Book problems and solution for windows 7 SBN: 978-1-4461-4031-4 / Copyright: ilias el issaoui / Language: English /page : 460 / file : pdf This book takes you step by step to a new world. The world of Windows 7, even if the new system were not know how to use it. Explain everything to you in pictures. You do not need to for a long time to learn In order not to remain afraid of not being able to follow up on your friends, neighbors or family members Problems and solutions for Windows 7 Is a book for all. To small and great. Anyone who wants to learn Do not miss the opportuni...

Time Out a MsgBox
I have a file that runs a macro when it is open. This file may be opened manually or automatically by a macro from another file. If the file is opened manually I would like a MsgBox to prompt the user to end the macro before it gets into the body of the code. If the file is opened automatically I would like the MsgBox to automatically respond after a time delay to allow the macro to continue running the bulk of the code. Is there anyway to force a response to a MsgBox after a preset amount of time with no response from the user? Hi, How about this instead which closes...

Hiding columns problem
Hello All, When I try to hide a number of columns in part of my spreadsheet, I receive to following message: 'Cannot Shift objects off sheet' According to the Microsoft website, this is because there are cells with comments in the area that is being hidden. I cannot see any comments in the cells, even with show all comments enabled. Can anyone help on this. Graham > When I try to hide a number of columns in part of my spreadsheet, I receive > to following message: > > 'Cannot Shift objects off sheet' It could also be formats. Select a range of columns star...

Calculating time and pay in excel
Hi, i'm trying to make a spreadsheet that can calculate pay per minute depending on an average length of time worked. Its driving me crazy and any help would be greatly appreciated! Coloumn A is length of time, at the bottom is a total time, next to that is an average length of time worked and this is then multiplied by the amount appropriate for that average. What would be the best way to work all of this out accurately? Many thanks for any help you can give Mark -- wobbleman ------------------------------------------------------------------------ wobbleman's Profile: http://www.e...

Am I behind the times?
I have just installed Exchange sp2 on my SBS 2008 boxes and all went well. One weird thing at the end of one of the installs it said Web Apps (I think) couldn't restore default configuration, but everything seems to be working just fine. So then I went to look for updates and I see a few that I must have missed. I use WSUS on the network. One is Exchange SP2 update rollup 3. Go for it or no? Second, Windows Server 2008 SP2. Yikes, when did that come out for SBS? I wonder why it never popped up before since it looks like it's been out for quite some time. Nonetheless, s...

How do I use same date parameters for different queries and report
I'm having the same problem as Gavelle described below. That is: I've followed the advice and set up a form to have my queries and reports pick the dates from the form. But the queries/reports are still asking me to enter the beginning and ending date parameters for each query/report. Also, when i set up the form it runs the query correctly. But when i try to run the query (or report based off the query) I only get a general parameter box stating Enter Parameter Value "forms!FORMNAME!StartDate and another for the end date. Everything in my query criteria is typed correc...

run time error
When I attempt to import a text file (.txt) in to Access, an error occurs. Run-time error �3163�: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data When I click the Debug button this pops up: .Fields("TranType").Value = rs("MoveType").Value A different error occurs when save it to my desktop, and this is what happens.Run-time error �2185�: You can�t reference a property or method for a control unless the control has the focus. When I click the Debug button this pops up: MsgBox "Import File Not Found:&quo...

Printing Journal Problems
Well theres two sides this this tale. Many of the retailers we supply to have very simple pos only setups, using store manager for only TII, TIO, POs etc. With this, they only normally have 1 printer, a receipt printer. What we have done to solve this problem is develop an XML template for the PO prints etc, set up with a Epson T88 as a windows printer. This all works absolutely fine with most of our customers except one. For whatever reason, the Journal now refuses on their printers. Its inceredibly bizarre, as it is set up identically as our other retailers, but none of this retailers sto...

Start Time & End Time
I have a table of data for my Scheduled cutting list that is exported from another program into an Excel Spreadsheet and from this table I would like to be able to find the start and end time of the production runs automatically in a separate table. The table is set up like this Planned order Time Product Description Volume 12345 08:00:00 Product 1 3500 23435 11:32:00 Product 2 5680 14567 13:24:00 Product 3 10000 and so on. I can find the start time by us...

Date Lookup
Hi, assuming the following data: A B 1 31-Dec-2009 50 2 4-Jan-2010 30 3 5-Jan-2010 20 In C, I have the date for the first day of every month e.g. 1-Jan-2010. In the adjacent cell in D, i need to lookup the value in B for the first number that occur for that month (in this case 4-Jan and so, the number should be 30). I tried various formula but as there are no 1-Jan in column A, it returns the value of 50 (31-Dec). Anyone here has a solution for this? Thanks Hi, If there was a 1 jan in your data what would you want to return th...

displaying the date of every 7th day in a colomn
I would like tio display the date for every Monday in a colomn starting from a past Monday date. Can anyone be of assistance? George, with your start data in A1, put this in A2 and drag down as far as needed =A1+7 -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "George the machinist" <George the machinist@discussions.microsoft.com> wrote in message news:73B42142-116A-474D-8627-DCA4EF98EB7C@microsoft.com... > I wo...

Transferring freight more than one time on a sales order
We frequently have partial shipments of sales orders and we leave the sales order open until everything has shipped instead of creating backorders. The system will allow you to have several tracking numbers for each order but you can only transfer the freight once. I would like to make the suggestion to be able to transfer freight more than one time. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, ...

Incorrect Time Stamps
Exchange seems to be stamping the wrong time on emails and events sporadically. All the times on servers are correct. Any thoughts? -- Craig Tuttle - Level I Exchange Tech UCNSB.org The time stamp can also be handled by the client. Which exact time field is being stamped wrong? Have you verified that the client has the correct time (i.e. Daylight savings time set correctly)? -- Ben Winzenz Exchange MVP MessageOne "Craig Tuttle" <CraigTuttle@discussions.microsoft.com> wrote in message news:02D51926-77DC-43EB-A5B5-7A00F4530323@microsoft.com... > Exchange seems to b...

subtracting 28 days from a date
I know this is simple, but I am at a loss...A1 is a given date...A2 needs to be 28 days prior. Formula please? -- Bill RPC On Wed, 6 Jan 2010 14:40:03 -0800, Billrpc <Billrpc@discussions.microsoft.com> wrote: >I know this is simple, but I am at a loss...A1 is a given date...A2 needs to >be 28 days prior. Formula please? Try this formula in cell A2: =A1-28 Hope this helps / Lars-�ke Got it, I knew it was simple, but I still don't know what was wrong. -- Bill RPC "Billrpc" wrote: > I know this is simple, but I am at a loss...A1 is...