Time conversion

[ByteMe]

Hi there,

Is it possible to convert numbers such as '7.5' (7 hours & 30 mins) to a correct time of 07:30 & then convert it to a text format of 00730.

Basically cell A1 has the value of 7.5 & i need cell B1 to show 00730

I'm really struggling with this one so any help would be greatly appreciated!

Kind regards

 

[Andrew Poulsom]

This:

=A1/24

formatted as dhhmm should do it.

 

[starryeyed]

You could use:

="00"&TEXT(A1/24, "hmm")

formatted as text, not number.

 

[lenze]

For a completely different approach

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    Application.EnableEvents = False
    Target.Offset(0, 1).Value = Evaluate("=dollarfr(" & Target & ",60)")
    Application.EnableEvents = True
End Sub

Custom format column B as
## "hrs".00 "mins."

Analysis Toolpak required. Also, Dollarde can be used to go the other way
Reference http://www.mrexcel.com/board2/viewtopic.php?t=174642

lenze