Show a time 50 min before entered time

[Guest]

I want to enter a time in cell d2 and I want cell d2 to show 50 minutes
earlier. What formula can I use.
At present when I format the cells as Hrs & mins when I enter 0700 the time
returns as 00:00

Regards jcs

 

[Guest]

Use the following worksheet event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("D2")
If Intersect(Target, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
r.Value = r.Value - 0.03472222222
Application.EnableEvents = True
End Sub

If you enter 7:00 in the cell, is will display 6:10

 

[David Biddulph]

Enter the time as 07:00, not 0700.

You've talked of entering a time in D2, but then showing a different value
in the same cell D2. That's not something which can be done with a simple
reliable formula. You might do it with a circular reference, but that is
not recommended. You'll be better putting 07:00 in D2, and =D2-TIME(0,50,0)
[or =D2-"0:50"] in a separate cell.

 

[Don Guillett]

Right click sheet tab>view code>copy/paste this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$D$2" Then Exit Sub
x = Left(Target, 2) & ":" & Right(Target, 2)
'MsgBox x
Application.EnableEvents = False
Target.Value = TimeValue(x) - (50 / 1440)
Application.EnableEvents = True
End Sub