Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm trying to use datedif to calculate the difference between two dates. It seems to be missing from my function list. I have the analysis toolpak installed. hmmm any ideas?

    What I want to do is calc the diff between two dates, and if the dates are the same date, return a 1 instead of a zero. Am I correct in thinking datedif will give me this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    DATEDIF is built into Excel but it is not documented officially (a very strange omission by Microsoft).

    See DATEDIF Worksheet Function for the syntax.

    When the two dates are the same, DATEDIF will return 0, so you'd have to add 1 if you want the result to be 1.

    Note: if you want to calculate the number of days between two dates, you don't need DATEDIF. You can simply subtract the dates, e.g.

    =B1-A1

    Excel tends to format the result as a date; to see the number of days set the number format of the cell with the formula to General.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks!

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    [quote name='MelanieB' post='795505' date='29-Sep-2009 16:32']What I want to do is calc the diff between two dates, and if the dates are the same date, return a 1 instead of a zero. Am I correct in thinking datedif will give me this?[/quote]

    if you are not interested in what the actual difference is, and just want a 0/1 returned, you could try this - assuming the dates are in A1 & B1
    =1*(A1=B1) which gives 1 if the dates are the same else giving 0

    If you do want to know the actual difference
    =if(A1=B1,1,A1-B1) gives 1 if the dates are the same, and the difference if they are not. If using this method the next question would be how to tell whether a result of 1 represents the same date in both fields or successive dates - conditional formatting anyone?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Sometimes people want to count the number of days in the range Date1 ... Date2.
    If Date1 = Date2, there is 1 day.
    If Date1 and Date2 are consecutive days, there are 2 days, etc.

    This can be calculated as =Date2-Date1+1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •