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.