# convert a decimal into time

#### kgkev

##### Well-known Member
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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Robert E Lee

##### Active Member
Try multiplying by 24 rather than 60

Robert

#### kgkev

##### Well-known Member
Maybe I should have posted on a basic maths forum - Cheers.

#### GTO

##### MrExcel MVP
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

##### Well-known Member
I did it like this

Excel Workbook
ABCDEF
1Run Countdown
2TODAYLEAVINGDAYSHOURSMINUTESSECOND
304/03/2009 10:4527/03/2009 15:302344448
Sheet1

Code:
``````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``````

Code:
``````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

Replies
1
Views
254
Replies
3
Views
423
Replies
7
Views
633
Replies
4
Views
187
Replies
4
Views
366

1,195,936
Messages
6,012,394
Members
441,695
Latest member
MickRobertson

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back