Announcement

Collapse
No announcement yet.

[Solved] VBA : Difference between two dates

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • [Solved] VBA : Difference between two dates

    This is an age old query, I received help some months back on calculating the difference between two dates. There remains one problem:

    What I have at the moment:
    I have a cell called CURRENT and one called TARGET.

    Current has todays date and target has 29/07/05.

    I have a cell called TO GO which has the formula = Target - Current

    The cell is formated in the following: yy mm dd hh:mm:ss

    There is VBA which updates NOW so that I have a countdown.
    (Dim SchedRecalc As Date

    Code:
    Sub SetTime()
        SchedRecalc = Now + TimeValue("00:00:01")
        Application.OnTime SchedRecalc, "Recalc"
    End Sub
    
    Sub Recalc()
        Range("Current").Value = Now
        Call SetTime
    End Sub
    
    Sub Disable()
        On Error Resume Next
        Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
    End Sub)
    All this works fine.

    The Problem:

    The result in the cell TO GO is not accurate. It does not show the exact number of days to go. For example if Current = 29/07/04 and Target = 29/07/05 I want to see TO GO showing that there are 12 Months and 0 days to go.

    Any ideas why the DAY part is not giving the answer I am expecting?

    MZP
    Last edited by Dave Hawley; June 29th, 2004, 10:36.

  • #2
    What answer are you getting at the moment?

    BTW - Using NOW() will only give you the 12months for the duration of 1 second because the day,hour,minute etc will only be Zero, for example on the stroke of midnight....

    Posting up the answer you currently get & the answer you'd like is always helpful in these circumstances...

    Will
    Kind Regards, Will Riley

    LinkedIn: Will Riley

    Comment


    • #3
      I am still having difficulty with this,

      As an example, if I have the current date as 28/07/05 and the target date as 29/07/05 the formula I have used is to simply subtract one by the other and the result is 1. This is obviously correct, there is 1 day to go. The problem appears to be with the number format I am using (see below):

      yy "Years", mm "Months", dd "Days", hh "Hours", mm "Minutes", ss "Seconds"

      The format above is showing the value 1 as being the month.

      What I am looking for is: 0 Years, 0 Months, 1 Day etc

      Had the current date been 28/06/05 and the target 29/07/05 I would like the result cell to display 0 years, 1 month, 1 day etc

      Any suggestions on how I can achieve my objective as everything else (the code for the countdown etc)is working fine?

      MZP

      Comment


      • #4
        I use the following formula on the spreadsheet which does give the answer you're after.
        Namely: =DATDIFF(A6,F6)
        With the first date in A6 and the second in F6

        HTH
        Regards,
        Iain Anderson

        Comment


        • #5
          The problem is that your formula (and DATEDIFF) give an integer answer in number of days. This is not a date that can be formatted into years, months, days, etc. If you want that kind of display, you'll need to write formulas to calculate the number of months (30 days/month??) and years (365/year except this is a leap year).... Get's messy.

          If you format the integer as a date, it will start with 1=1 Jan 1900 (if I rememebr correctly).
          Best Regards,
          Tom
          ---------------------------
          Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

          Comment


          • #6
            Just to add a bit.

            If cell A1 is your target date, you could use a formula like:

            =YEAR(A1)-YEAR(TODAY()) & " Years " & MONTH(A1)-MONTH(TODAY()) & " Months " & DAY(A1)-DAY(TODAY()) & " Days"

            But this would only be a start.

            For example try the above with day in a subsequent month that is a lower day of the month than today. For example, using May 10th as a target for today (14 April) will return.

            0 Years 1 Months -4 Days

            Note the MINUS 4 days.

            So you would need to process this further to convert negative numbers to positive and decriment the previous number by one. Do do this accurately, you need to use the numbe of days in the current month.

            =DAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0))

            will give you the last day of the current month.
            Best Regards,
            Tom
            ---------------------------
            Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

            Comment


            • #7
              The following formula seems to work, where cell B1 has the target as and Excel date time and cell B2 has =NOW()
              =YEAR(B1-B2)-1900&" years, "&MONTH(B1-B2)-1&" months, "&DAY(B1-B2)&" days, "&HOUR(B1-B2)&" hours, "& MINUTE(B1-B2)&" minutes, and "&SECOND(B1-B2)&" seconds."
              Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

              Comment


              • #8
                Best Regards,
                Tom
                ---------------------------
                Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

                Comment


                • #9
                  Thank you all for your tips/suggestions...the professors formula worked!

                  MZP

                  Comment


                  • #10
                    In my spreadsheet I have the following formula: =DATDIFF(A6,F6) and that gives the answer; 0 Years, 0 Months, 7 Days

                    The difference between both days being 7 days. Why should this be wrong?
                    Please note that the formula is =datdiff(a6,f6) and not =datediff

                    Regards,
                    Iain Anderson

                    Comment


                    • #11
                      Hi, Iain,

                      You are correct, but he was also looking to break the differnence down to hours, minutes and seconds. That requires bringing in the NOW() function (as Derk did), I think. (I actually don't have DATDIFF() on my home system (Excel 97) -- not sure if it comes with a later Excel release or I haven't got the needed AddIn loaded. But as I remember from other threads, DATDIFF returns an integer number of days.)

                      Fun problem, no?
                      Best Regards,
                      Tom
                      ---------------------------
                      Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

                      Comment

                      Working...
                      X