Time entries

G

gjcase

I have a spreadsheet with time entries on it. Problem is any entries
after 1:00 are assumed by Excel (2002) to be AM. Does anyone know how
to reset the assumption? I'd like the AM/PM assumption to switch at
6:00 vs 1:00.

I know we can just enter 1:00 as 13:00 or as 1 p, but I'd prefer to not
rely on the data entry to get that right.

I know I can use data validation to flag the entry, but again, I'm
looking for the least intrusive method. I would also prefer that we not
have to enter data in one column & process it in another. Any ideas?

---GJC
 
D

David McRitchie

It's bad enough that people work through midnight and you have to modify
formulas because people just record the time and not the date, but now you want
Excel to figure out if you mean AM or PM -- I certainly would not want to touch that.

Data Validation would not require another column. See
http://www.contextures.com/tiptech.html

And I think you meant 0:00 (midnight) instead of 1:00 and you are confusing
the heck out me, and proably anyone who would use or update your worksheet.
More information on Date and TIme
http://www.mvps.org/dmcritchie/excel/datetime.htm
http://www.cpearson.com/excel/datetime.htm
 
G

gjcase

Thanks for the response, Dave, I'll look over the links you sent. Sorr
if I confused you, I was trying to keep it short.

No, I meant 1 PM, not midnight. We have a list of of entries for 1s
shift. Time entries such as 11:48 Excel assumes to be AM. An entr
such as 12:34, Excel assumes to be PM. But 1:01 is AM again, unles
you enter it as 13:01 or 1:01 am. I'm trying to force the entry to b
between 6AM & 6PM without having to make the user enter AM or PM.

Thanks again.

---GJ
 
R

robert111

make each cell that users put a date in a drop down list that goes from
00:00 to 23:59, in 1 minute intervals, then there can be no doubt.
 
G

gjcase

Thanks for the help. I got this to work by modifying some VBA code on
Chip Pearson's page.

---GJC
 
D

David McRitchie

Hi .......,
I see you answered your own question, but since this was ready to go here
is my solution.

Obviously you can't enter 6:00 and have it mean both 6:00 AM and 6:00 PM.

Actually there is really only one line of code in the macro, but since macro
code is being installed, it might as well check for some entry
errors as well, and of course allow you to clear a cell.

Anyone entering a whole number would be entering days
instead of time but it would look like zero, so it will be intercepted and cleared.
Since you can't enter a time greater than 18:00 then the actual test will be
to test for greater than .75 (3/4 of a day).

Your method leaves absolutely no leeway for workking outside of your ranges,
and I think this is going to bite you later on, if not in programming then for
whoever learned that 5:00 was a shortcut for 17:00 when they later end up
having to enter times times outside your range in some other applications..

You will have to chage the C:D in the code to whatever columns are valid for entry.

The following is an Event Macro and is installed as follows:
Right Click on the sheet tab, View Code
Insert the following code

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Columns("C:D"), Target) Is Nothing Then Exit Sub
If IsEmpty(Target) Then Exit Sub
On Error Resume Next
Dim txtHeld
If Not IsNumeric(Target.Value) Then Exit Sub
If Target < 0.25 Then Target = Target + 0.5
If Target > 0.75 Then
txtHeld = Target.Text
Application.EnableEvents = False 'should be part of Change macro
Target.ClearContents
Target.Activate
Application.EnableEvents = True 'should be part of Change macro
MsgBox txtHeld & " is invalid " & Chr(10) _
& "please reenter as time between 6:00 and 18:00" _
& " in cell " & Target.Address(0, 0)
End If
End Sub

If you make a change to the Event Macro and it fails to get invoked then see
http://www.mvps.org/dmcritchie/excel/event.htm#problems
 
Top