48 hour timer

  • Thread starter Thread starter Jason Lindner
  • Start date Start date
J

Jason Lindner

I have tried several different options that I have found online, but still
can't quite get Excel to do what I need. I am looking for a way to count
down 48 hours from from when a date is entered into a specific cell. Anyone
have any ideas. Thanks in advance for your help.
 
Hi Jason

Unclear what you want and how it's supposed to work. But rightclick sheet
tab, go "view code", paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dt As Date
If Target.Address(False, False, xlA1) = "A1" Then
Dt = Now + 2
Range("B1").FormulaR1C1 = _
"=DATE(" & Year(Dt) & "," & _
Month(Dt) & "," & _
Day(Dt) & ")+TIME(" & _
Hour(Dt) & "," & _
Minute(Dt) & "," & _
Second(Dt) & ")-NOW()"
Range("B1").NumberFormat = "[hh]:mm:ss"
End If
End Sub

Now it enters a new countdown formula in B1 at each new A1 entry, and it
changes on each recalculation (entry in other cell or F9).

If you however want this to tick every second, it requires a macro loop that
introduces unwanted effects.

It this works, click the "Yo da man" button.

HTH. Best wishes Harald
 
This is very close to what I need, thank you. To further clarify, I have a
whole list of projects that are in rows. I need to have a timer for each
project. Is there a way to do this without having to hard set the cells, or
create separate code for each row?

Thanks.

Harald Staff said:
Hi Jason

Unclear what you want and how it's supposed to work. But rightclick sheet
tab, go "view code", paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dt As Date
If Target.Address(False, False, xlA1) = "A1" Then
Dt = Now + 2
Range("B1").FormulaR1C1 = _
"=DATE(" & Year(Dt) & "," & _
Month(Dt) & "," & _
Day(Dt) & ")+TIME(" & _
Hour(Dt) & "," & _
Minute(Dt) & "," & _
Second(Dt) & ")-NOW()"
Range("B1").NumberFormat = "[hh]:mm:ss"
End If
End Sub

Now it enters a new countdown formula in B1 at each new A1 entry, and it
changes on each recalculation (entry in other cell or F9).

If you however want this to tick every second, it requires a macro loop that
introduces unwanted effects.

It this works, click the "Yo da man" button.

HTH. Best wishes Harald

Jason Lindner said:
I have tried several different options that I have found online, but still
can't quite get Excel to do what I need. I am looking for a way to count
down 48 hours from from when a date is entered into a specific cell.
Anyone
have any ideas. Thanks in advance for your help.
Click to expand...
Click to expand...
 
For further clarification, here is an example of what I am trying to do:

Project A 1/21/09
Project B 1/23/09

I need a timer that will count down 48 hours from the time I entered the
date of 1/21/09 or 1/23/09. I am trying to set up a type of alarm that
another task must be completed within 48 hours of that date being entered in
column AL.

Thanks so much.

Jason Lindner said:
This is very close to what I need, thank you. To further clarify, I have a
whole list of projects that are in rows. I need to have a timer for each
project. Is there a way to do this without having to hard set the cells, or
create separate code for each row?

Thanks.

Harald Staff said:
Hi Jason

Unclear what you want and how it's supposed to work. But rightclick sheet
tab, go "view code", paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dt As Date
If Target.Address(False, False, xlA1) = "A1" Then
Dt = Now + 2
Range("B1").FormulaR1C1 = _
"=DATE(" & Year(Dt) & "," & _
Month(Dt) & "," & _
Day(Dt) & ")+TIME(" & _
Hour(Dt) & "," & _
Minute(Dt) & "," & _
Second(Dt) & ")-NOW()"
Range("B1").NumberFormat = "[hh]:mm:ss"
End If
End Sub

Now it enters a new countdown formula in B1 at each new A1 entry, and it
changes on each recalculation (entry in other cell or F9).

If you however want this to tick every second, it requires a macro loop that
introduces unwanted effects.

It this works, click the "Yo da man" button.

HTH. Best wishes Harald

Jason Lindner said:
I have tried several different options that I have found online, but still
can't quite get Excel to do what I need. I am looking for a way to count
down 48 hours from from when a date is entered into a specific cell.
Anyone
have any ideas. Thanks in advance for your help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top