+ Reply to Thread
Results 1 to 6 of 6

Fix minimum date calculation

  1. #1
    Forum Contributor
    Join Date
    04-03-2014
    Location
    Berlin
    MS-Off Ver
    Excel 2010
    Posts
    160

    Fix minimum date calculation

    Hello everybody,

    I've got a little issue with calculating the minimum date from a data set, as it displays "00.01.1900".

    The problem seems to be the missing data in some of the cells. In this case the code should of course take over the available minimum date from the cells where the data is available as long as it regards the same item no. in column A.
    I've been trying to edit the code, unfortunately without success. Sadly the upload of files does not seem to work either in this forum, so I am pasting the code below..

    Would you guys give a hand with that? Thanks a lot!

    Best regards

    HTML Code: 
    
    Option Explicit
    
    Sub GrandStartDate()
        
        Application.Calculation = xlCalculationAutomatic
        Call MinValues(Range("F2"), "Apple", Range("A2"), Range("D2"), Range("B2"))
            
    End Sub
     
    Public Sub MinValues( _
        rGrandStartDates As Range, _
        sServiceDescription As String, _
        rContracts As Range, _
        rServiceDescriptions As Range, _
        rStartDates As Range)
         
        Dim lastrow As Long
        Dim sFormula As String
        Dim rContract As Range
         
        With ActiveSheet
             
            lastrow = .Cells(.Rows.Count, rStartDates.Column).End(xlUp).Row
            Set rGrandStartDates = rGrandStartDates.Resize(lastrow - rGrandStartDates.Row + 1)
            Set rContracts = rContracts.Resize(lastrow - rContracts.Row + 1)
            Set rContract = rContracts.Cells(1, 1)
             
            Set rServiceDescriptions = rServiceDescriptions.Resize(lastrow - rServiceDescriptions.Row + 1)
            Set rStartDates = rStartDates.Resize(lastrow - rStartDates.Row + 1)
             
            sFormula = "=MIN(IF(" & rContracts.Address(True, True) & _
            "=" & rContract.Address(False, True) & _
            ",IF(" & rServiceDescriptions.Address(True, True) & _
            "=""" & sServiceDescription & """," & _
            rStartDates.Address(True, True) & _
            ")))"
             
            rGrandStartDates.Cells(1, 1).FormulaArray = sFormula
            rGrandStartDates.Cells(1, 1).AutoFill Destination:=rGrandStartDates, Type:=xlFillDefault
            rGrandStartDates.Value = rGrandStartDates.Value
             
        End With
        
            Columns("F:G").Select
            Selection.NumberFormat = "dd/mm/yyyy"
        
        Call GrandEndDate
        
    End Sub
    Attached Files Attached Files
    Last edited by vio.coman; 12-21-2016 at 08:25 AM.

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Fix minimum date calculation

    I'm not sure but just try changing FormulaArray to Formula

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,401

    Re: Fix minimum date calculation

    Please Login or Register  to view this content.

    =MIN(IF(($A$2:$A$9=$A2)*($D$2:$D$9="Apple")*($B$2:$B$9>0),$B$2:$B$9))
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Forum Contributor
    Join Date
    04-03-2014
    Location
    Berlin
    MS-Off Ver
    Excel 2010
    Posts
    160

    Re: Fix minimum date calculation

    Thanks Mike, it does not really work...

  5. #5
    Forum Contributor
    Join Date
    04-03-2014
    Location
    Berlin
    MS-Off Ver
    Excel 2010
    Posts
    160

    Re: Fix minimum date calculation

    Thanks a lot AlphaFrog, it works perfectly! ;-)

  6. #6
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,517

    Re: Fix minimum date calculation

    Okay I wasn't sure, just knew your formula was not an Array formula. Can you explain the process. It is had for me to understand being all your data is the same. Maybe upload another example workbook with some different data and show your expected results.

+ 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. Formula help for bonus calculation with minimum threshold?
    By wisey11 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-07-2015, 09:56 AM
  2. Replies: 9
    Last Post: 11-25-2014, 06:07 PM
  3. [SOLVED] How would I modify calculation to have value = zero (0) after reaching a minimum.
    By Jim15 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-18-2013, 02:14 PM
  4. [SOLVED] calculate equation, but report a minimum value if calculation is too low
    By Rule723 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-10-2012, 03:06 PM
  5. Replies: 3
    Last Post: 05-25-2012, 03:16 AM
  6. Finding Minimum Value of Calculation and Cell Reference
    By centerNegative in forum Excel General
    Replies: 3
    Last Post: 07-16-2011, 11:03 AM
  7. Excel 2007 : Minimum Taxes Calculation Worksheet
    By jwillis76 in forum Excel General
    Replies: 3
    Last Post: 01-12-2011, 11:42 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