Adding date and time to cell programatically?

[Simon Lloyd]

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]

I should have mentioned i can get the date and time in the cell bu
instead of the date being 10/03/2010 it always enters a
03/10/2010....i'm in the U

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

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

Any ideas

VBA Code:
--------------------Sub Formula_Add(
Dim eformula, d As Date, MyTime, MyTime
Application.ScreenUpdating = Fals
d = CDate(Format(Date, "dd/mm/yyyy")) -

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" The
Sheets("Sheet1").Range("A1").Value = d & " " & MyTime 'TimeValue("05:45"
Els
'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 I

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

--
Simon Lloyd

Regards
Simon Lloy
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)

 

[Bob Phillips]

Sub Formula_Add()
Dim eformula As String, MyTime As Double, MyTime1 As Double
Application.ScreenUpdating = False

MyTime = #3:45:00 AM#
MyTime1 = #5:45:00 PM#

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

Sheets("Sheet1").Range("A1").Value = _
CDate(Date - 1) _
+ IIf(Evaluate(eformula) = "Nights", MyTime, MyTime1)

End Sub

 

[Simon Lloyd]

Cheers Bob, works a treat, IIf ! i'm sure you discussed this with me a
few years back, guess i wasn't listening


VBA Code:
--------------------
Sub Formula_Add()
Dim eformula As String, MyTime As Double, MyTime1 As Double
Application.ScreenUpdating = False

MyTime = #3:45:00 AM#
MyTime1 = #5:45:00 PM#

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

Sheets("Sheet1").Range("A1").Value = _
CDate(Date - 1) _
+ IIf(Evaluate(eformula) = "Nights", MyTime, MyTime1)

End Sub

--------------------



--

HTH

Bob

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' ('The Code Cage Forums' (http://www.thecodecage.com/forumz/chat.php))

Forums' (http://www.thecodecage.com/forumz/showthread.php?t=186566)

'Excel Live Chat' (http://&quot;http://www.thecodecage.com/forumz/&quot

--
Simon Lloyd

Regards,
Simon Lloyd
'Excel Chat' (http://www.thecodecage.com/forumz/chat.php)