• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Please tell me how to convert minutes to decimal

Abhijeet
1) Use correct format with Your column A; those are texts, not times
2) after that one solution would be:
Code: 
Sub h_2()
    For y = 2 To 5
        Cells(y, 2) = Cells(y, 1) * 24
    Next y
End Sub
 
This is not work and tell me data is not fix multiple columns as well this type of values so i want to replace in same cell
 
Abhijeet
As I already wrote
1) Use correct format with Your column A
... means, why You cannot get those values as time?
if really You need to make this difficult way then..
2) You can find same kind of 'challenge' solved many times from Chandoo.org > Search.
3) or You will solve hour, minute and second parts from those texts and after that You'll have 'time'. Next step You'll already know.
 
Hi !

As a child can achieve this just using a worksheet formula !
Just divide the part after ":" by 60 and add part before ":" …
If really a code is needed, use Split and apply same logic !

As vletm way works directly within a worksheet formula
like =24*A2 with a General cell format,
so even by code formula is the easy way avoiding a slow loop
just with 3 (expert) to 5 (advanced or beginner) codelines …
​
 
i have no option to change the format i received file from client so i can not tell to them i can to change any columns in my file so i want to replace these values in same cell
 

As I never wrote to change initial format and
any way from my previous post works with text format !
​
 
And as I yet wrote :
If really a code is needed, use Split and apply same logic !
Click to expand...
So a beginner could use a useless loop with Split VBA text function
just with 5 codelines …

But like any very beginner you can use an additional column
to enter formula like =24*A2, change its cell format to General
then copy it down then select entire converted cells, Copy then
Paste Special as Values on initial column, delete additional column,
all in less of ten seconds of your life ! :rolleyes:

If you really need a code, redo these actions just after activating
Macro recorder : you will have your own code base ! (Noob way)
In the same ten seconds …

Advanced way is to not use any loop but just Formula property (5 lines).
And an expert directly evaluates result to initial column (3 codelines) …
​
 
Abhijeet
You should learn to read!
That previous code works as I wrote AFTER those were times.
i have no option to change the format i received file from client so i can not tell to them i can to change any columns in my file so i want to replace these values in same cell
This is not work and tell me data is not fix multiple columns as well this type of values so i want to replace in same cellHave You even tried to ask ... or Your company just want to do extra work?

Next code will work with text format too.
Because You cannot/won't and those cells can be anywhere ...
1) You have to select RANGE (ONE COLUMN), cells,
which You would like to change
2) You run next code.
3) Repeat steps 1 and 2 as many times as need
Code: 
Sub DoSomeExtraWork()
    On Error Resume Next
    Dim Rng As Range
    Set WorkRng = Application.Selection
    If WorkRng.Columns.Count > 1 Then
        MsgBox "Only ONE Column!"
        Exit Sub
    End If
    For Each Rng In WorkRng.Rows
        Err.Clear
        chk_a = Rng.Value
        x = WorksheetFunction.Find(":", chk_a, 1)
        If Err.Number = 0 Then Rng.Value = Val(Left(chk_a, x - 1)) + Val(Mid(chk_a, x + 1, 999)) / 60
    Next Rng
End Sub
 
According to initial attachment,
according to the child logic exposed in post #6,
my 2 cents beginner 5 codelines demonstration
(as using a loop is the worst way with big data !) :​
Code: 
Sub DemoBeginner()
         Dim Rg As Range, V
    For Each Rg In Range("A2", Cells(1).End(xlDown))
        V = Split(Rg.Value, ":")
        If UBound(V) > 0 Then Rg.Value = V(0) + V(1) / 60
    Next
End Sub
Do you like it ? So thanks to click on bottom right Like !
​
 
You must log in or register to reply here.
Back
Top