software

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 amp; process it in another. Any ideas?

---GJC--
gjcase
------------------------------------------------------------------------
gjcase's Profile: www.excelforum.com/member.php...oamp;userid=26061
View this thread: www.excelforum.com/showthread...hreadid=528671It'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
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
www.mvps.org/dmcritchie/excel/datetime.htm
www.cpearson.com/excel/datetime.htm

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;gjcasequot; gt; wrote in message
...
gt;
gt; I have a spreadsheet with time entries on it. Problem is any entries
gt; after 1:00 are assumed by Excel (2002) to be AM. Does anyone know how
gt; to reset the assumption? I'd like the AM/PM assumption to switch at
gt; 6:00 vs 1:00.
gt;
gt; I know we can just enter 1:00 as 13:00 or as 1 p, but I'd prefer to not
gt; rely on the data entry to get that right.
gt;
gt; I know I can use data validation to flag the entry, but again, I'm
gt; looking for the least intrusive method. I would also prefer that we not
gt; have to enter data in one column amp; process it in another. Any ideas?
gt;
gt; ---GJC
gt;
gt;
gt; --
gt; gjcase
gt; ------------------------------------------------------------------------
gt; gjcase's Profile: www.excelforum.com/member.php...oamp;userid=26061
gt; View this thread: www.excelforum.com/showthread...hreadid=528671
gt;

Thanks for the response, Dave, I'll look over the links you sent. Sorry
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 1st
shift. Time entries such as 11:48 Excel assumes to be AM. An entry
such as 12:34, Excel assumes to be PM. But 1:01 is AM again, unless
you enter it as 13:01 or 1:01 am. I'm trying to force the entry to be
between 6AM amp; 6PM without having to make the user enter AM or PM.

Thanks again.

---GJC--
gjcase
------------------------------------------------------------------------
gjcase's Profile: www.excelforum.com/member.php...oamp;userid=26061
View this thread: www.excelforum.com/showthread...hreadid=528671
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.--
robert111
------------------------------------------------------------------------
robert111's Profile: www.excelforum.com/member.php...oamp;userid=31996
View this thread: www.excelforum.com/showthread...hreadid=528671
Thanks for the help. I got this to work by modifying some VBA code on
Chip Pearson's page.

---GJC--
gjcase
------------------------------------------------------------------------
gjcase's Profile: www.excelforum.com/member.php...oamp;userid=26061
View this thread: www.excelforum.com/showthread...hreadid=528671Hi .......,
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 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(quot;Cquot;), 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 lt; 0.25 Then Target = Target 0.5
If Target gt; 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 amp; quot; is invalid quot; amp; Chr(10) _
amp; quot;please reenter as time between 6:00 and 18:00quot; _
amp; quot; in cell quot; amp; 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
www.mvps.org/dmcritchie/excel/event.htm#problems
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: www.mvps.org/dmcritchie/excel/excel.htm
Search Page: www.mvps.org/dmcritchie/excel/search.htm

quot;gjcasequot; gt; wrote in message
...
gt;
gt; Thanks for the response, Dave, I'll look over the links you sent. Sorry
gt; if I confused you, I was trying to keep it short.
gt;
gt; No, I meant 1 PM, not midnight. We have a list of of entries for 1st
gt; shift. Time entries such as 11:48 Excel assumes to be AM. An entry
gt; such as 12:34, Excel assumes to be PM. But 1:01 is AM again, unless
gt; you enter it as 13:01 or 1:01 am. I'm trying to force the entry to be
gt; between 6AM amp; 6PM without having to make the user enter AM or PM.
gt;
gt; Thanks again.
gt;
gt; ---GJC
gt;
gt;
gt; --
gt; gjcase
gt; ------------------------------------------------------------------------
gt; gjcase's Profile: www.excelforum.com/member.php...oamp;userid=26061
gt; View this thread: www.excelforum.com/showthread...hreadid=528671
gt;
Thanks, I appreciate the help.

---GJC--
gjcase
------------------------------------------------------------------------
gjcase's Profile: www.excelforum.com/member.php...oamp;userid=26061
View this thread: www.excelforum.com/showthread...hreadid=528671