Cells(I, 15).Value = "hour=" & """" & Format(Hour(Cells(I, 6).Value),"00")
&":"
& Format(Minute(Cells(I,6)), "00") & """"
Regards
Sam
On Fri, Oct 28, 2011 at 6:20 AM, Eduardo Cereja <cerejaedua...@gmail.com>wrote:
> Dear Paul, thanks a lot for the expalnation. Really good.
> But using the code that you wrote the result is:
>
> hour="9" and not equal hour="09:06"
>
> I tried this way:
>
> Cells(I, 15).Value = "hour=" & """" & Hour(Cells(I, 6).Value) &":"
> &Minute(Cells(I,6))& """"
>
> but results in: hour="9:6" inspite of hour="09:06"
>
> Could you help me again?
>
> On 27 out, 10:59, Paul Schreiner <schreiner_p...@att.net> wrote:
> > First of all, you need to understand that to Excel, "time" is simply a
> fraction
> > of a day.
> >
> > So, 9:00am is really 9/24 of a day, or .375
> >
> > 9:06:24 is 0.379444444444444 of a day...
> >
> > when you SEE the cell as a time: 9:06 AM, it is a display format.
> > You're DISPLAYING the number as a time.
> >
> > you could enter ANY number:
> > 40515.225997274
> > And the Integer portion is the number of days since 1/1/1900
> > 40515 = December 3, 2010
> > and the decimal portion (.225997274)
> > is the time:
> > 5:25:26 AM
> >
> > So, the problem is that cells(i,6) is returning the actual VALUE of the
> cell,
> > not what is displayed.
> >
> > if you want the number represented as an hour, you'll need to convert it
> using
> > hour()
> > as in:
> >
> > Cells(i, 15).Value = "hour=" & """" & hour(Cells(i,6 )) &""""
> >
> > also,
> >
> > the line:
> > Set r = Range("A1:J60")
> > isn't required
> >
> > You could use:
> > Sub xml()
> > Dim I
> > For I = 5 To 60
> > If Cells(I, 1) <> "" Then
> > Cells(I, 15).Value = "hour=" & """" & Hour(Cells(I, 6).Value) &
> """"
> > End If
> > Next I
> > End Sub
> >
> > Paul
> > -----------------------------------------
> > “Do all the good you can,
> > By all the means you can,
> > In all the ways you can,
> > In all the places you can,
> > At all the times you can,
> > To all the people you can,
> > As long as ever you can.” - John Wesley
> > -----------------------------------------
> >
> > ________________________________
> > From: Eduardo Cereja <cerejaedua...@gmail.com>
> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
> > Sent: Thu, October 27, 2011 8:05:22 AM
> > Subject: $$Excel-Macros$$ Time between " "
> >
> > Hi, I´m using the code below. Cells(i,6) has hour data, 09:06 for
> > example. I need to transfer this value to another cell , but between
> > "09:06".
> > The code below result in:
> > hour="0,404861111111111"
> > however i want
> > hour="09:06"
> >
> > Coud anyone help me with this simple problem?
> >
> > Best regards
> >
> > Sub xml()
> > Set r = Range("A1:J60")
> > For i = 5 To 60
> > If r.Cells(i, 1) <> "" Then
> > Cells(i, 15).Value = "hour=" & """" & Cells(i,6 )&""""
> > End If
> > Next i
> >
> > End Sub
> >
> > --
> >
> ---------------------------------------------------------------------------
> -------
> >
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> > 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
> >
> > To post to this group, send email to excel-macros@googlegroups.com
> >
> > <><><><><><><><><><><><><><><><><><><><><><>
> > Like our page on facebook , Just follow below linkhttp://
> www.facebook.com/discussexcel
>
> --
> FORUM RULES (925+ members already BANNED for violation)
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
> not get quick attention or may not be answered.
>
> 2) Don't post a question in the thread of another member.
>
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
>
> 4) Acknowledge the responses you receive, good or bad.
>
> 5) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> NOTE : Don't ever post personal or confidential data in a workbook. Forum
> owners and members are not responsible for any loss.
>
>
> ------------------------------------------------------------------------------------------------------
> To post to this group, send email to excel-macros@googlegroups.com
>
--
Sam Mathai Chacko
--
FORUM RULES (925+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
