Excel Time Problem

[Gilbertjoe]

I have a cell that is formatted as time of H:MM AM/PM with a data validation of Time and the validation set from 12:00 AM to 11:59 PM so the user has to enter the time exactly that way. The problem is that if the user enters the time without the AM or PM like 2:00 and not 2:00 PM the cell allows this and defaults to AM even with data validation.
How to I set the cell so the user is required to enter the AM or PM as well and not have excel auto enter AM or PM.

 

[SkipVought]

Hi,

Well 2:00 AM IS between 12:00 AM and 11:59 PM

???

What am I missing?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

I might have missed you point, but if you want the user to enter time without the AM/PM indicator then use military time
14:00 is 2:00 PM



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Gilbertjoe]

Let me clarify a bit more.
Ok. As of the way its set up now (as I've described above:
This is what I DO NOT want to happen:
If the user enters 2:00 (and forgets to put in AM or PM) the cell will accept it but Excel places an AM at the end (I guees as a default) since the user didn't specify AM or PM.
What I WOULD LIKE to happen:
if the user puts 2:00 in the cell (and forgets to put in AM or PM) I Do not want Excel to (I guess default) and put in AM.

 

[jdhilljr]

It can be done if the cells where they enter the time are formatted as text. Then you would qualify with RIGHT 2 ="AM" OR "PM". The only other difference is when you do the math for the times you would need to multiply the sum *24 to get it in the format you want.
=IF(RIGHT(F3,2)="AM",(F3-E3)*24,"AM/PM&quot
Jim

 

[SkipVought]

That would be AWFULLY annoying -- When I enter 2:00, I mean 2:00 AM NOT 2:00 PM

What do you want? A mind reading program?

2:00 is 2:00 AM, and

14:00 is 2:00 PM

Wish all you want! Them's the facts!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Gilbertjoe]

They don't have mind reading programs yet? I'll set up a meeting with Bill Gates.
What I would like if for excel not to Default AM if a user doesn't remember to put in AM or PM and return an error message.

 

[Gilbertjoe]

what I mean by "remember" is
the users enter 2:00 and do not specify AM or PM. The users, being idiots most of the time, "forget" to specify AM or PM and just enter 2:00 and move on to the next cell unaware they just entered a time 12 Hours off. They think 2:00 means 2:00 PM. Excel, of course reads a 2:00 entry by the user without AM or PM as AM and 14:00 as 2:00 PM.
I'd rather use 24 hr time and be done with this mess but am stuck in this crappy 12 hr format at my managers direction.

 

[tahilg]

What you have specified is the display and output format. What you are asking about is input validation. MS Excel does not have that. MS Access does.

Gunny

 

[MSIsam]

If the user will not be inputting odd times (ex. 2:01 PM), you can just create a drop down list and use that as your selection criteria
Hope this helps.