Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,651
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can I add time that I entered in only minutes and seconds greg Excel Discussion (Misc queries) 3 October 24th 07 08:30 PM
Show time stamp when a value is entered TVCCBJB Excel Discussion (Misc queries) 4 September 8th 06 09:29 PM
Time Card Show Up Time Lewis Excel Worksheet Functions 2 November 14th 05 08:39 PM
Show timesheet time in and out in regular time versus military tim John Excel Worksheet Functions 1 November 11th 05 06:14 AM
I need a time sheet template that verifies time entered against sy Bob Powell Excel Discussion (Misc queries) 1 April 19th 05 03:11 PM


All times are GMT +1. The time now is 05:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"