Solved

Force Time Values

Posted on 2013-01-25
12
215 Views
Last Modified: 2013-01-26
Hello Experts,

I have a spreadsheet that I deployed to a few employees to test throughout the day. While I have no difficulty typing in time values the proper way for Excel (IE ##:## AM). I noticed each employee typed it slightly different, and it created problems with my spreadsheet.

Examples of how the user typed in time values "5:00PM" resulted in errors, and "1:00" resulted in 1:00 AM, when the user really wanted 1:00 PM.

Ideas?

~ Geekamo
0
Comment
Question by:Geekamo
  • 6
  • 5
12 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38821379
Geekamo,

Fortunately, human imagination knows few bounds so it's unlikely that we can come up with every possible method that your users can devise. You're off to a good start with the test users, but you know the rest will surprise you! By any chance do you have other spreadsheets on which they enter times? If so, analyse that.

If you don't have previous examples then get each user to enter a handful of specified dates on a spreadsheet - then you'll know exactly where you are. (If you and the users are on the same LAN then this is a trivial exercise. Otherwise the worst case is that you'll need to use your email system and there are lots of examples of that in this site!)

Some questions...
 - What's the earliest time?
 - What's the latest time?
 - Are minutes and/or seconds allowed to be non-zero?
 - Is the user entering a single time or a From and To pair?
 - If Pairs, is there a maximum and/or minimum gap between the start and end time?

Edit: Oops, meant to say that once you know how they enter times then you can accept their formats or give them an error message. I'd definitely give them the error message - otherwise you're teaching them to enter invalid formats which you know will cause problems later on. At best, I'd give them a message ("Did you mean 4:32PM?") every time - they'll soon learn it's quicker to do right the first time!

Thanks,
Brian.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38821415
@ redmondb,

I've attached a screenshot of the 3 cells in question... (Start, End, Exclude), and hell might as well throw the white columns into the mix too.

I definitely want the users of this worksheet to follow the strict ##:## (AM/PM) format.  As far as I'm concerned - if you're forced into it, then in a day or two the user will be use to it and it'll be second nature.

Time Format
Cells : B5 and C5

The user could have a time value as low as "8:00 AM" and high as "7:30 PM".

Cell : D5

Is formatted to show #:##, for example "1:00" would represent 1 hour. (Although I think in the formula bar, it reads as "1:00:00 AM".

Columns : F:I

These time values are always, #:##:##.

So that's basically my setup.  My goal is to force the users to type in the proper format for each area, and if they do it wrong - they'll get a little validation warning message.

~ Geekamo
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38821439
Hi, Geekamo.

Please see attached. You will need to look at the four different data validations (B5, C5, D5 and F1:I20) as I made some assumptions.

Regards,
Brian.Cond-Form.xlsm
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38821465
@ Brian,

Your example workbook appears to block every attempt I make typing a time value incorrectly.  I'm looking at your formulas, but the majority of it - I'm lost.

Would you mind taking at least one of your example formulas, and talk me through what it's doing?  I'm having a hard time following it through.  (I'm still a newb in training!)

~ Geekamo
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38821546
Geekamo,

Sure!

The one in C5 is the most complex, so let's go through that...
=AND(C5>=1/3,C5<=19.5/24,C5>=B5)

The AND function returns TRUE when all of its comma-delimited entries are themselves TRUE. In this case, there are three entries...
C5>=1/3        This returns TRUE if the value in C5 is greater than or equal to 8:00:00 AM.
C5<=19.5/24 This returns TRUE if the value in C5 is less than or equal to 19:30:00 PM.
C5>=B5         This returns TRUE if the value in C5 is greater than or equal to the value in B5.

So, if each of these entries is true then AND will pass TRUE to Data Validation, indicating that the value entered is valid. Conversely, if even one of the entries returned FALSE (perhaps the Start Time was greater than the End time) then AND will pass FALSE and Data Validation will display an error message.

(As an aside, Excel stores times as values between 0 and 1. So, 1 is 24 hours, 0.5 is 12 hours, 8/24 = 1/3 is 8 hours, etc.)

Please let me know if you'd like more on any of this or of the other cells.

Regards,
Brian.
0
 
LVL 10

Expert Comment

by:broro183
ID: 38821670
Hi Geekamo,

It looks like Brian has got you sorted out, so this is just an aside which may or may not be useful. If you press [ctrl + shift + :] it will enter the current time in the active cell.


hth
Rob
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Comment

by:Geekamo
ID: 38823194
@ Brian,

Your explanation was perfect!

Thank you very much!

~ Geekamo
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38823195
@ Rob,

Thanks, great tip!

~ Geekamo
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38823206
Thanks, Geekamo!
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38823282
@ Brian,

Do you mind taking a look? Let me know if you notice any problems with my version of the formula.  It's your solution, with my name references and I am referencing serial time (I think that is what it's called)

So, B5:D5 & F:I all has validation.

Start and End (min 7:30 am / max 7:30 pm)
End needs to be greater then start.
Exclude needs to be less then the duration of End - Start
And the columns need to be less then the duration of End - Start

~ Geekamo
Talk-Time-Calculator.xlsm
0
 
LVL 1

Author Comment

by:Geekamo
ID: 38823299
@ Brian,

Actually, as I play around with some of the cells - I noticed an issue.

In the columns section, it will only accept a value if the duration of that value is between the duration of End - Start.

So if my start is 10:00 AM and my end is 10:10 AM, then it won't accept the call duration if it's longer then 10 minutes because you weren't at work long enough to have a call that lasted that long.

So that aside, if you place a call duration in the column, it doesn't give an error message even if there is no start & end time. Any ideas?
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38823433
Geekamo,

(1) Data Validation is a bit peculiar about blank values. So I unticked the "Ignore Blank" for C5, D5 and F:I.
(2) Not an error, but some of your Validations used unnecessary SUM and/or AND, so I tidied them up.
(3) Your explicit time values were also not an error. I didn't change them, but I'd recommend using 7.5/24 rather than 0.3125 - they're both 7:30AM but in six months time which one will be the easier to understand and/or change?

Regards,
Brian.Talk-Time-Calculator-V2.xlsm
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now