+ Reply to Thread
Results 1 to 10 of 10

Date Difference Bases on Cell Value

  1. #1
    Registered User
    Join Date
    12-10-2014
    Location
    Midwest
    MS-Off Ver
    MS 2010/MS 2013
    Posts
    12

    Date Difference Bases on Cell Value

    Hello Everyone,

    I have been working on a solution to my problem for some time. I have found a formula that works but gives a circular ref error. I would like to correct this error by using vba to accomplish the same task. I found a snipit of code which performs the general objective Im trying to achieve but have not been able to adapt it to my specific use. I figure I would consult the community and hopefully get some feedback on my method.

    Here is the task im trying to complete. I have an excel sheet with a number of categories that relate to product information. I created a userform that individuals can interact with to submit data to this excel sheet. One of the functions of this userform is to log the date and time of the user submission. Each submission has followup activities which another user addresses at a later time. When all of these actions have been completed, I would like to calculate the total disposition time from submission to completion. Essentially I want to run a formula based on a cell value change. Here is the circular ref formula I was using:

    Please Login or Register  to view this content.
    The Log Complete column is an "IF" formula that displays "Yes" when some criteria has been met and display "No" until the criteria has been met. When the formula displays "Yes" I attempting to calculate the different between the date and time NOW() minus the date and time of the entry in hours.

    Here is a reduced version of the excel sheet showing relevant cells:

    2015-07-25_10-03-19.jpg

    Here is the VBA I'm trying to modify.

    Please Login or Register  to view this content.
    Right not the code looks for a numerical change the Log Complete column. I would like this change to occur when rCell.Value = "Yes". I have been unsuccessful in completing this task. Additionally, I would like to modify the .Value = Now to actually equal (NOW()-Table1[Date and Time])*24. I believe I need to create a range and set to A:A to store date and time values.

    Any help or alternative solutions would be greatly appreciated. I am continuing to work on this and will post updates as I find them. Thanks in advance.
    Last edited by swisha; 07-25-2015 at 12:17 PM.

  2. #2
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Date Difference Bases on Cell Value

    Hello
    I may be missing something, but could you not just use:

    Please Login or Register  to view this content.
    Placed in the 'Throughput' column and custom format to: '[h]:mm' ? If you reference the 'Throughput' column with the formula in that column (as you have done), you'll get a circular reference as you describe.

    DBY

  3. #3
    Registered User
    Join Date
    12-10-2014
    Location
    Midwest
    MS-Off Ver
    MS 2010/MS 2013
    Posts
    12

    Re: Date Difference Bases on Cell Value

    Hello DBY,

    Thanks for the feedback. I attempted the approach you describe and found the formula was continually reevaluated. This caused the throughput time to inflate as the value of NOW() is always changing. I'm looking more of a time stamp function when certain criteria has been met. Thank you for the suggestion.

    Swisha

  4. #4
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Date Difference Bases on Cell Value

    Hi
    Yes I didn't realize you wanted a Time Stamp. The formula approach will constantly update as you refresh the workbook. Do you just want your user form to enter a Time Stamp alongside the 'Yes' in the 'Throughput' column? You can easily add the Now-Date & Time calculation to the column alongside the 'Yes' entry from your user form when adding data, without running a Worksheet Event.
    Last edited by DBY; 07-26-2015 at 12:01 PM.

  5. #5
    Registered User
    Join Date
    12-10-2014
    Location
    Midwest
    MS-Off Ver
    MS 2010/MS 2013
    Posts
    12

    Re: Date Difference Bases on Cell Value

    Hello again DBY,

    Yes that is what I’m trying to accomplish. The Throughput column should calculate Now-Date and Time when the Log Complete column updates to "Yes". My current formula give the circular ref described earlier. I was looking for an alternative solution and ran across the VBA code described above. Another alternative would be to add more columns to the form; one for a completion date and another for the calculation. It was my hope to perform these two steps in one go around to simply the form. I do like the idea of using VBA in order to prevent users from changing the code; which is an ongoing issue.
    Do you suggest any other approaches to this dilemma? Thank you for taking the time to help.

    Swisha

  6. #6
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Date Difference Bases on Cell Value

    Hi
    I've put together a sample form which is perhaps how I might go about this. The worksheet has a Table and the user form adds date; time and yes or no with a calculation if 'Yes' is chosen. There's also a validation check for the dates and time input.

    Hope it gives you a few ideas. Just to note, I'm still learning VBA myself so there may be a better way of doing this but try it out.

    DBY
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-10-2014
    Location
    Midwest
    MS-Off Ver
    MS 2010/MS 2013
    Posts
    12

    Re: Date Difference Bases on Cell Value

    I'm wondering if the formula above functions correctly, should I even change it. I am reading that you can allow a circular reference by turning on iterative calculations and specifying the number of iterations. Since I only want the formula to run one time after the first IF condition is met, I should be able to set the iterations to one.

  8. #8
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Date Difference Bases on Cell Value

    Hi
    I'm still not sure why you need to even reference the 'Throughput Time' column with your formula. You also said a formula solution was no good as it updates with the sheet:

    I attempted the approach you describe and found the formula was continually reevaluated.
    That is why you wanted a VBA Time Stamp value which will not update each time the sheet is recalculated. Does my example form not give you any ideas? You said you were using a user form to submit data.

    DBY

  9. #9
    Registered User
    Join Date
    12-10-2014
    Location
    Midwest
    MS-Off Ver
    MS 2010/MS 2013
    Posts
    12

    Re: Date Difference Bases on Cell Value

    Hello DBY,

    I reference the “Throughput Time” column as a means to only evaluate the NOW-Date & Time formula one time. For example, when the worksheet event calls for the formula to reevaluate, the Throughput formula checks itself to determine if a value has already been determined. If so, then do not reevaluate the formula. This was the circular reference. The formula looks to be under control since I turned on iterative calculation function.
    I was looking for a slightly different approach than you offered in your code. I was attempting to create a range for the “Log Complete” column. This approach would trigger the NOW-Date & Time formula in the event any value within the range was equal to “Yes”. Additionally, I think I need to create ranges for the Date & Time column as well as Log Complete. However, I am having difficulty extracting the .Value of each cell within the range. If that makes since.
    In your code, if I change the “Log Complete” value to “Yes”, the code does not execute. Ideally I believe VBA would be the most robust approach to this solution. Moving forward, I will continue to dig into this topic; if anything this will expand my knowledge on working on ranges. I do appreciate your help on the topic and please, if I’m misunderstanding your approach please enlighten me. I just hope I’m not overthinking the problem at hand. Again, thank you for your support thus far.

    Swisha

  10. #10
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Date Difference Bases on Cell Value

    Hi
    I guess I'm not understanding exactly what it is you're attempting. My User Form inserted a 'Yes' or 'No' into the 'Log Completed?' column and then the Time Stamp in the adjacent column. However, if you are manually entering 'Yes' or 'No' into your Table, then you would perhaps need a Worksheet Change Event to enter the Time Stamp.

    See the attached file. If you type 'Yes' into the 'Log Completed?' column it enters the Time Stamp. However, if a Time Stamp already exits it does nothing. Typing 'No' or any other value will result in the Time Stamp being cleared.

    DBY
    Attached Files Attached Files
    Last edited by DBY; 07-28-2015 at 12:09 PM. Reason: Uploaded New Example File

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Hide columns bases on unique date value in each column
    By salazarrn in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-28-2013, 06:24 PM
  2. Color code specific cells (on a per row bases) based on textual value within cell
    By Davey19 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 09-15-2013, 08:22 PM
  3. [SOLVED] Difference between two Date and Times cell in Hours
    By kidengineer in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-23-2013, 04:05 AM
  4. Display max of date from column A bases on unique value in column A
    By saikrishna_tunga in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-05-2013, 10:02 PM
  5. Difference in NETWORKDAYS, but to current date if cell is empty
    By Dan.Reynolds in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-22-2013, 05:59 PM
  6. Replies: 0
    Last Post: 02-03-2013, 06:33 PM
  7. Return a Row Entry bases upon Cell Value
    By Staci in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-23-2012, 08:07 PM
  8. Difference in Date blank cell problem
    By qwertyas in forum Excel General
    Replies: 2
    Last Post: 07-11-2010, 06:33 AM

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