Prev: select and paste data based on creteria on previous cells
Next: A Sub in another Sub...
From: Jacob Skaria on 3 Jun 2010 04:48
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")
> > > > >
> > > > >
From: Modeste on 3 Jun 2010 05:37
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
@+

From: Modeste on 3 Jun 2010 05:45
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
@+

From: Eric on 3 Jun 2010 09:08
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
> @+
>
From: Modeste on 3 Jun 2010 10:03
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)))


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: select and paste data based on creteria on previous cells
Next: A Sub in another Sub...