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
.
.
.  Reply With Quote

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.  Reply With Quote

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)  Reply With Quote

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  Reply With Quote

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.  Reply With Quote

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  Reply With Quote

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.  Reply With Quote

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"  Reply With Quote

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  Reply With Quote

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.  Reply With Quote

User Tag List

Tags for this Thread

convert, decimal, input, time, vba  Posting Permissions

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