Difference between Dates doesnt work

I am trying to find out the difference between two dates. I want it i
number of years, they are all actually between 1 to 5 years only.

Do I need to keep the dates in a particular format? I have
26-May-2004 format.

However, i have changed that too and used
26/05/2004

and used *=DATEDIF(B2;C2;"y")* function but it still doesnt work.

I am using WinNT 97.

Please help.

Thank yo

--
Message posted from http://www.ExcelForum.com

0
7/23/2004 7:27:00 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
398 Views

Similar Articles

[PageSpeed] 5

Hi!

You might try =YEAR(A2)-YEAR(A1)  formatted as a number with 0 d.p.

A1 and A2 hold dates.

Al

--
Message posted from http://www.ExcelForum.com

0
7/23/2004 7:52:25 PM
No doesnt work. I didnt understand what you meant by 0 d.p. though

--
Message posted from http://www.ExcelForum.com

0
7/23/2004 8:00:16 PM
Hi!!

Sorry!

0 d.p. is an abbreviation of zero decimal places. In other words, 
whole number or integer.

Al

--
Message posted from http://www.ExcelForum.com

0
7/23/2004 8:06:11 PM
The delimiter used within Excel functions should be a 
comma, not a semicolon as is used in your example. 

Try something like "=DATEDIF(B2,C2,"y")"

By the way, the date format should not matter as long as 
the data is recognized as a valid date. The date values 
are actually just numeric counts of days starting with 
1/1/1900 (this is 1/2/1904 in Mac versions). Time within 
each day is the decimal equivalent of 24 hours.

HTH,

TK
>-----Original Message-----
>I am trying to find out the difference between two dates. 
I want it in
>number of years, they are all actually between 1 to 5 
years only.
>
>Do I need to keep the dates in a particular format? I have
>26-May-2004 format.
>
>However, i have changed that too and used
>26/05/2004
>
>and used *=DATEDIF(B2;C2;"y")* function but it still 
doesnt work.
>
>I am using WinNT 97.
>
>Please help.
>
>Thank you
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
anonymous (74721)
7/23/2004 8:30:08 PM
On Fri, 23 Jul 2004 14:27:00 -0500, sachinattri
<<sachinattri.19v32y@excelforum-nospam.com>> wrote:

>I am trying to find out the difference between two dates. I want it in
>number of years, they are all actually between 1 to 5 years only.
>
>Do I need to keep the dates in a particular format? I have
>26-May-2004 format.

No, but the date has to be recognized by Excel as a date.  A common mistake is
that the date is really text and not a number.  You can test for this by a
formula of the type =ISTEXT(A1).  Substitute for A1 the cell reference of your
date.  If the formula returns TRUE, your date is TEXT and not a true excel
date.
>
>and used *=DATEDIF(B2;C2;"y")* function but it still doesnt work.

What, exactly, happens when it "doesn't work"?

--ron
0
ronrosenfeld (3122)
7/23/2004 8:52:48 PM
You're using semicolons for your list separator.

If you're not using an English language version of excel, maybe it's a language
difference.

If this is true, try this against a test workbook:

hit alt-f11 (to get to the VBE)
hit ctrl-g  (to see the immediate window)

type this and hit enter:

Range("a1").Formula = "=datedif(b2,c2,""Y"")"

alt-f11 to get back to excel and look at your formula.



"sachinattri <" wrote:
> 
> I am trying to find out the difference between two dates. I want it in
> number of years, they are all actually between 1 to 5 years only.
> 
> Do I need to keep the dates in a particular format? I have
> 26-May-2004 format.
> 
> However, i have changed that too and used
> 26/05/2004
> 
> and used *=DATEDIF(B2;C2;"y")* function but it still doesnt work.
> 
> I am using WinNT 97.
> 
> Please help.
> 
> Thank you
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
7/24/2004 12:41:44 AM
Reply:

Similar Artilces:

