Time conversion

ByteMe · Apr 24, 2006

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 · Apr 24, 2006

This:

=A1/24

formatted as dhhmm should do it.

starryeyed · Apr 24, 2006

You could use:

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

formatted as text, not number.

lenze · Apr 24, 2006

For a completely different approach

Code:

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

Time conversion

ByteMe

Board Regular

Excel Facts

Andrew Poulsom

MrExcel MVP

starryeyed

Board Regular

lenze

Legend

Similar threads

Forum statistics

Share this page

Time conversion

ByteMe

Board Regular

Excel Facts

Andrew Poulsom

MrExcel MVP

starryeyed

Board Regular

lenze

Legend

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock