compute the difference between two dates accurately

Date360 gives an inacurate answer that over the course of 20 years can be 
over a month.  Is there an accurate way of computing the difference between 
two dates?
0
dilly (6)
4/29/2005 7:46:04 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
404 Views

Similar Articles

[PageSpeed] 12

how about simply subtracting one from another
=B1-A1
where B1 holds the end date and cell A1 the start date

Why days360 unless you want to use 360 days per year convention.


"Dilly" <Dilly@discussions.microsoft.com> wrote in message 
news:8A4A1C01-0D01-461B-9121-7D86FDBCDD76@microsoft.com...
> Date360 gives an inacurate answer that over the course of 20 years can be
> over a month.  Is there an accurate way of computing the difference 
> between
> two dates? 


0
nh1 (51)
4/29/2005 8:05:10 PM
=A1-B1

where A1 holds the later date and B1 the earlier date, format as general
DATE260 is an accounting function

Regards,

Peo Sjoblom

"Dilly" wrote:

> Date360 gives an inacurate answer that over the course of 20 years can be 
> over a month.  Is there an accurate way of computing the difference between 
> two dates?
0
PeoSjoblom (790)
4/29/2005 8:08:14 PM
Dilly

See Chip Pearson's site for info on the DATEDIF Function.

http://www.cpearson.com/excel/datedif.htm


Gord Dibben Excel MVP

On Fri, 29 Apr 2005 12:46:04 -0700, "Dilly" <Dilly@discussions.microsoft.com>
wrote:

>Date360 gives an inacurate answer that over the course of 20 years can be 
>over a month.  Is there an accurate way of computing the difference between 
>two dates?

0
Gord
4/29/2005 8:10:41 PM
Thanks but I tried that.  for example: 2004/05/30 - 1986/06/24 returns 
1917/12/06.  actual answer should be 1917/11/06.  it doesn't recognize zero 
month.

"N Harkawat" wrote:

> how about simply subtracting one from another
> =B1-A1
> where B1 holds the end date and cell A1 the start date
> 
> Why days360 unless you want to use 360 days per year convention.
> 
> 
> "Dilly" <Dilly@discussions.microsoft.com> wrote in message 
> news:8A4A1C01-0D01-461B-9121-7D86FDBCDD76@microsoft.com...
> > Date360 gives an inacurate answer that over the course of 20 years can be
> > over a month.  Is there an accurate way of computing the difference 
> > between
> > two dates? 
> 
> 
> 
0
dilly (6)
4/29/2005 8:24:08 PM
If you just wanted the number of days, try formatting that cell as general.


Dilly wrote:
> 
> Thanks but I tried that.  for example: 2004/05/30 - 1986/06/24 returns
> 1917/12/06.  actual answer should be 1917/11/06.  it doesn't recognize zero
> month.
> 
> "N Harkawat" wrote:
> 
> > how about simply subtracting one from another
> > =B1-A1
> > where B1 holds the end date and cell A1 the start date
> >
> > Why days360 unless you want to use 360 days per year convention.
> >
> >
> > "Dilly" <Dilly@discussions.microsoft.com> wrote in message
> > news:8A4A1C01-0D01-461B-9121-7D86FDBCDD76@microsoft.com...
> > > Date360 gives an inacurate answer that over the course of 20 years can be
> > > over a month.  Is there an accurate way of computing the difference
> > > between
> > > two dates?
> >
> >
> >

-- 

Dave Peterson
0
ec357201 (5290)
4/29/2005 9:59:40 PM
Thanks, but I need it in yy/mm/dd format.  see gord dibben's answer below.

"Dave Peterson" wrote:

> If you just wanted the number of days, try formatting that cell as general.
> 
> 
> Dilly wrote:
> > 
> > Thanks but I tried that.  for example: 2004/05/30 - 1986/06/24 returns
> > 1917/12/06.  actual answer should be 1917/11/06.  it doesn't recognize zero
> > month.
> > 
> > "N Harkawat" wrote:
> > 
> > > how about simply subtracting one from another
> > > =B1-A1
> > > where B1 holds the end date and cell A1 the start date
> > >
> > > Why days360 unless you want to use 360 days per year convention.
> > >
> > >
> > > "Dilly" <Dilly@discussions.microsoft.com> wrote in message
> > > news:8A4A1C01-0D01-461B-9121-7D86FDBCDD76@microsoft.com...
> > > > Date360 gives an inacurate answer that over the course of 20 years can be
> > > > over a month.  Is there an accurate way of computing the difference
> > > > between
> > > > two dates?
> > >
> > >
> > >
> 
> -- 
> 
> Dave Peterson
> 
0
dilly (6)
4/30/2005 1:18:02 PM
Reply:

