Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: convert decimal input to real time vba code

  1. #1
    Board Regular
    Join Date
    Sep 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default convert decimal input to real time vba code

    Hi
    I need a vba code that converts a decimal input to real time and does nothing if it's time.
    for example:
    2.5 convert to 2:30
    3.5 convert to 3:30
    and if input is time do notting
    2:30 convert to 2:30
    3:30 convert to 3:30
    .
    .
    .

  2. #2
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,648
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: convert decimal input to real time vba code

    Will you be reusing the cell that has the input? If you input time eg 3:30 excel will format that cell to time. Then you cant just type 3.3 in that same cell as it will be converted to a time (actually a time and date but you wont see the date part). If you arent reusing the cells then its possible.
    Looking for opportunities

  3. #3
    Board Regular
    Join Date
    Sep 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: convert decimal input to real time vba code

    Quote Originally Posted by steve the fish View Post
    Will you be reusing the cell that has the input? If you input time eg 3:30 excel will format that cell to time. Then you cant just type 3.3 in that same cell as it will be converted to a time (actually a time and date but you wont see the date part). If you arent reusing the cells then its possible.
    Yes. I want to do this with a vba code

    For Each cell In isect
    If (Application.IsNumber(cell) = True) Then
    cell = cell / 24
    End If
    Next cell

    I'm using this code, but I want this condition to not run on the time entry (eg 3:30 same 3:30 show)

  4. #4
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,641
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: convert decimal input to real time vba code

    It will only work if you're entering time of day with nothing earlier than 01:00 hrs. For duration or times before 01:00 there is no way of doing what you want.
    Code:
    For Each cell In isect
        If (Application.IsNumber(cell) = True) Then
            If cell.value > 1 then cell = cell / 24
        End If
    Next cell

  5. #5
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,648
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: convert decimal input to real time vba code

    You cant just divide by 24. 3.30 for example is not going to convert to 3:30 if divided by 24.
    Looking for opportunities

  6. #6
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,648
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: convert decimal input to real time vba code

    Try this. It solely based on the number being typed is greater than 1.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rng As Range, c As Range
    
    Set rng = Intersect(Target, Columns("A"))
    
    If Not rng Is Nothing Then
        If rng.Rows.Count <> Rows.Count Then
            For Each c In rng
                If IsNumeric(c.Value) Then
                    If c.Value > 1 And c.Value <> Int(c.Value) Then
                        c.Value = Int(c.Value) / 24 + Evaluate("MOD(" & c.Value & ",1)") / 0.6 / 24
                    End If
                End If
            Next
        End If
    End If
                
    End Sub
    Looking for opportunities

  7. #7
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,641
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: convert decimal input to real time vba code

    Quote Originally Posted by steve the fish View Post
    You cant just divide by 24. 3.30 for example is not going to convert to 3:30 if divided by 24.
    The request was to convert 3.50 to 3:30, not 3.30 to 3:30.

  8. #8
    Board Regular
    Join Date
    Sep 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: convert decimal input to real time vba code

    Quote Originally Posted by steve the fish View Post
    You cant just divide by 24. 3.30 for example is not going to convert to 3:30 if divided by 24.
    with this cod 3.50 entry Becomes to 3:30
    but problem is 3:30 entry Becomes to 00:08

    For Each cell In isect
    If (Application.IsNumber(cell) = True) Then
    cell = cell / 24
    End If
    Next cell
    Worksheets("Sheet1").Columns("A").NumberFormat = "[hh]:mm"

  9. #9
    Board Regular
    Join Date
    Sep 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: convert decimal input to real time vba code

    Quote Originally Posted by steve the fish View Post
    Try this. It solely based on the number being typed is greater than 1.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rng As Range, c As Range
    
    Set rng = Intersect(Target, Columns("A"))
    
    If Not rng Is Nothing Then
        If rng.Rows.Count <> Rows.Count Then
            For Each c In rng
                If IsNumeric(c.Value) Then
                    If c.Value > 1 And c.Value <> Int(c.Value) Then
                        c.Value = Int(c.Value) / 24 + Evaluate("MOD(" & c.Value & ",1)") / 0.6 / 24
                    End If
                End If
            Next
        End If
    End If
                
    End Sub
    i need for example 1.5 convert to 1:30 , 1 convert to 1:00

  10. #10
    Board Regular
    Join Date
    Sep 2019
    Posts
    55
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: convert decimal input to real time vba code

    Quote Originally Posted by jasonb75 View Post
    It will only work if you're entering time of day with nothing earlier than 01:00 hrs. For duration or times before 01:00 there is no way of doing what you want.
    Code:
    For Each cell In isect
        If (Application.IsNumber(cell) = True) Then
            If cell.value > 1 then cell = cell / 24
        End If
    Next cell
    Thankful . But for all time there must be a way.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •