How to assign unit for TimeValue?

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

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")
 
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
 
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 said:
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 said:
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 said:
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")
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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")
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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)


:

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")
Click to expand...
Click to expand...
Click to expand...
 
Try VBA()

Timeserial(0,30,0)

--
Jacob (MVP - Excel)


Eric said:
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 said:
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 said:
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)

:

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)


:

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")
Click to expand...
Click to expand...
Click to expand...
 
Bonsour® "Eric" a écrit
Dim MyTime
MyTime = TimeValue("4:35:17 PM")
Click to expand...

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?
Click to expand...

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
Click to expand...

in A2 = A1*"0:1"
cell format hh:mm:ss

HTH
@+
 
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
 
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)
Click to expand...

dTime = Now + Sheets("Date").Range("A2").Value
or
dTime = Now + TimeValue(Sheets("Date").Range("A2").Text)

;o)))
 
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
 
With Somecell
.numberformat = "hh:mm:ss"
.value = timeserial(0, workSheets("Date").Range(A1).Value, 0)
end with
 
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
 
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
 
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
 
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
 
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
 
Back
Top