Excel Formula Conundrum

[JamieS]

Hello all, I have an abnormal issue. I hope you folks find it interesting...

I'm working on 2 cells. Cell 1 is a formula that shows a date which fluctuates daily. I want Cell 2 to show the earliest date Cell 1 has ever had.

Ex. Cell 1 shows 3/15 on day 1, 3/18 on day 2, and 3/23 on day 3. I would like Cell 2 to show 3/15 for all 3 days.

Is that even possible?? Any help or guidance you guys can give me is greatly appreciated.

 

[Joe4]

Welcome to the Board!

Excel has no memory of what was in the cell prior to the current value, but you might be able to use some automated VBA code to capture the values are store the lowest value in another cell.
So as cell C1 is updated, it would look at the value in C2. If C1 is less than C2, then it would update the value in C2 to that value. Otherwise, it would stay the same.
The big question is, how and when exactly is the value in C1 being updated?

 

[pitchoute]

Since Cell 1 contains a formula that updates its date daily, we need a way to store the earliest date that has ever appeared in Cell 1. However, Excel formulas alone cannot keep track of historical values over multiple days because they recalculate each time the worksheet is recalculated.

To solve this, we can use a VBA macro that runs whenever the worksheet recalculates. This macro will compare the current value of Cell 1 with the stored minimum date in Cell 2 and update Cell 2 if necessary.

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    On Error GoTo CleanUp

    Dim CurrentDate As Variant
    Dim StoredMinDate As Variant

    ' Replace "A1" with your Cell 1 address
    CurrentDate = Range("A1").Value

    ' Replace "A2" with your Cell 2 address
    If IsEmpty(Range("A2").Value) Then
        ' Initialize Cell 2 with the current date
        Range("A2").Value = CurrentDate
    Else
        StoredMinDate = Range("A2").Value

        If IsDate(CurrentDate) And IsDate(StoredMinDate) Then
            If CurrentDate < StoredMinDate Then
                Range("A2").Value = CurrentDate
            End If
        ElseIf IsDate(CurrentDate) Then
            Range("A2").Value = CurrentDate
        End If
    End If

CleanUp:
    Application.EnableEvents = True
End Sub

 

[Joe4]

I see you liked my reply.
If you answer the questions I asked, we may be able to help you set something up.
Or have you already figured it out yourself?

 

[Peter_SSs]

@pitchoute
When posting vba code in the forum, please use the available code tags (not Quote tags). It makes your code much easier to read/debug & copy. My signature block below has more details. I have changed the tags for you this time.

 

[JamieS]

I see you liked my reply.
If you answer the questions I asked, we may be able to help you set something up.
Or have you already figured it out yourself?

Hi Joe, thank you for your answer and please excuse the late response. I was hoping to stay away from using macros, partially because I'm ignorant of them, but mostly because the computer I use does not belong to me.

I'm not sure it will help, but Cell 1's formula is simply =TODAY()+M10. M10 itself adds a few other cells, then takes that total and divides by a number to get the number of days.

I was hoping to simply use an IF statement to keep the spreadsheet clean, but I haven't worked out how to get it to work yet.

 

[Joe4]

Like I said, Excel has no memory of past values in the cell. Excel formulas can only work on the values present on the sheet right now (not what they were before).
So unless you are manually storing the earliest date in some cell somewhere (so that the value is hard-coded, a not a formula), I don't think you can do what you want just with formulas and no manual intervention. That is unless you use VBA.

 

[Peter_SSs]

Try this with a copy of your workbook (perhaps on a new blank worksheet). I am assuming cell 1 is A1 and cell 2 is B1

1. Go to File -> Options -> Formulas -> In the Calculation options at top right select "Enable iterative calculation" and set Maximum Iterations to 1 -> OK
2. As a test put this formula in M10 =RANDBETWEEN(1,9999)
3. Format A1:B1 with a date format
4. In A1 put =TODAY()+M10
5. Ensure B1 is empty
6. Now in the empty B1 put the formula =IF(B1=0,A1,MIN(A1,B1))
7. Take note of the B1 date and press F9 to recalculate the sheet.
8. Repeat step 7 as many times as you like

 

[JamieS]

Try this with a copy of your workbook (perhaps on a new blank worksheet). I am assuming cell 1 is A1 and cell 2 is B1

1. Go to File -> Options -> Formulas -> In the Calculation options at top right select "Enable iterative calculation" and set Maximum Iterations to 1 -> OK
2. As a test put this formula in M10 =RANDBETWEEN(1,9999)
3. Format A1:B1 with a date format
4. In A1 put =TODAY()+M10
5. Ensure B1 is empty
6. Now in the empty B1 put the formula =IF(B1=0,A1,MIN(A1,B1))
7. Take note of the B1 date and press F9 to recalculate the sheet.
8. Repeat step 7 as many times as you like

You're a genius, sir. Thank you!!

 

[Peter_SSs]

You're welcome. Glad to help. Thanks for the follow-up.