Hi all, i have been trying to check a cell that contains NOW() for a
time and populate another cell based on an evaluation of a formula with
a concatenation that looks like NOW().

I need to put a certain date AND a certain time in a cell but it can't
be text as the cell is used by the network for PI tag data retrival, as
you can see from the coe i have tried a number of things, the cell is
custom formatted to dd/mm/yyyy hh:mm.

Any ideas?



VBA Code:
--------------------


Sub Formula_Add()
Dim eformula, d As Date, MyTime, MyTime1
Application.ScreenUpdating = False
d = CDate(Format(Date, "dd/mm/yyyy")) - 1

MyTime = #3:45:00 AM#
MyTime1 = #5:45:00 PM#
MsgBox CDate(d)

eformula = "=IF(AND(MOD('Sheet1'!B1,1)TIMEVALUE(" & Chr(34) & "07:00" & Chr(34) & _
"),MOD('Sheet1'!B1,1)<TIMEVALUE(" & Chr(34) & "19:00" & Chr(34) & "))," & Chr(34) & "Days" & Chr(34) & _
"," & Chr(34) & "Nights" & Chr(34) & ")"

If Evaluate(eformula) = "Nights" Then
Sheets("Sheet1").Range("A1").Value = d & " " & MyTime 'TimeValue("05:45")
Else
'Sheets("Sheet1").Range("A1").Value = Format(Date, "dd/mm/yyyy") & " 17:45"
'Sheets("Sheet1").Range("A1").Value = Date - 1 & " 17:45"
Sheets("Sheet1").Range("A1").Value = CDate(d) & " 17:45"
End If

End Sub
--------------------


--
Simon Lloyd

Regards,
Simon Lloyd
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)
------------------------------------------------------------------------
Simon Lloyd's Profile: 1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=186566

Excel Live Chat