Hi Everyone, I have a problem in Microsoft Excel and I hope someone will find a solution for me.

In cell A1 I have =Today()
in cell B1 I have a code that gives me the time in real time
From cells E6:E369 I have a calendar
From Cells H6:H369 I wrote this formula,
IF(ISNA(VLOOKUP(E6,$A$1:$B$1,2,FALSE)),0,VLOOKUP(E 6,$A$1:$B$1,2,FALSE))
This gives me a '0' where the date does not match and the time where the date matches.
Is there a way of how I can keep the time in the matching cell without
changing (i.e. stopping the clock in the relevant cell) and keep the result even when the date changes the next day.

Thank you
Albert

Hi,

You will need to code this with VBA, you will have to convert the formula
to a value anytime there is a match. Once converted the formula will be
lost, is that acceptable?
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"AlbertMBartolo" wrote:


Hi Everyone, I have a problem in Microsoft Excel and I hope someone will
find a solution for me.

In cell A1 I have =Today()
in cell B1 I have a code that gives me the time in real time
From cells E6:E369 I have a calendar
From Cells H6:H369 I wrote this formula,
IF(ISNA(VLOOKUP(E6,$A$1:$B$1,2,FALSE)),0,VLOOKUP(E 6,$A$1:$B$1,2,FALSE))
This gives me a '0' where the date does not match and the time where
the date matches.
Is there a way of how I can keep the time in the matching cell without

changing (i.e. stopping the clock in the relevant cell) and keep the
result even when the date changes the next day.

Thank you
Albert




--
AlbertMBartolo

Hi,

Here is the code:

Sub ConvertToValue()
Dim cell As Range
For Each cell In Range("H6:H369")
If cell < 0 Then
cell.Value = cell
End If
Next cell
End Sub

However, you could attach this to the Workbook_Open even or
Workbook_BeforeSave event or many others. Otherwise you will need to run it
manually, which is certainly ok.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"AlbertMBartolo" wrote:


Hi Everyone, I have a problem in Microsoft Excel and I hope someone will
find a solution for me.

In cell A1 I have =Today()
in cell B1 I have a code that gives me the time in real time
From cells E6:E369 I have a calendar
From Cells H6:H369 I wrote this formula,
IF(ISNA(VLOOKUP(E6,$A$1:$B$1,2,FALSE)),0,VLOOKUP(E 6,$A$1:$B$1,2,FALSE))
This gives me a '0' where the date does not match and the time where
the date matches.
Is there a way of how I can keep the time in the matching cell without

changing (i.e. stopping the clock in the relevant cell) and keep the
result even when the date changes the next day.

Thank you
Albert




--
AlbertMBartolo

Thank You ever so much Shane, it worked perfectly, exactly what I wanted. But sorry I can not find the Yes button