Pulling data daily from a differently named report everyday
Hello, I have a spreadsheet where I use formulas to pull data from multiple reports on a shared server everyday. What I do is to save those reports under a certain name everyday since the formulas have to have the static report name in them to pull data. (For example, A1 may pull data from c:\reportfolder\reportA, B1 may pull from c: \reportfolder2\reportB, etc). In these report folders our IT group runs a new report in it everyday but of course they change the name of the report every day (to reflect the date) . . so in the reportfolder there will be "reportA-3-12-2009", and "...

Different margins on different pages
I am setting up a template for letters at work. The first page of our letterhead has one ste of margins and all subsequent pages will have a different sent of margins. I know that section breaks can be used, but if staff copy and paste text from existing letters into this new template, the section break moves. Is there any way of locking the section break to the page? so the pasted text goes over the top of it? Hi Lilly80, You could setup your document with a 'different first page' layout. That allows the first page to have a different set of margins to the rest o...

Money 2005 release date 9/21/04
Excerpted from the Money 05 press materials fact sheet which seems to have just been updated. Availability: Money Deluxe 90-day downloadable trial versions are scheduled to be available in September 2004 at http://www.microsoft.com/money/. Money Standard, Deluxe and Premium versions are scheduled to be available for purchase at retail or full download at http://www.microsoft.com/money/ on September 21, 2004. Were did you get this info. I can't find it on the MS site. "prokkos" <anonymous@discussions.microsoft.com> wrote in message news:132201c499a6$18a9ab50$a30...

due dates #3
I'm probably missing something here is what I'm doing Formula 1 =F5:F60= Formula 2 =MONTH(TODAY())=MONTH(F5:F60) Formula 3 =TODAY()>F5:F60 above is the actual typing I am placing in the formula box thanks for any help the "F" column contains due dates for recerts for aprox 55 people fro F5 down to F6 -- canma ----------------------------------------------------------------------- canman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1459 View this thread: http://www.excelforum.com/showthread.php?threadid=26223 Hi, try these step...

Different values for error bars in series
In Excel 2007, how do you put in different error bar values for each data point in a series? So far I am only able to put in one value for all in my bar chart. Hi, See Jon's blog on the subject. http://peltiertech.com/WordPress/error-bars-in-excel-2007/#comments Cheers Andy On 06/04/2010 15:52, wdwind1 wrote: > In Excel 2007, how do you put in different error bar values for each data > point in a series? So far I am only able to put in one value for all in my > bar chart. -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info ...

Out of Office Reply does not work
Hello all I've 3 Storage Groups in my Exchange environment and I'm hosting 3 Domains. In each of these 3 Storage groups are 5 Mailbox Stores. Many Users on this servers have multiple E-Mail addresses like user@company1.com, user@company2.at, ....) The first Storage group (installed by default) has no Problems with replying Out of Office messages. (e.g. all Mailbox-Stores can send Out of Office replies) The 2nd and 3rd Storage group cannot reply Out of Office messages, nor internal or external. I've tested it many times, but no Out of Office reply worked (internal and extern...

A Different CRM for GP
Hello, We are pondering the idea of implementing MS Dynamics CRM. However, I am curious whether anyone else has had success in integrating a different CRM product with GP 10.0. It's not that we don't like the MS product; I just like to ensure I have covered all bases. Our partner does not have any other recommendations as they have not worked with any other CRM applications. Any information would be greatly apprecaited. Thank you, -- Jessie GoldMine is a good product, very mature, and easier to maintain table structures. There are links to GP but I cannot remember the fir...

Dates changing in MPP?
When I enter my receipts I obviously use the transaction date for the date of the sale. However, I just noticed that a lot of my dates have changed by a few days when I compared against the Visa statement. Is there an issue where MPP changes the date? Even downloaded transactions match against the transaction date before I accept them or I'll change it. Thanks In microsoft.public.money, Geekster wrote: >When I enter my receipts I obviously use the transaction date for the date >of the sale. However, I just noticed that a lot of my dates have changed by >a few days ...

