convert decimal input to real time vba code

[aboly8000]

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
.
.
.

 

[steve the fish]

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.

 

[aboly8000]

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)

 

[jasonb75]

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.

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

 

[steve the fish]

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

 

[steve the fish]

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

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

 

[jasonb75]

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.

 

[aboly8000]

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"

 

[aboly8000]

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

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

 

[aboly8000]

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.

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.