Number to time conversion

  • Thread starter Thread starter Tom Snyder
  • Start date Start date
T

Tom Snyder

Hi ALL

I have a number 725. I want to convert it to time like this 07:25 AM
I want to be able to add or subtract hours & minuts from this time.
I've tried everything I know and can't get it to work.
If I type 725 into a cell, then format it for time, it returns 0:00
What am I doing wrong .

Thanks for any help you can give me.

Tom Snyder
 
Don, Could I use this to keep excel from reading my data formatted as 5:14
or 65:32 as time? If so, can I apply it to specific columns in a sheet? Can
I force some to always be text and will it maintain it after pasting data
into that page?
Thanks,

Mark


Don Guillett said:
right click on the sheet tab>view code>insert this
modify to suit
====
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
'If Target.Column <> 1 Or Target.Column <> 2 Then Exit Sub
If Target.Address = "$B$1" Then ActiveSheet.Name = Target
Application.EnableEvents = False
'If Target.Column <> 1 Or Target.Column <> 2 Then Exit Sub
If Target.Row > 5 And Target.Column = 2 And Target > 1 Or Target.Row > 5 And
Target.Column = 3 And Target > 1 Then
Target = Format(Left(Target.Value, 2) & ":" & Right(Target.Value, 2),
"hh:mm")
'End If
'Application.EnableEvents = True
Cells(Target.Row, 4) = Format(Cells(Target.Row, 3) - Cells(Target.Row, 2),
"hh:mm")
Cells(Target.Row, 5) = (Cells(Target.Row, 4) * 1440) * ([d4] / 60)
End If
Application.EnableEvents = True
End Sub




Tom Snyder said:
Hi ALL

I have a number 725. I want to convert it to time like this 07:25 AM
I want to be able to add or subtract hours & minuts from this time.
I've tried everything I know and can't get it to work.
If I type 725 into a cell, then format it for time, it returns 0:00
What am I doing wrong .

Thanks for any help you can give me.

Tom Snyder
Click to expand...
Click to expand...
 
I don't understand. I thought the idea was to get it to be formatted as
time?
if not, I guess you can format as text.

telefono said:
Don, Could I use this to keep excel from reading my data formatted as 5:14
or 65:32 as time? If so, can I apply it to specific columns in a sheet? Can
I force some to always be text and will it maintain it after pasting data
into that page?
Thanks,

Mark


Don Guillett said:
right click on the sheet tab>view code>insert this
modify to suit
====
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
'If Target.Column <> 1 Or Target.Column <> 2 Then Exit Sub
If Target.Address = "$B$1" Then ActiveSheet.Name = Target
Application.EnableEvents = False
'If Target.Column <> 1 Or Target.Column <> 2 Then Exit Sub
If Target.Row > 5 And Target.Column = 2 And Target > 1 Or Target.Row > 5 And
Target.Column = 3 And Target > 1 Then
Target = Format(Left(Target.Value, 2) & ":" & Right(Target.Value, 2),
"hh:mm")
'End If
'Application.EnableEvents = True
Cells(Target.Row, 4) = Format(Cells(Target.Row, 3) - Cells(Target.Row, 2),
"hh:mm")
Cells(Target.Row, 5) = (Cells(Target.Row, 4) * 1440) * ([d4] / 60)
End If
Application.EnableEvents = True
End Sub




Tom Snyder said:
Hi ALL

I have a number 725. I want to convert it to time like this 07:25 AM
I want to be able to add or subtract hours & minuts from this time.
I've tried everything I know and can't get it to work.
If I type 725 into a cell, then format it for time, it returns 0:00
What am I doing wrong .

Thanks for any help you can give me.

Tom Snyder
Click to expand...
Click to expand...
Click to expand...
 
Back
Top