Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show a time 50 min before entered time
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show a time 50 min before entered time
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 -- Gary''s Student - gsnu200754 "cristo" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show a time 50 min before entered time
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. -- David Biddulph "cristo" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show a time 50 min before entered time
Right click sheet tabview codecopy/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 -- Don Guillett Microsoft MVP Excel SalesAid Software "cristo" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I add time that I entered in only minutes and seconds | Excel Discussion (Misc queries) | |||
Show time stamp when a value is entered | Excel Discussion (Misc queries) | |||
Time Card Show Up Time | Excel Worksheet Functions | |||
Show timesheet time in and out in regular time versus military tim | Excel Worksheet Functions | |||
I need a time sheet template that verifies time entered against sy | Excel Discussion (Misc queries) |