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
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
ASKER
@ 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.
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
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.
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
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
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
ASKER
@ 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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
@ Brian,
Your explanation was perfect!
Thank you very much!
~ Geekamo
Your explanation was perfect!
Thank you very much!
~ Geekamo
ASKER
@ Rob,
Thanks, great tip!
~ Geekamo
Thanks, great tip!
~ Geekamo
Thanks, Geekamo!
ASKER
@ 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
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
ASKER
@ 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?
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?
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
(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
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.