How to assign unit for TimeValue?

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")
 
J

Jacob Skaria

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
 
J

Jacob Skaria

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...
 
E

Eric

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...
 
E

Eric

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...
 
J

Jacob Skaria

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...
 
M

Modeste

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
@+
 
E

Eric

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
 
M

Modeste

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)))
 
E

Eric

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
 
D

Dave Peterson

With Somecell
.numberformat = "hh:mm:ss"
.value = timeserial(0, workSheets("Date").Range(A1).Value, 0)
end with
 
E

Eric

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
 
E

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
 
E

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
 
J

Jacob Skaria

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
 
E

Eric

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

OnTime Change_Event macro AND copy to sheet2 macro trouble 7
Excel VBA to go to specific sheet & cell in the current workbook not working. 2
Code arrangement 3
Simple Variable to someone 5
Freeze the value of one cell for secs while worksheet auto updates 4
Copy Array to Array, how to and should I? 13
To choose several sheets for synchronizing 3
Convert A1 value to a sheet name and copy to it 2

Top