Automatically Converting Formula in a Cell to its Value at the End of the Day

[Ronald Lawrence]

I want the dynamic values in a cell to show until midnight and then like
Cinderella convert to its value only when the clock strikes 12 midnight i.e.
when the date changes to the next day, i.e. I'm using the =TODAY() function
for this.

I am using an "IF" formula and while the date =TODAY() is "true" the formula
provides dynamic values but when the date is not true (a fraction of a
second after midnight) I want the cell the show its last value on the
previous day.

Is this possible in Excel 2000?

TIA to all replies.

 

[Guest]

when do you want it to change?
i fit is a specific time, you can use the =today()-XXX where xxx is the
number of seconds you want the data to stay there divided by the number of
seconds in a day
If it is not a specific time, we need more info on what you want.

 

[Ron Rosenfeld]

I want the dynamic values in a cell to show until midnight and then like
Cinderella convert to its value only when the clock strikes 12 midnight i.e.
when the date changes to the next day, i.e. I'm using the =TODAY() function
for this.

I am using an "IF" formula and while the date =TODAY() is "true" the formula
provides dynamic values but when the date is not true (a fraction of a
second after midnight) I want the cell the show its last value on the
previous day.

Is this possible in Excel 2000?

TIA to all replies.

What is your formula?
--ron

 

[Ronald Lawrence]

I am tying to develop this formula in cell BT10:

=IF(TODAY()=BT8,Main!$A$20,converted value of $A$20 at midnight)

Where BT8 is today's date in a row of week days (for full year)
Where $A$20 is the dynamic cell in a Sheet called "Main" - this is
Cinderella
BT10 is the cell where I want the value of $A$20 to be recorded at midnight.
I have been playing with VALUE(Main!$A20) in the "false" condition area but
that hasn't been working for me.

The problem is I don't know how to convert the $A$20 cell on condition
(false) to its value only.

Basically, Main!$A$20 is the cell where the sum of the price of shares which
change throughout the day (typed in hourly for me). I want BT10 to record
its value at midnight automatically for trend graphing purposes and so that,
the next day, Main!$A$20 is working on cell BS10 (relating to the date at
BS8) and so on.

Obviously, since I am trend graphing I want this formula to apply to many
similar cells BT11, BT12, etc down the column for that date. Presently,
because I can't get the formula to work, I have to type in the value of all
these cells at the end of the day. It's not a huge chore (only about twenty
cells) but I would like the satisfaction of having it done automatically and
of knowing how to program this in future.

Thanks for your response Ron, hope you can help.

 

[Guest]

in other words you want to record the value in A20 in another cell one a day
at time = 00:00.
do you want it to record in a different cell each day, or do you copy out
the value from BT10 each day yourself?

right click on the tab
select this workbook section and try something like

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Static olddate As Date
If Date <> olddate Then
Range("BT10") = Cells(20, 1)
olddate = Date
End If
End Sub


End Sub

 

[Ron Rosenfeld]

I am tying to develop this formula in cell BT10:

=IF(TODAY()=BT8,Main!$A$20,converted value of $A$20 at midnight)

Where BT8 is today's date in a row of week days (for full year)
Where $A$20 is the dynamic cell in a Sheet called "Main" - this is
Cinderella
BT10 is the cell where I want the value of $A$20 to be recorded at midnight.
I have been playing with VALUE(Main!$A20) in the "false" condition area but
that hasn't been working for me.

The problem is I don't know how to convert the $A$20 cell on condition
(false) to its value only.

Basically, Main!$A$20 is the cell where the sum of the price of shares which
change throughout the day (typed in hourly for me). I want BT10 to record
its value at midnight automatically for trend graphing purposes and so that,
the next day, Main!$A$20 is working on cell BS10 (relating to the date at
BS8) and so on.

Obviously, since I am trend graphing I want this formula to apply to many
similar cells BT11, BT12, etc down the column for that date. Presently,
because I can't get the formula to work, I have to type in the value of all
these cells at the end of the day. It's not a huge chore (only about twenty
cells) but I would like the satisfaction of having it done automatically and
of knowing how to program this in future.

Thanks for your response Ron, hope you can help.

Hmm.

Not sure exactly how your data is set up. It sounds as if your dates start in
BT8 and then proceed to the left. Of course, that doesn't leave you room for a
full years trading dates, so maybe I'm missing something.

In any event, it seems you are going to need VBA to do what you want. Probably
an event macro that looks at the value in Main!A20 when it changes, and just
writes it into the appropriate cell in your BT range.

You should be able to modify the following to work with your layout:

Right click on the Main sheet tab and select View Code.

Paste the code below into the window that opens.

Basically, if A20 changes, it writes the value into the cell in row 10 that
corresponds with TODAY's date on your system clock. So when the last entry is
made TODAY, that value will be placed in the cell corresponding to TODAY.

=========================================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range
Dim Dts As Range
Dim c As Range

Set AOI = [$A$20]
Set Dts = Worksheets("Sheet2").Range("BT8:B8")

If Not Intersect(Target, AOI) Is Nothing Then
With Dts
Set c = .Find(Date, LookIn:=xlValues)
If c Is Nothing Then Exit Sub
End With
c.Offset(2, 0).Value = Target.Value
End If

End Sub
=======================================

I don't know where you are getting your stock quotes from. But if they are
carried on MSN Money, you might be able to use Microsofts MSN Money Stock Quote
add-in to help automate the process.

===============================

--ron

 

[Ronald Lawrence]

Thanks Ron. I will work on this today and see how it goes. I appreciate
very much the work you have put I and hope it gives me the result I want.