Similar Artilces:

Date format in Excel
I have a large spred sheet in which all dates are formated in the US way mm. dd.yyyy how can I change this to the UK way dd.mm.yyyy, I have experimented with the normal date formatting but this mixes up days and months. -- All the best Ken You need to truly define what your data is. Do you have text that look like the US format, or is it a true date simply formatted US style? If the latter, you can change the formatting of the cells (custom formatting dd.mm.yyyy). If the former: =DATE(--RIGHT(A2,4),--LEFT(A2,2),--MID(A2,4,2)) This will convert the text into a true serial ...

kindly, before I crash this computer against the wall..
....does someone know why when I write this declaration: dim idx as Index (or even DAO.Index) it changes into: dim idx as index (or DAO.index) ....and obviously it screams errors if I run the code. References seem fine. As a guess, you have defined index somewhere in your database. I am surprised however that Dim idx as DAO.INDEX does not overcome the problem. Does this happen in just one database or in any database you have? If just one, you might try to decompile and then compile your VBA code and see if that helps. John Spencer Access MVP 2002-2005, 2007-2010 Th...

Different page headers
Is it possible to create a different page header on page 1 of a report than the one that would appear on all the other pages? If so how would I go about that? Thanks Tony Actually I want the page header only to appear from page 2 onwards. Thanks Tony "Tony Williams" <tw@invalid.com> wrote in message news:%23zCybIP$HHA.536@TK2MSFTNGP06.phx.gbl... > Is it possible to create a different page header on page 1 of a report > than the one that would appear on all the other pages? If so how would I > go about that? > Thanks > Tony > Figured it out thanks....

Assistance Requested for Date Arithmetic
I've been struggling with this scenario for many hours now and am hopeful someone on this group can provide an assist. Consider the following known data: 2005 Annual Invoice Date 2005 Annual Invoice Amount 2006 Annual Invoice Amount I need to create a monthly amortized view of this data (through 2007), taking into consideration that the invoice month is one month in advance of the booking month. For instance: A customer was billed $12000 on 3/1/05 for an annual contract. This invoice would be amortized $1000 per month for 12 months starting 4/05 and ending 3/06. The renewal in 2006...

Mac Word freezes Computer my computer
I have recntly purchased an apple Macbook intel core Duo notebook running Tiger and MS office for mac version 2004 for students. both have been fully updated off of the web. When I load a word doc file off of my USB stick all's well until I try to then save a file; when quite often, the coloured circle appears forever, when word on the tool bay is touched it indicates that " word is not responding" when I try to force quit this some times works BUT then finder will not work and will not reset, so the only way to regain control of the computer is to hold down the "on/off key&...

How to Use One PST file on Two Computers?
Hello: I have a desktop machine with a pst file in my Outlook 2003. I would like to copy this pst file to my laptop. How do I synchronize the same file on 2 machines so I won't get duplicate e-mails? I use one premium Yahoo e-mail account and several free Yahoo accounts with YahooPOPs.. Thanks in advance, Spudy Hi Spudy, it is not possible by default to share PST files. A list of a list of third-party synctools are also on this site: http://www.slipstick.com/outlook/sync.htm On the Microsoft Office Marketplace site is a list of third-party messaging tools: http://office.microsoft.com...

Two Exchange Server
I have Company A with Location A and Location B. Location B implemented a separate Exchange Server, HOW can I get mail to flow to A & B with same PUBLIC DNS NAME? How can I reroute SMTP to another server but not in the same Exchange site or ORG? I just want mail to go to next set of mailboxes at another location if it does not find it in the first location - WHEN different Exchange site / org altogether. Hope it makes sense. On Thu, 6 Jan 2005 13:43:21 -0600, "Ed" <edg@wti.com> wrote: >I have Company A with Location A and Location B. Location B implement...

