Time and Vlookup Problem

[AlbertMBartolo]

Hi Everyone, I have a problem in Microsoft Excel and I hope someone wil
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(E6,$A$1:$B$1,2,FALSE))
This gives me a '0' where the date does not match and the time wher
the date matches.
Is there a way of how I can keep the time in the matching cell withou

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

Thank you
Alber

 

[Shane Devenshire]

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?

 

[Shane Devenshire]

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.

 

[AlbertMBartolo]

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

;801717']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


:
-

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(E6,$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