2 users access calendar online with different rights
hi user A wants to give user B permission to see, add and edit entries in calendar online http://www.exchangedomain.ch/usersname/kalender/ user B is able to see, but not to add or edit entries at the moment in outlook itself its working. user B can open the folder calendar of user A and make entries and change entries what have to be done that this is possible also with web access exchange ? thankx mike schwarz On Thu, 26 Oct 2006 14:09:29 +0200, "Mike Schwarz" <ctek@ctek.ch> wrote: >hi > >user A wants to give user B permission to see, add and edit entries in &...

Work Orders, Sales Taxes and Modifying Forms
Hey everyone, I am currently helping a client implement Microsoft POS, and have some random questions. 1) Is there a way to have the software default to a work order as opposed to a sales transaction? I tried adding a task pad button using the POS function "convert to work order" but that gives an error message. Also, the software defaults to the "Customer by Number" POS function every time a user logs in; can I have it run a task pad instead? 2) Also, can I set sales taxes per customer? Most of our customers do not pay any sales taxes (exempt boxes ...

Same column, different cell width at different row
I have tried inserting a break and split, didn't work. I just want to separate the top half of a page with the bottom half so I can apply different cell width on the same column. Or how would I be able to do this? Same column but different cell width. Thanks! Glenn You can't do that. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Glenn Chung" <GlennChung@discussions.microsoft.com> wrote in message news:20746997-9D99-4684-A352-120370D27036@microsoft.com... >I have tried inserting a break and split, di...

Fixed Assets
When using the Fixed Asset module, I am finding some small differences when compared to the Depreciation calculated by the External Accountants for tax purposes. Client would like to match exactly to the amounts calculated by external accounts. What is the best way to fix this issue? thanks, -- Patti Need more info Patti. What are the depreciation methods/settings that are creating the differences? Does this occur on every asset or just some? Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com get your gptip42today at www.gp2themax.blogs...

Dates Difference
Hi, I need to get the difference between 2 dates (in number of days), how do i get that in vc++? I have used the DateDiff function in asp but couldn't find anything like that. Also, how can i convert string to a date? thanks in advance karan >I need to get the difference between 2 dates (in number of days), how >do i get that in vc++? Karan, Convert (if the dates aren't already in this format) the dates/times to FILETIME and subtract the values. >Also, how can i convert string to a date? Try COleDateTime::ParseDateTime or VarDateFromStr. Dave -- MVP VC++ FAQ: http://ww...

Want to display value and difference on one chart
I am trying to create a chart that displays x and 10-x on the same chart. I want the chart to show a meaured value and the difference of that value to 10 in a different color. On Wed, 6 Aug 2008, in microsoft.public.excel.charting, JMH <JMH@discussions.microsoft.com> said: >I am trying to create a chart that displays x and 10-x on the same chart. I >want the chart to show a meaured value and the difference of that value to 10 >in a different color. Use your spreadsheet to calculate the values, then use the chart to graph them. Never try to use a chart for calculation. ...