Restoring computer
I would like to restore my computer from the day I got it. Does anyone know how to do this? I am fed up its all screwy and I think this will fix all our cont. problems Depends on what OS you are running. You can always reformat your hard drive. But save anything that you want to keep first. ALso on xp you can do a restore. but it only goes back so far. Still save important files because it will erase them. "high5me" <high5me@comcast.net> wrote in message news:%23ep9TO0hFHA.1444@TK2MSFTNGP10.phx.gbl... >I would like to restore my computer from the day I got it. ...

Sum of Two Union Queries
Hello, I have the following SQL that produces two record totals (as it should). But I want to SUM those two values from within the same query. Rather than create a second query that SUMs the two values from the first query: SELECT Count(*) AS SubCount FROM [MyTable1] WHERE (([MyTable1].UNIQUEUSER)="JOHNSMITH") UNION ALL SELECT Count(*) AS SubCount FROM [MyTable2] WHERE (([MyTable2].SWCASSIGNEDUSER)="JOHNSMITH"); The results I get are: SubCount -------------- 380 2 I've tried "wrapping" that entire SQL code inside of a SELECT SUM, but upon execution it ...

Unable to Open a backup file (Money 2004) on a new computer
Though I am computer savvy, MS Money is driving me crazy. Last year I have installed Money 2004 on my laptop and used it once a month to download transactions. Everytime I close money, I get a new file created on my hard disk with the name "My Money Backup ++.mbf", where + indicates a number. I always open the latest .mbf file to access the information. Now I have purchased a new laptop and installed Money 2004 on that as well. I have copied the latest .mbf file from my old laptop onto a diskette and tried to open it from the new laptop in vain. Whatever I do, I get stuc...

Is there a builtnconvenient difference between Express and new Out
In Express folders - inbox for example - double clicking a message opens it (no preview pane being used). Read message and decide to 'delete'. This messwage dissappears to the deleted folder but is REPLACED immediately by the next message. When performing the same routine in Outlook, after deleting, the screen reverts/returns to the inbox making it necessary to double click the next message to open it. VERY INCONVENIENT. Is it possible to configure Outlook to SUBSTITUTE deleted messages with the NEXT message in the box being viewed?? Eric "eric" <eric@discussion...

convert text "20100315" to date format
Hi, I have some data exported from a system, the date is a text displayed as "20100315" which should read yyyymmdd. I need to convert this text to another date format showing mm/dd/yyyy in text for importing into another system. As the text format yyyymmdd does not contain any "/" or "-" so using text to column will be a difficulty. Please advise how could I handle this. Thanks a lot. Best Regards, Terry =TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)),"mm-dd-yyyy") and copy paste special-values. -- Regards Dave Hawle...

XML with two tables
HI The XML below has two tables.They are Category and Products. I need to get the Products when i select a Category for eg:- when i mention "CAR" the i need to get "maruthi","santro" and "ford". The relation between the two tables is "Category_Product". I'm getting all the products. Can any one Help me. <?xml version="1.0" encoding="utf-8" ?> <Catalog xmlns="http://tempuri.org/Products.xsd"> <Category> <Name>Car </Name> <Product> <title>Maruthi</title&...

Copying profiles from computer to computer
Hi there, does anyone know where I will find the folder where Outlook 2002 profiles are kept. My team hotdesk and I want to set up all their profiles on one machine then copy them over to each of the other machines rather than going through setting something like 35 profiles up on each. The machines are all windows 98 SE Any help would be gratefully received. Thanks Paul "Paul" <paulmckinlay@nospam_blueyonder.co.uk> wrote in message news:12cb701c4434c$2702d8d0$a401280a@phx.gbl... > Any help would be gratefully received. > Thanks > Paul The profile informati...

Switching Outlook Contacts to Apple computer
I have been unable to find away to import my Outlook contacts and calender information to a Apple/Mac Powerbook G4 using the OS X platform. Can anyone give me some suggestions.I have Entourage X as email. Thanks, Not sure about how to deal with your calendar information, but you can export your Outlook contacts to a spread sheed, then import them from the spread sheet to you apple application. -- Dab Cut off: yourhead to respond "Brent" <anonymous@discussions.microsoft.com> wrote in message news:017b01c3c58f$b297bdb0$a301280a@phx.gbl... > I have been unable to find a...

How to share Outlook 2003 with 3 computers (peer to peer)
How to I share my Outlook folders, calendar, contacts, tasks, notes? NO Exchange Server! ( I am peer to peer with 3 computers in my office) help! David Create a Personal Folders file on a drive/share that all 3 computer have access to. This PST file can be opened via the File > Open > Outlook data file on each machine. Now for the bad news, Outlook opens the file for exclusive access. Therefore when Machine A has Outlook running, Machines B/C cannot open/work with this shared personal folders file. By the way, there are 3rd party solutions that let multiple machines share t...

