Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

Force Time Values

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
Geekamo
Asked:
Geekamo
  • 6
  • 5
1 Solution
 
redmondbCommented:
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
 
GeekamoAuthor Commented:
@ 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
 
redmondbCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
GeekamoAuthor Commented:
@ 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
 
redmondbCommented:
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
 
broro183Commented:
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
 
GeekamoAuthor Commented:
@ Brian,

Your explanation was perfect!

Thank you very much!

~ Geekamo
0
 
GeekamoAuthor Commented:
@ Rob,

Thanks, great tip!

~ Geekamo
0
 
redmondbCommented:
Thanks, Geekamo!
0
 
GeekamoAuthor Commented:
@ 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
 
GeekamoAuthor Commented:
@ 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
 
redmondbCommented:
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now