convert a decimal into time

[kgkev]

I'm trying to create a sort of count down timer showing Days, Hours & minutes.


A1 = now
A2 = 27/03/09 15:30
A3 = INT(A2-A1) This gives the total number of days
A4 = =B1-A1-C1 - This gives the remainder of the days (0.22 at the moment)

Now I'm not sure how to covert 0.22 into hours & minutes.

I though 0.22*60 but that is 13 but it should be less than 6

 

[Robert E Lee]

Try multiplying by 24 rather than 60

Robert

 

[kgkev]

Maybe I should have posted on a basic maths forum - Cheers.

 

[GTO]

Greetings,

Not much at formulas, I think this might work as well.

A1: 03/04/2009 03:13:51
A2: 03/27/2009 15:30:00
A3: =INT(A2-A1)
A4: =TEXT((A2-A1)-A3,"HH:MM")

Mark

 

[kgkev]

I did it like this

Excel Workbook
	A	B	C	D	E	F
1	Run Countdown
2	TODAY	LEAVING	DAYS	HOURS	MINUTES	SECOND
3	04/03/2009 10:45	27/03/2009 15:30	23	4	44	48
Sheet1

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
     
    If Target.Row = 1 And Target.Column = 1 Then
        Select Case Target.Range("A1")
        Case "": Target.Range("A1") = "Run Countdown"
        Case "Run Countdown": Target.Range("A1") = "Pause Countdown"
        Case "Pause Countdown": Target.Range("A1") = "Run Countdown"
        Case Else: Target.Range("A1") = ""
        End Select
        Cancel = True
    End If
     
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 1 And Target.Column = 1 Then
        If Target.Range("A1") = "Run Countdown" Then Call AutoRecalcWorkbook
    End If
End Sub

Sub AutoRecalcWorkbook()
     
     ThisWorkbook.Worksheets("Sheet1").Calculate
    If ThisWorkbook.Worksheets("Sheet1").Range("A1") <> "Run Countdown" Then
        Exit Sub
    End If
    Application.OnTime Now() + TimeValue("00:00:01"), "AutoRecalcWorkbook"
     
End Sub

Autoupdates - With the ability to pause by right clicking A1