# Thread: convert decimal input to real time vba code Thanks:  8 Post #5339093 (1)Post #5339129 (1)Post #5339097 (1)Post #5339135 (1)Post #5339073 (1) Likes:  8 Post #5339093 (1)Post #5339129 (1)Post #5339097 (1)Post #5339135 (1)Post #5339073 (1)

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

3. ## Re: convert decimal input to real time vba code

Originally Posted by 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.
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. ## 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. ## 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.

6. ## 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```

7. ## Re: convert decimal input to real time vba code

Originally Posted by 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.
The request was to convert 3.50 to 3:30, not 3.30 to 3:30.

8. ## Re: convert decimal input to real time vba code

Originally Posted by 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.
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. ## Re: convert decimal input to real time vba code

Originally Posted by steve the fish
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. ## Re: convert decimal input to real time vba code

Originally Posted by 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.
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.