Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: [Solved] VBA : Difference between two dates

  1. #1
    Join Date
    28th August 2003
    Location
    London
    Posts
    106
    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

    VB:
    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 at 11:36.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,678
    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

    Web Presence:
    LinkedIn: Will Riley

  3. #3
    Join Date
    28th August 2003
    Location
    London
    Posts
    106
    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    25th January 2003
    Location
    Christchurch, New Zealand
    Posts
    24
    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    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.

  6. #6
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    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.

  7. #7
    Join Date
    15th April 2003
    Location
    USA
    Posts
    7,433
    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."

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035
    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.

  9. #9
    Join Date
    28th August 2003
    Location
    London
    Posts
    106
    Thank you all for your tips/suggestions...the professors formula worked!

    MZP

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    25th January 2003
    Location
    Christchurch, New Zealand
    Posts
    24
    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Difference Between 2 Dates
    By shevtsov in forum EXCEL HELP
    Replies: 1
    Last Post: October 21st, 2006, 11:54
  2. Difference in years between 2 dates
    By samson in forum EXCEL HELP
    Replies: 2
    Last Post: June 23rd, 2005, 13:13
  3. Difference between 2 dates
    By HarryFrohlich in forum EXCEL HELP
    Replies: 12
    Last Post: July 19th, 2004, 04:18
  4. Difference between dates
    By rajeevg in forum EXCEL HELP
    Replies: 6
    Last Post: June 23rd, 2004, 17:07

Bookmarks

Posting Permissions

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