two-sided printer setup wizard, Publisher
In 2007 version of Publisher--- I try and run the 2-sided printer setup wizard using the bypass tray (called tray 1) on my printer (for heavy stock) and it will not work. Every time it still draws from tray 2, not tray 1. Is there a way to overide this? I have set the defaults in widows to pull from tray 1 and also in Publisher right before running this wizard to no avail. Most duplex units will not work with card stock. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "bc84" <bc84@discussions.microsoft.com> w...

Select, update query--same query, different result
Hi I have an existing Update query that I just copied and converted to a Select query. When I run it as an Update query, I get 4 records. However, when I run it as a Select query, I get 411 records. It is the same query with the exception of Select vs. Update. Anyone know why this is? Paul Why did you not post the SQL so it could be looked at? -- Build a little, test a little. "Paul" wrote: > Hi > I have an existing Update query that I just copied and converted to a Select > query. When I run it as an Update query, I get 4 records. However, whe...

Should be able to assign a different checkbook to different store
I would like to be able to assign a different Checkbook ID to each store. We deposit to different accounts for bank reconciliation reasons and now have to manually change each transaction that is imported to GP ---------------- 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, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://w...

Reactivating Office on a new computer.
I had a downloaded copy of Office 2007 on a laptop which has been destroyed beyond repair. I have now bought a new laptop. How do I re-download my copy of Office, please? -- Steve Contact the vendor from where you purchased it. "stemac" <stemac@discussions.microsoft.com> wrote in message news:795E14D0-A5F1-4CA4-B2B4-A102986CBADD@microsoft.com... :I had a downloaded copy of Office 2007 on a laptop which has been destroyed : beyond repair. I have now bought a new laptop. How do I re-download my copy : of Office, please? : -- : Steve The vendor was...

transferring e-mails between computers
Can anyone tell me how to copy/transfer my e-mails from one computer to another without risking over-writing all of the e-mails on the new computer. The old e-mails are on Outlook Express 5. Thanks very much. >-----Original Message----- >Can anyone tell me how to copy/transfer my e-mails from >one computer to another without risking over-writing all >of the e-mails on the new computer. The old e-mails are >on Outlook Express 5. > >Thanks very much. >. >If you are keeping your e-mails on the server you don't have to do anything. However, if you are keepi...

Computer becomes unresponsive after installing updates.
Hi, I'm hoping someone could shed some light on some issues I've been having. I've just done a format and installed WinXPPro with SP2 and the computer. The automatic updates picks up a batch to download (approx 65), but whenever update KB956572 or KB959426 is installed, subsequence updates fail and the computer becomes unresponsive. I am unable to open IE or even notepad; any application will not launch and I'm forced to shutdown. After the restart, I can see the history by going to windows update via IE, and the failed updates all have the "Error Code: 0...

disc date for next month
We want to be able to choose a discount date for the next month the discount is allowed. Our terms are 2% 10th net 15th. We can choose the due date for the 15th of the next month, but we can't choose the discount date as the 10th of the next month. All the invoices entered into the system from the 1st thru the 10th calculate the discount for the current month instead of the next month. ---------------- 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 me...

Page Numbering & First page Different 2007
Page numbering and controlling the first page is wrong most the time. Is there an alternative by going thru fields and stopping the first page from having a number on it. Word is too capricious, forgets where it is, etc... Hi, Detailed instructions for adding page numbers in Word 2007 are given in http://office.microsoft.com/en-us/word/HP012265131033.aspx . If you are placing your page numbers in headers or footers, the section-specific settings that you configure according to those instructions override any switches that you might add to the PAGE field. If you keep this ru...

Excel 2003 dates
I have a excel spreadsheet with a column of dates that was ceated in Office XP. Example: 5-May-05 We recently updated to office 2003 and now whenever I copy this column into a different spreadsheet all of the dates change and end in 01 now. check in <tools><options><calculation> and see if 1904 date system is checked. "mdob" wrote: > I have a excel spreadsheet with a column of dates that was ceated in Office XP. > > Example: 5-May-05 > > We recently updated to office 2003 and now whenever I copy this column into > a different spreadshee...