Sending from 2 different computers
I have Outlook 2003 on my desktop at home and 2002 on my laptop while traveling. My server is Comcast. I would strongly prefer to use Outlook (rather than Comcast's webmail) on both computers. I have configured Outlook on both computers to receive email. I can also leave a copy on the server so it can be downloaded again later on the other computer, assuring that I always have copies of all emails no matter which computer I'm using. Problem is I can't send from my laptop. I've tried Comcast's instructions for configuring Outlook 2002 for use while traveling, but it doesn&...

Date for latest Update
Hi I want a cell show today() Date when somebody has change something in the document...Also the date will only change when I saving the document.... How I do that ?? //Sokoban ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ThisWorkbook.Names.Add Name:="LastSaved", _ RefersToR1C1:="=""" & _ Format(Now, "mmm-dd-yyyy hh:mm") & "&qu...

Different shape width in different views in Excel 2007??
In Excel 2007, I create a shape in "Normal" view and set its width to any value (e.g. to 15cm). Now I change to "Page Layout" view and guess what... the width now shows up as something more than 16cm??! (The width property as points in VBA changes accordingly.) Switching to "Page Break View" will result in one more different width value. Is this "just" a big bug in Excel 2007 or did I miss something?? I know that the selected default printer or the chosen zoom factor may have an influence on the shown size of a shape, but these discrepancies are ...

Date Range
Hi, When I want the date rage appeared on the report header, I use textbox and put (qry criteria) in its recordsource. It works fine. However, For this time, I got a message like this ‘First([Between [From this date] And [To this date]])" Is there something wrong with the qry criteria? Would you please show me how to fix the problem? Thanks Chi It's very hard to understand the crtieria, can you post the full SQL What is the First in the beginning? Why there are to square brackes in the end And in the beinning? First([Between [From this date] And [To this date]])" T...

appointment prints different time
Hi, My client uses a french CRM with date format dd/mm/yyyy. when he creates an appointment with the SFO client from e.g. 11 o'clock until 16 o'clock it is saved like correctly. but when he prints it out, the starttime is 8 o'clock and end time 13 o'clock. it is like that for every appointment. it always prints out 3 hours earlier. Anybody had this too? Thanx Cypress, You can check if both the server and the user are in the same time zone. Server: Open Control Panel, Date and Time, Timezone Client: On the laptop open Control Panel, Date and Time, Timezone Open CRM vi...

Different Columns on different pages
Hi - think this will be easily resolved but I am stuck! In publisher 2000 how do you create a multipaged, two page spread that has different columns on some pages. I keep bringing myself back to "ignore background" and trying to use "arrange" - "layout Guides" - but this isnt right. Am I thinking too deep? Is the whole point that the layout guides are simply guides and stay the same thorugh the whole document regardless.... you just use ruler guides to make the changes through the different pages? Any idea's? Thanks Julia ...

Unusual Date Format
I am writing an Excel 2002 template. Two columns of dates (date from and date to) are both imported from an external source. The date format is 'cyymmdd' so today would be '1100421'. The cells are formatted as text. I need to calculate the difference between the dates shown as a number of weeks, rounded down to the nearest whole number. Any help would be appreciated. =INT((DATE(LEFT(A3,3)+1900,MID(A3,4,2),RIGHT(A3,2))-DATE(LEFT(A2,3)+1900,MID(A2,4,2),RIGHT(A2,2)))/7) -- David Biddulph "Grey Old Man" <GreyOldMan@discussions.microsoft.com&g...

Difference 05-21-04
What is the difference between CRM standard and professional? -Johnny Johnny wrote: > What is the difference between CRM standard and professional? > > -Johnny Does it have anything to do with Outlook integration? -- - I am Johnny! Your Internet penpal. I have a scooter, how about you? "Johnny" <alphascooter-verizon@yahoo.com> wrote in message news:evfBJNwPEHA.3708@TK2MSFTNGP10.phx.gbl... > Johnny wrote: > > > What is the difference between CRM standard and professional? > > > > -Johnny > Does it have anything to do with Outlook inte...

divs are different in different browsers
I just ran accross something strange. I tried to used "Div Tags" instead of tables. So it looks something liket his: <div> <div></div><div></div> <div></div><div></div> <div></div><div></div> </div> It looks great in Internet Explorer, but firefox and chrome destroy it. Can anyone let me know on what to look for within the css or layout of the divs to make it work proper. Or in this case...is it just best to go back to tables within 1 div tag. Like this: <div> ...

Compare and Merge Two different CRM Instances
We have two different CRM environments that we want to merge into one. My first step was to try and compare the different instances to see what the different were between them. I exported all of the customizations from each and performed a diff. Yuck. Since the customizations don't export the elements in the same order it looks like their are over 7,000 differences between the two files. Reality says that at best there may be 50-100. Is there any way to get these to output in the same order so that I can get a more realistic diff between them? The only other thought I have at this po...

How do i block or protect final date gantt?? 12-21-09
How do i protect or block this final date from Gantt???? -- PaseEscolar ------------------------------------------------------------------------ PaseEscolar's Profile: http://forums.techarena.in/members/166005.htm View this thread: http://forums.techarena.in/microsoft-project/1284687.htm http://forums.techarena.in This is a multi-part message in MIME format. ------=_NextPart_000_0022_01CA825D.4AD20E10 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Hi PaseEscolar, Welcome to this Microsoft Project newsgr...