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

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

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

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

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

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

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

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

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

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