| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to assign unit for TimeValue?
I would like to input the number of minutes in cell A1 to set the Time Value,
as shown below there is 30 in cell A1 (Unit - minute). Does anyone have any suggestions on how to the value retrieve from cell A1 is under minute unit? Thanks in advance for any suggestions Eric Sheets("Date").Range(A1).Value, TimeValue(Sheets("Date").Range(A1).Value) TimeValue("00:30:00") |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
Divide by 1440 will convert that to excel time format....
Sub Macro() 'with 30 in cell A1 Range("B1") = Range("A1") / 1440 Range("B1").NumberFormat = "[h]:mm:ss" End Sub -- Jacob (MVP - Excel) "Eric" wrote: I would like to input the number of minutes in cell A1 to set the Time Value, as shown below there is 30 in cell A1 (Unit - minute). Does anyone have any suggestions on how to the value retrieve from cell A1 is under minute unit? Thanks in advance for any suggestions Eric Sheets("Date").Range(A1).Value, TimeValue(Sheets("Date").Range(A1).Value) TimeValue("00:30:00") |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
Timevalue () expects a string as below
(taken from help) Dim MyTime MyTime = TimeValue("4:35:17 PM") Could you please elaborate what is your requirement here.... -- Jacob (MVP - Excel) "Eric" wrote: When I assign the value within cell B1, the TimeValue function is not working. Do you have any suggestions? Thanks in advance for any suggestions Eric Sheets("Date").Range("B1") = Range("A1") / 1440 Sheets("Date").Range("B1").NumberFormat = "[h]:mm:ss" TimeValue(Sheets("Date").Range("B1").Value) "Jacob Skaria" wrote: Divide by 1440 will convert that to excel time format.... Sub Macro() 'with 30 in cell A1 Range("B1") = Range("A1") / 1440 Range("B1").NumberFormat = "[h]:mm:ss" End Sub -- Jacob (MVP - Excel) "Eric" wrote: I would like to input the number of minutes in cell A1 to set the Time Value, as shown below there is 30 in cell A1 (Unit - minute). Does anyone have any suggestions on how to the value retrieve from cell A1 is under minute unit? Thanks in advance for any suggestions Eric Sheets("Date").Range(A1).Value, TimeValue(Sheets("Date").Range(A1).Value) TimeValue("00:30:00") |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
When I assign the value within cell B1, the TimeValue function is not working.
Do you have any suggestions? Thanks in advance for any suggestions Eric Sheets("Date").Range("B1") = Range("A1") / 1440 Sheets("Date").Range("B1").NumberFormat = "[h]:mm:ss" TimeValue(Sheets("Date").Range("B1").Value) "Jacob Skaria" wrote: Divide by 1440 will convert that to excel time format.... Sub Macro() 'with 30 in cell A1 Range("B1") = Range("A1") / 1440 Range("B1").NumberFormat = "[h]:mm:ss" End Sub -- Jacob (MVP - Excel) "Eric" wrote: I would like to input the number of minutes in cell A1 to set the Time Value, as shown below there is 30 in cell A1 (Unit - minute). Does anyone have any suggestions on how to the value retrieve from cell A1 is under minute unit? Thanks in advance for any suggestions Eric Sheets("Date").Range(A1).Value, TimeValue(Sheets("Date").Range(A1).Value) TimeValue("00:30:00") |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
Dim MyTime
MyTime = TimeValue("4:35:17 PM") If I would like to retrieve the number (in minute unit) and convert into ("4:35:17 PM") format, could you please give me any suggestions on how to do it? For example, there is 30 in cell A1, which unit is minute, and I would like to retrieve 30 value and turn into "00:30:00" string and place it inside TimeValue function. TimeValue(string), which string is equal to "00:30:00" Do you have any suggestions on how to do it? Thanks in advance for any suggestions Eric "Jacob Skaria" wrote: Timevalue () expects a string as below (taken from help) Dim MyTime MyTime = TimeValue("4:35:17 PM") Could you please elaborate what is your requirement here.... -- Jacob (MVP - Excel) "Eric" wrote: When I assign the value within cell B1, the TimeValue function is not working. Do you have any suggestions? Thanks in advance for any suggestions Eric Sheets("Date").Range("B1") = Range("A1") / 1440 Sheets("Date").Range("B1").NumberFormat = "[h]:mm:ss" TimeValue(Sheets("Date").Range("B1").Value) "Jacob Skaria" wrote: Divide by 1440 will convert that to excel time format.... Sub Macro() 'with 30 in cell A1 Range("B1") = Range("A1") / 1440 Range("B1").NumberFormat = "[h]:mm:ss" End Sub -- Jacob (MVP - Excel) "Eric" wrote: I would like to input the number of minutes in cell A1 to set the Time Value, as shown below there is 30 in cell A1 (Unit - minute). Does anyone have any suggestions on how to the value retrieve from cell A1 is under minute unit? Thanks in advance for any suggestions Eric Sheets("Date").Range(A1).Value, TimeValue(Sheets("Date").Range(A1).Value) TimeValue("00:30:00") |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
Try VBA()
Timeserial(0,30,0) -- Jacob (MVP - Excel) "Eric" wrote: Dim MyTime MyTime = TimeValue("4:35:17 PM") If I would like to retrieve the number (in minute unit) and convert into ("4:35:17 PM") format, could you please give me any suggestions on how to do it? For example, there is 30 in cell A1, which unit is minute, and I would like to retrieve 30 value and turn into "00:30:00" string and place it inside TimeValue function. TimeValue(string), which string is equal to "00:30:00" Do you have any suggestions on how to do it? Thanks in advance for any suggestions Eric "Jacob Skaria" wrote: Timevalue () expects a string as below (taken from help) Dim MyTime MyTime = TimeValue("4:35:17 PM") Could you please elaborate what is your requirement here.... -- Jacob (MVP - Excel) "Eric" wrote: When I assign the value within cell B1, the TimeValue function is not working. Do you have any suggestions? Thanks in advance for any suggestions Eric Sheets("Date").Range("B1") = Range("A1") / 1440 Sheets("Date").Range("B1").NumberFormat = "[h]:mm:ss" TimeValue(Sheets("Date").Range("B1").Value) "Jacob Skaria" wrote: Divide by 1440 will convert that to excel time format.... Sub Macro() 'with 30 in cell A1 Range("B1") = Range("A1") / 1440 Range("B1").NumberFormat = "[h]:mm:ss" End Sub -- Jacob (MVP - Excel) "Eric" wrote: I would like to input the number of minutes in cell A1 to set the Time Value, as shown below there is 30 in cell A1 (Unit - minute). Does anyone have any suggestions on how to the value retrieve from cell A1 is under minute unit? Thanks in advance for any suggestions Eric Sheets("Date").Range(A1).Value, TimeValue(Sheets("Date").Range(A1).Value) TimeValue("00:30:00") |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
Bonsour® "Eric" a écrit
Dim MyTime MyTime = TimeValue("4:35:17 PM") what else ??? If I would like to retrieve the number (in minute unit) and convert into ("4:35:17 PM") format, could you please give me any suggestions on how to do it? Sub eric() Dim MyTime As Date MyTime = TimeValue("4:35:17 PM") MsgBox MyTime & vbCrLf & " equal : " & Int(MyTime / TimeValue("0:01")) & " minutes" End Sub For example, there is 30 in cell A1, which unit is minute, and I would like to retrieve 30 value and turn into "00:30:00" string and place it inside in A2 = A1*"0:1" cell format hh:mm:ss HTH @+ |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
Bonsour® "Eric" a écrit
I would like to input the number of minutes in cell A1 to set the Time Value, as shown below there is 30 in cell A1 (Unit - minute). Does anyone have any suggestions on how to the value retrieve from cell A1 is under minute unit? In other words : ;o))) http://www.cpearson.com/excel/DateTimeEntry.htm HTH @+ |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
A1 = 30
A2 = A1*"0:1", which show 00:30:00 in hh:mm:ss format dTime = Now + TimeValue(Sheets("Date").Range("A2").Value) When I run the macro, there is an error on dTime. Do you have any suggestions on how to fix the code? so "00:30:00" can be retrieved for TimeValue function. Thank everyone very much for any suggestions Eric "Modeste" wrote: Bonsour® "Eric" a écrit Dim MyTime MyTime = TimeValue("4:35:17 PM") what else ??? If I would like to retrieve the number (in minute unit) and convert into ("4:35:17 PM") format, could you please give me any suggestions on how to do it? Sub eric() Dim MyTime As Date MyTime = TimeValue("4:35:17 PM") MsgBox MyTime & vbCrLf & " equal : " & Int(MyTime / TimeValue("0:01")) & " minutes" End Sub For example, there is 30 in cell A1, which unit is minute, and I would like to retrieve 30 value and turn into "00:30:00" string and place it inside in A2 = A1*"0:1" cell format hh:mm:ss HTH @+ |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
Bonsour® "Eric" a écrit
A1 = 30 A2 = A1*"0:1", which show 00:30:00 in hh:mm:ss format dTime = Now + TimeValue(Sheets("Date").Range("A2").Value) dTime = Now + Sheets("Date").Range("A2").Value or dTime = Now + TimeValue(Sheets("Date").Range("A2").Text) ;o))) |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
Eric, what is wrong with
Now + timeserial(0,30,0) -- Jacob (MVP - Excel) "Eric" wrote: A1 = 30 A2 = A1*"0:1", which show 00:30:00 in hh:mm:ss format dTime = Now + TimeValue(Sheets("Date").Range("A2").Value) When I run the macro, there is an error on dTime. Do you have any suggestions on how to fix the code? so "00:30:00" can be retrieved for TimeValue function. Thank everyone very much for any suggestions Eric "Modeste" wrote: Bonsour® "Eric" a écrit Dim MyTime MyTime = TimeValue("4:35:17 PM") what else ??? If I would like to retrieve the number (in minute unit) and convert into ("4:35:17 PM") format, could you please give me any suggestions on how to do it? Sub eric() Dim MyTime As Date MyTime = TimeValue("4:35:17 PM") MsgBox MyTime & vbCrLf & " equal : " & Int(MyTime / TimeValue("0:01")) & " minutes" End Sub For example, there is 30 in cell A1, which unit is minute, and I would like to retrieve 30 value and turn into "00:30:00" string and place it inside in A2 = A1*"0:1" cell format hh:mm:ss HTH @+ |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
timeserial(0,variable,0) contains a variable, which is retrieved from cell A1.
Do you have any suggestions? Thank everyone very much for any suggestions Eric "Jacob Skaria" wrote: Eric, what is wrong with Now + timeserial(0,30,0) -- Jacob (MVP - Excel) "Eric" wrote: A1 = 30 A2 = A1*"0:1", which show 00:30:00 in hh:mm:ss format dTime = Now + TimeValue(Sheets("Date").Range("A2").Value) When I run the macro, there is an error on dTime. Do you have any suggestions on how to fix the code? so "00:30:00" can be retrieved for TimeValue function. Thank everyone very much for any suggestions Eric "Modeste" wrote: Bonsour® "Eric" a écrit Dim MyTime MyTime = TimeValue("4:35:17 PM") what else ??? If I would like to retrieve the number (in minute unit) and convert into ("4:35:17 PM") format, could you please give me any suggestions on how to do it? Sub eric() Dim MyTime As Date MyTime = TimeValue("4:35:17 PM") MsgBox MyTime & vbCrLf & " equal : " & Int(MyTime / TimeValue("0:01")) & " minutes" End Sub For example, there is 30 in cell A1, which unit is minute, and I would like to retrieve 30 value and turn into "00:30:00" string and place it inside in A2 = A1*"0:1" cell format hh:mm:ss HTH @+ |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
With Somecell
.numberformat = "hh:mm:ss" .value = timeserial(0, workSheets("Date").Range(A1).Value, 0) end with Eric wrote: timeserial(0,variable,0) contains a variable, which is retrieved from cell A1. Do you have any suggestions? Thank everyone very much for any suggestions Eric "Jacob Skaria" wrote: Eric, what is wrong with Now + timeserial(0,30,0) -- Jacob (MVP - Excel) "Eric" wrote: A1 = 30 A2 = A1*"0:1", which show 00:30:00 in hh:mm:ss format dTime = Now + TimeValue(Sheets("Date").Range("A2").Value) When I run the macro, there is an error on dTime. Do you have any suggestions on how to fix the code? so "00:30:00" can be retrieved for TimeValue function. Thank everyone very much for any suggestions Eric "Modeste" wrote: Bonsour® "Eric" a écrit Dim MyTime MyTime = TimeValue("4:35:17 PM") what else ??? If I would like to retrieve the number (in minute unit) and convert into ("4:35:17 PM") format, could you please give me any suggestions on how to do it? Sub eric() Dim MyTime As Date MyTime = TimeValue("4:35:17 PM") MsgBox MyTime & vbCrLf & " equal : " & Int(MyTime / TimeValue("0:01")) & " minutes" End Sub For example, there is 30 in cell A1, which unit is minute, and I would like to retrieve 30 value and turn into "00:30:00" string and place it inside in A2 = A1*"0:1" cell format hh:mm:ss HTH @+ -- Dave Peterson |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
A1 = 2
A2 = 0:02:00 dTime = Now + TimeValue(Sheets("Date").Range("A2").Value) The macro should be repeated on every two minutes, but it repeated on every minute, I have already quited excel application and reopen this file to ensure the initial setting is erased. Do you have any clue? Thank everyone very much for any suggestions Eric "Modeste" wrote: Bonsour "Eric" a crit A1 = 30 A2 = A1*"0:1", which show 00:30:00 in hh:mm:ss format dTime = Now + TimeValue(Sheets("Date").Range("A2").Value) dTime = Now + Sheets("Date").Range("A2").Value or dTime = Now + TimeValue(Sheets("Date").Range("A2").Text) ;o))) |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
I have changed Value into Text.
A1 = 2 A2 = 0:02:00 dTime = Now + TimeValue(Sheets("Date").Range("A2").Text) The macro should be repeated on every two minutes, but it repeated on every minute, I have already quited excel application and reopen this file to ensure the initial setting is erased. Do you have any clue? Thank everyone very much for any suggestions Eric "Modeste" wrote: Bonsour "Eric" a crit A1 = 30 A2 = A1*"0:1", which show 00:30:00 in hh:mm:ss format dTime = Now + TimeValue(Sheets("Date").Range("A2").Value) dTime = Now + Sheets("Date").Range("A2").Value or dTime = Now + TimeValue(Sheets("Date").Range("A2").Text) ;o))) |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
Try
MsgBox Now + TimeSerial(0, Range("A1").Value, 0) -- Jacob (MVP - Excel) "Eric" wrote: timeserial(0,variable,0) contains a variable, which is retrieved from cell A1. Do you have any suggestions? Thank everyone very much for any suggestions Eric "Jacob Skaria" wrote: Eric, what is wrong with Now + timeserial(0,30,0) -- Jacob (MVP - Excel) "Eric" wrote: A1 = 30 A2 = A1*"0:1", which show 00:30:00 in hh:mm:ss format dTime = Now + TimeValue(Sheets("Date").Range("A2").Value) When I run the macro, there is an error on dTime. Do you have any suggestions on how to fix the code? so "00:30:00" can be retrieved for TimeValue function. Thank everyone very much for any suggestions Eric "Modeste" wrote: Bonsour® "Eric" a écrit Dim MyTime MyTime = TimeValue("4:35:17 PM") what else ??? If I would like to retrieve the number (in minute unit) and convert into ("4:35:17 PM") format, could you please give me any suggestions on how to do it? Sub eric() Dim MyTime As Date MyTime = TimeValue("4:35:17 PM") MsgBox MyTime & vbCrLf & " equal : " & Int(MyTime / TimeValue("0:01")) & " minutes" End Sub For example, there is 30 in cell A1, which unit is minute, and I would like to retrieve 30 value and turn into "00:30:00" string and place it inside in A2 = A1*"0:1" cell format hh:mm:ss HTH @+ |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
The pop up message is 03-Jun-10 11:20:37 PM,
so it cannot retrieve value 2 from cell A1, and return TimeSerial(0,2,0) Do you have any suggestions? Thank everyone very much for any suggestions Eric "Jacob Skaria" wrote: Try MsgBox Now + TimeSerial(0, Range("A1").Value, 0) -- Jacob (MVP - Excel) "Eric" wrote: timeserial(0,variable,0) contains a variable, which is retrieved from cell A1. Do you have any suggestions? Thank everyone very much for any suggestions Eric "Jacob Skaria" wrote: Eric, what is wrong with Now + timeserial(0,30,0) -- Jacob (MVP - Excel) "Eric" wrote: A1 = 30 A2 = A1*"0:1", which show 00:30:00 in hh:mm:ss format dTime = Now + TimeValue(Sheets("Date").Range("A2").Value) When I run the macro, there is an error on dTime. Do you have any suggestions on how to fix the code? so "00:30:00" can be retrieved for TimeValue function. Thank everyone very much for any suggestions Eric "Modeste" wrote: Bonsour® "Eric" a écrit Dim MyTime MyTime = TimeValue("4:35:17 PM") what else ??? If I would like to retrieve the number (in minute unit) and convert into ("4:35:17 PM") format, could you please give me any suggestions on how to do it? Sub eric() Dim MyTime As Date MyTime = TimeValue("4:35:17 PM") MsgBox MyTime & vbCrLf & " equal : " & Int(MyTime / TimeValue("0:01")) & " minutes" End Sub For example, there is 30 in cell A1, which unit is minute, and I would like to retrieve 30 value and turn into "00:30:00" string and place it inside in A2 = A1*"0:1" cell format hh:mm:ss HTH @+ |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
If cell A1 has 2 then the result would be 03-Jun-10 11:20:37 PM PM which is 2
minutes greater than the current time which is 03-Jun-10 11:18:37 PM -- Jacob (MVP - Excel) "Eric" wrote: The pop up message is 03-Jun-10 11:20:37 PM, so it cannot retrieve value 2 from cell A1, and return TimeSerial(0,2,0) Do you have any suggestions? Thank everyone very much for any suggestions Eric "Jacob Skaria" wrote: Try MsgBox Now + TimeSerial(0, Range("A1").Value, 0) -- Jacob (MVP - Excel) "Eric" wrote: timeserial(0,variable,0) contains a variable, which is retrieved from cell A1. Do you have any suggestions? Thank everyone very much for any suggestions Eric "Jacob Skaria" wrote: Eric, what is wrong with Now + timeserial(0,30,0) -- Jacob (MVP - Excel) "Eric" wrote: A1 = 30 A2 = A1*"0:1", which show 00:30:00 in hh:mm:ss format dTime = Now + TimeValue(Sheets("Date").Range("A2").Value) When I run the macro, there is an error on dTime. Do you have any suggestions on how to fix the code? so "00:30:00" can be retrieved for TimeValue function. Thank everyone very much for any suggestions Eric "Modeste" wrote: Bonsour® "Eric" a écrit Dim MyTime MyTime = TimeValue("4:35:17 PM") what else ??? If I would like to retrieve the number (in minute unit) and convert into ("4:35:17 PM") format, could you please give me any suggestions on how to do it? Sub eric() Dim MyTime As Date MyTime = TimeValue("4:35:17 PM") MsgBox MyTime & vbCrLf & " equal : " & Int(MyTime / TimeValue("0:01")) & " minutes" End Sub For example, there is 30 in cell A1, which unit is minute, and I would like to retrieve 30 value and turn into "00:30:00" string and place it inside in A2 = A1*"0:1" cell format hh:mm:ss HTH @+ |
|
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
When I use following coding, my macro is repeated running every second.
A1 = 2 dTime = Now + TimeSerial(0, Sheets("Date").Range("A1").Value, 0) Application.OnTime dTime, "aUpdating" MsgBox Now + TimeSerial(0, Range("A1").Value, 0) did show the time + 2 mins When I use following coding, my macro is repeated running every 2 minutes dTime = Now + TimeSerial(0, 2, 0) Application.OnTime dTime, "aUpdating" Do you have any suggestions on what wrong the retrieved value from cell A1? Thank everyone very much for any suggestions Eric "Jacob Skaria" wrote: If cell A1 has 2 then the result would be 03-Jun-10 11:20:37 PM PM which is 2 minutes greater than the current time which is 03-Jun-10 11:18:37 PM -- Jacob (MVP - Excel) "Eric" wrote: The pop up message is 03-Jun-10 11:20:37 PM, so it cannot retrieve value 2 from cell A1, and return TimeSerial(0,2,0) Do you have any suggestions? Thank everyone very much for any suggestions Eric "Jacob Skaria" wrote: Try MsgBox Now + TimeSerial(0, Range("A1").Value, 0) -- Jacob (MVP - Excel) "Eric" wrote: timeserial(0,variable,0) contains a variable, which is retrieved from cell A1. Do you have any suggestions? Thank everyone very much for any suggestions Eric "Jacob Skaria" wrote: Eric, what is wrong with Now + timeserial(0,30,0) -- Jacob (MVP - Excel) "Eric" wrote: A1 = 30 A2 = A1*"0:1", which show 00:30:00 in hh:mm:ss format dTime = Now + TimeValue(Sheets("Date").Range("A2").Value) When I run the macro, there is an error on dTime. Do you have any suggestions on how to fix the code? so "00:30:00" can be retrieved for TimeValue function. Thank everyone very much for any suggestions Eric "Modeste" wrote: Bonsour® "Eric" a écrit Dim MyTime MyTime = TimeValue("4:35:17 PM") what else ??? If I would like to retrieve the number (in minute unit) and convert into ("4:35:17 PM") format, could you please give me any suggestions on how to do it? Sub eric() Dim MyTime As Date MyTime = TimeValue("4:35:17 PM") MsgBox MyTime & vbCrLf & " equal : " & Int(MyTime / TimeValue("0:01")) & " minutes" End Sub For example, there is 30 in cell A1, which unit is minute, and I would like to retrieve 30 value and turn into "00:30:00" string and place it inside in A2 = A1*"0:1" cell format hh:mm:ss HTH @+ |
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
Make sure you have the value 2 in Sheets("Date") and not in the active sheet.
-- Jacob (MVP - Excel) "Eric" wrote: When I use following coding, my macro is repeated running every second. A1 = 2 dTime = Now + TimeSerial(0, Sheets("Date").Range("A1").Value, 0) Application.OnTime dTime, "aUpdating" MsgBox Now + TimeSerial(0, Range("A1").Value, 0) did show the time + 2 mins When I use following coding, my macro is repeated running every 2 minutes dTime = Now + TimeSerial(0, 2, 0) Application.OnTime dTime, "aUpdating" Do you have any suggestions on what wrong the retrieved value from cell A1? Thank everyone very much for any suggestions Eric "Jacob Skaria" wrote: If cell A1 has 2 then the result would be 03-Jun-10 11:20:37 PM PM which is 2 minutes greater than the current time which is 03-Jun-10 11:18:37 PM -- Jacob (MVP - Excel) "Eric" wrote: The pop up message is 03-Jun-10 11:20:37 PM, so it cannot retrieve value 2 from cell A1, and return TimeSerial(0,2,0) Do you have any suggestions? Thank everyone very much for any suggestions Eric "Jacob Skaria" wrote: Try MsgBox Now + TimeSerial(0, Range("A1").Value, 0) -- Jacob (MVP - Excel) "Eric" wrote: timeserial(0,variable,0) contains a variable, which is retrieved from cell A1. Do you have any suggestions? Thank everyone very much for any suggestions Eric "Jacob Skaria" wrote: Eric, what is wrong with Now + timeserial(0,30,0) -- Jacob (MVP - Excel) "Eric" wrote: A1 = 30 A2 = A1*"0:1", which show 00:30:00 in hh:mm:ss format dTime = Now + TimeValue(Sheets("Date").Range("A2").Value) When I run the macro, there is an error on dTime. Do you have any suggestions on how to fix the code? so "00:30:00" can be retrieved for TimeValue function. Thank everyone very much for any suggestions Eric "Modeste" wrote: Bonsour® "Eric" a écrit Dim MyTime MyTime = TimeValue("4:35:17 PM") what else ??? If I would like to retrieve the number (in minute unit) and convert into ("4:35:17 PM") format, could you please give me any suggestions on how to do it? Sub eric() Dim MyTime As Date MyTime = TimeValue("4:35:17 PM") MsgBox MyTime & vbCrLf & " equal : " & Int(MyTime / TimeValue("0:01")) & " minutes" End Sub For example, there is 30 in cell A1, which unit is minute, and I would like to retrieve 30 value and turn into "00:30:00" string and place it inside in A2 = A1*"0:1" cell format hh:mm:ss HTH @+ |
|
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to assign unit for TimeValue?
It works now, the value 2 in sheets("Plan").
Thank everyone very very much Eric "Jacob Skaria" wrote: Make sure you have the value 2 in Sheets("Date") and not in the active sheet. -- Jacob (MVP - Excel) "Eric" wrote: When I use following coding, my macro is repeated running every second. A1 = 2 dTime = Now + TimeSerial(0, Sheets("Date").Range("A1").Value, 0) Application.OnTime dTime, "aUpdating" MsgBox Now + TimeSerial(0, Range("A1").Value, 0) did show the time + 2 mins When I use following coding, my macro is repeated running every 2 minutes dTime = Now + TimeSerial(0, 2, 0) Application.OnTime dTime, "aUpdating" Do you have any suggestions on what wrong the retrieved value from cell A1? Thank everyone very much for any suggestions Eric "Jacob Skaria" wrote: If cell A1 has 2 then the result would be 03-Jun-10 11:20:37 PM PM which is 2 minutes greater than the current time which is 03-Jun-10 11:18:37 PM -- Jacob (MVP - Excel) "Eric" wrote: The pop up message is 03-Jun-10 11:20:37 PM, so it cannot retrieve value 2 from cell A1, and return TimeSerial(0,2,0) Do you have any suggestions? Thank everyone very much for any suggestions Eric "Jacob Skaria" wrote: Try MsgBox Now + TimeSerial(0, Range("A1").Value, 0) -- Jacob (MVP - Excel) "Eric" wrote: timeserial(0,variable,0) contains a variable, which is retrieved from cell A1. Do you have any suggestions? Thank everyone very much for any suggestions Eric "Jacob Skaria" wrote: Eric, what is wrong with Now + timeserial(0,30,0) -- Jacob (MVP - Excel) "Eric" wrote: A1 = 30 A2 = A1*"0:1", which show 00:30:00 in hh:mm:ss format dTime = Now + TimeValue(Sheets("Date").Range("A2").Value) When I run the macro, there is an error on dTime. Do you have any suggestions on how to fix the code? so "00:30:00" can be retrieved for TimeValue function. Thank everyone very much for any suggestions Eric "Modeste" wrote: Bonsour® "Eric" a écrit Dim MyTime MyTime = TimeValue("4:35:17 PM") what else ??? If I would like to retrieve the number (in minute unit) and convert into ("4:35:17 PM") format, could you please give me any suggestions on how to do it? Sub eric() Dim MyTime As Date MyTime = TimeValue("4:35:17 PM") MsgBox MyTime & vbCrLf & " equal : " & Int(MyTime / TimeValue("0:01")) & " minutes" End Sub For example, there is 30 in cell A1, which unit is minute, and I would like to retrieve 30 value and turn into "00:30:00" string and place it inside in A2 = A1*"0:1" cell format hh:mm:ss HTH @+ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy by timevalue to last row | Excel Programming | |||
| TimeValue formula | Excel Worksheet Functions | |||
| iserror(Timevalue()) | Excel Programming | |||
| OnTime / TimeValue | Excel Programming | |||
| DATEVALUE and TIMEVALUE | Excel Programming | |||
Linear Mode
