|
Adding date and time to cell programatically?
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 |
Adding date and time to cell programatically?
I should have mentioned i can get the date and time in the cell but instead of the date being 10/03/2010 it always enters as 03/10/2010....i'm in the UK Simon Lloyd;667669 Wrote: 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 |
Adding date and time to cell programatically?
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 "Simon Lloyd" wrote in message ... 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 |
Adding date and time to cell programatically?
Cheers Bob, works a treat, IIf ! i'm sure you discussed this with me a few years back, guess i wasn't listening :) Bob Phillips;667739 Wrote: 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 "Simon Lloyd" wrote in message ... 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)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 1 View this thread: 'Adding date and time to cell programatically? - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=186566) 'Excel Live Chat' (http://"http://www.thecodecage.com/forumz/") -- 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 |
| All times are GMT +1. The time now is 03:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com