+ Reply to Thread
Results 1 to 13 of 13

Macros for: If Today's Date minus other date is greater than certain amount of days Then

  1. #1
    Registered User
    Join Date
    08-19-2012
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2010
    Posts
    62

    Macros for: If Today's Date minus other date is greater than certain amount of days Then

    Any help will be appreciated for this newbie.
    For my purposes, I must have a macro.

    I need this macro to go through rows of data. The amount of rows is unknown and sometimes there are blank rows.
    The macro needs to take Today's Date and subtract a date in column C to get the difference in days.
    *Note: the dates in Column C are values and Column C is formatted as general.

    If the days are greater than 30, then the entire row's contents will be cleared (not deleted).
    I think I need some kind of loop to run until there are no more rows of data (but I haven't been able to get it to work yet).

    The data will look something like this:

    ______A________________B_______________C____________________
    1 ____info _____________info____________ 10/14/2012
    2 ____info _____________info____________ 10/01/2012
    3
    4 ____info _____________info____________ 06/14/2008
    5 ____info _____________info____________ 01/31/1999
    6
    7
    8 ____info _____________info____________ 10/19/2012
    9 ____info _____________info____________ 03/21/2012

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macros for: If Today's Date minus other date is greater than certain amount of days Th

    Perhaps a macro like this could be of help?

    Please Login or Register  to view this content.

    Alf

  3. #3
    Registered User
    Join Date
    08-19-2012
    Location
    Pennsylvania, USA
    MS-Off Ver
    Excel 2010
    Posts
    62

    Re: Macros for: If Today's Date minus other date is greater than certain amount of days Th

    Brilliant Alf!
    Worked like a charm!

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macros for: If Today's Date minus other date is greater than certain amount of days Th

    Glad to be of help. Thanks for rep!

    Alf

  5. #5
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Macros for: If Today's Date minus other date is greater than certain amount of days Th

    Just to add to this, I noticed you used Date in your macro. Would the following be fine? It subtracts two dates and if the value is 4 calls another macro.

    Please Login or Register  to view this content.

  6. #6
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macros for: If Today's Date minus other date is greater than certain amount of days Th

    I noticed you used Date in your macro. Would the following be fine?
    Yes but remember the vb expression "Date" is equivalent the worksheet function "=TODAY()"

    Alf

  7. #7
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Macros for: If Today's Date minus other date is greater than certain amount of days Th

    So if in range B1 I have a future date and want the macro to be called if the difference is 4 I can't use Date in the VB code?

  8. #8
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macros for: If Today's Date minus other date is greater than certain amount of days Th

    Sorry, guess my explanation was a bit muddled.

    Yes you can use "Date" if you whish to find the difference in days between a future date and today’s date.

    Alf

  9. #9
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Macros for: If Today's Date minus other date is greater than certain amount of days Th

    Quote Originally Posted by Alf View Post
    Sorry, guess my explanation was a bit muddled.

    Yes you can use "Date" if you whish to find the difference in days between a future date and today’s date.

    Alf
    No problem. So then the code I posted above should work in theory?

  10. #10
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macros for: If Today's Date minus other date is greater than certain amount of days Th

    Yes but you need to modify your code a bit:

    Please Login or Register  to view this content.
    Alf

  11. #11
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Macros for: If Today's Date minus other date is greater than certain amount of days Th

    Would this also work for subtracting time? Where B1 is a time formatted hh:mm? So if its one hour 30 minutes difference then call Macro2?

    Please Login or Register  to view this content.
    Last edited by ScabbyDog; 10-24-2012 at 12:09 PM.

  12. #12
    Valued Forum Contributor
    Join Date
    03-03-2009
    Location
    UK
    MS-Off Ver
    MS365 Subscription Excel for Mac
    Posts
    1,017

    Re: Macros for: If Today's Date minus other date is greater than certain amount of days Th

    I've tried the above code but it doesn't call Macro2 or return me an error so I'm presuming it is taking B1 - Time and the result isn't 01:30. Even though I've set it up so the difference is 1 hr 30 mins it still doesn't call Macro2.

  13. #13
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Macros for: If Today's Date minus other date is greater than certain amount of days Th

    Tested you code and it works for me. Could your problem be that the vba "Time" function is dynamic?

    Why don't you try and test it with two macros like this:

    Please Login or Register  to view this content.
    Then you can se if the time differnce is what you think it should be and you have the "propper" formate on the time function.

    Alf

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1