Prev: Formulae needed
Next: Hyperlink does not show full address
From: Google Rocks Google on 8 Feb 2010 09:26
Users enter a time that needs to be converted to quarters of an hour. For 8
to 22 minutes past the hour = x:15, for 23-37minutes past the hour = x:30
For 38 to 52 minutes past the hour =x:45 and For 53 minutes before the hour
to 7minutes past the hour = x:00. Many may ask Why? Its the way its always
been done by hand. Yikes! Thank you
From: "Bernie Deitrick" deitbe on 8 Feb 2010 09:42
Copy the code below, right-click the sheet tab, select "View Code" and paste
the code into the window that appears.

Change the A2:B100 to the range address where you want to enter times.


HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A2:B100")) Is Nothing Then Exit Sub

Application.EnableEvents = False
Target.Value = Application.Round(Target.Value * 96, 0) / 96
Application.EnableEvents = True
End Sub





"Google Rocks" <Google Rocks(a)discussions.microsoft.com> wrote in message
news:3619B504-E221-4140-9D46-1A40CAC4A29B(a)microsoft.com...
> Users enter a time that needs to be converted to quarters of an hour. For
> 8
> to 22 minutes past the hour = x:15, for 23-37minutes past the hour = x:30
> For 38 to 52 minutes past the hour =x:45 and For 53 minutes before the
> hour
> to 7minutes past the hour = x:00. Many may ask Why? Its the way its
> always
> been done by hand. Yikes! Thank you


From: Mike H on 8 Feb 2010 09:56
Hi,

With your time in A1 use

=ROUND(A1*96,0)/96
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Google Rocks" wrote:

> Users enter a time that needs to be converted to quarters of an hour. For 8
> to 22 minutes past the hour = x:15, for 23-37minutes past the hour = x:30
> For 38 to 52 minutes past the hour =x:45 and For 53 minutes before the hour
> to 7minutes past the hour = x:00. Many may ask Why? Its the way its always
> been done by hand. Yikes! Thank you
 | 
Pages: 1
Prev: Formulae needed
Next: Hyperlink does not show full address