Expert: Richard Rost - 7/8/2009

Question
I have a question regarding some string handling.
My basic problem: I have to calculate the exact time by given minutes after midnight. Ex. 456 = 7.36 a.m.
Fist i divide 456 by 60, that gives me 7.6, so I save the hours 7, then I multiply the .6 with 60 and that will give me the minutes. I save those two results (7 and 36) in two different strings: zhal1S and minS
after that I want to put those two separate strings together, so that I get a result string (resS) that looks like: 7:36
and then I wand to write that result in a specific cell on my excel sheet.
My code so far:

   Dim zahl As Double
   Dim zahl1 As Double
   Dim min As Double
   Dim zhal1S As String
   Dim minS As String
   Dim res As String
   
   zahl = 0
   Range("D7").Select
   zahl = Selection.Value
   zahl = zahl / 60
   zahl1 = zahl
   zahl1S = 0
   minS = 0
   
   For i = 1 To 24
       If zahl > 1 Then
           zahl = zahl - 1
       Else
           min = zahl * 60
           i = 30
       End If
   Next i
   zahl1 = zahl1 - zahl
   zahl1S = zahl1
   minS = min
   Ark.Cells(7, 5) = zahl1S
   Ark.Cells(7, 6) = minS
   Ark.Cells(7, 7) = resS

you should know, its my second day with VBA, so please excuse my horrible style.

Answer
If you're working with Excel, why not just use the HOUR and MINUTE functions in a cell?

=HOUR(A1)*60+MINUTE(A1)

You don't need complex VBA code to figure out the number of minutes past midnight a time is.

There are many different techniques in Excel you can use to break apart dates or times into their component parts, add or subtract dates, and figure out how much time is elapsed between two dates.

Read this free tutorial from my web site that explains how to work with adding dates and times in Excel:

http://599cd.com/tips/excel/adding-dates-times/?key=AllExperts

I also have this FREE video tutorial that shows you how to get month names (like "September") from dates:

http://599cd.com/tips/excel/month-names/?key=AllExperts

I spend a LOT of time talking about dates and times in my full Excel tutorials:

http://599cd.com/Excel?key=AllExperts

Let me know if you have any other questions or comments.

Also, be sure to visit my FREE Tips & Tricks Page at http://www.599cd.com/tips?key=AllExperts


Cordially,

Richard Rost
599CD Computer Training
http://www.599cd.com

P.S. Try one of my FREE tutorials at http://www.599cd.com/VB?key=AllExperts

P.P.S. I volunteer my time at AllExperts to help people, and I get a LOT of questions, so I can't take an hour to answer each question. If you need more DETAILED HELP, come to my TechHelp web site at http://www.599cd.com/TechHelp/AllExperts and I'll take as much time as you need to answer your question.

Out of work? Know someone who is? Get a FREE copy of my Computer Job Skills CD at http://www.599cd.com/JobSkills?key=AllExperts