Hello!
I'm currently tearing my hair out trying to figure out how to validate a cell where users input the date and time.
I have a formula set up that calculates the time difference between two cells. It will only calculate properly if the date and time are entered as:This would all be great if the end users would actually follow directions and enter the data as instructed. However, when I add validation to the columns with the dates & times, it fails because when users enter, for example, 7/15/11 8:03 AM, Excel automatically turns it into 7/15/11 8:03:00 AM, thus making the entry invalid.[$-409]m/d/yy h:mm AM/PM;@
Is there any way to stop Excel from automatically appending the seconds on to the time? Thanks in advance for your help!
Using Excel 2007, if that makes any difference.
Last edited by werm; 08-08-2011 at 08:20 AM. Reason: Added Excel version.
As long as valid times are entered then the formatting shouldn't matter - formatting only affects the way the data is displayed - it shouldn't have an impact on any calculations, e.g. 12:45:00 will be treated the same as 12:45
Which formula are you using for time difference - you should only have to subtract one from the other?
Audere est facere
The formula goes a little something like this:
It's a hand-me-down spreadsheet, so I didn't write the formulas, I can just tell you what the formulas are...=IF((OR(H142="N/A",I142="N/A",M142="Y")),"N/A",I142-H142)
OK, so with some conditions that's just a subtraction as I suggested. Why doesn't that work if seconds are displayed? It shouldn't make any difference......
Audere est facere
The validation I'm using is
I'm assuming this is where the problem lies, because it's checking for h:mm, but Excel changes the input to h:mm:ss. Is there any way to tell the spreadsheet to stop appending the seconds?Allow: Custom Formula: [$-409]m/d/yy h:mm AM/PM;@
I think you are confusing several different things here.....
You said that your formula didn't work correctly if the seconds were displayed - I don't follow you, as previously stated the display won't affect the calculations......
You say you are using data validation but what you have quoted would normally be used in number formatting rather than data validation.
If you don't want seconds entered then try this
Custom format cells as per your suggestion, i.e.
[$-409]m/d/yy h:mm AM/PM;@
[this simply stops display of seconds, it doesn't dictate whether there are seconds in the value]
then use custom data validation like this:
=SECOND(A1)=0
[assuming you are entering data in A1 - change as required]
That prevents user entering seconds other than zero
Audere est facere
I'm sorry, I got it mixed up. The calculation works fine either way.
I'm trying to prevent people from entering the date and time in different formats, like
which breaks the formula.8/3/11 336pm
So I have the validation set up like suggested, with, but whenever I try to enter the date & time in the exact format it's looking for, I get a validation error. Does that make more sense?[$-409]m/d/yy h:mm AM/PM;@
Here is an example spreadsheet, identical to what I'm working with if it helps at all.
Maintenance Log.xlsx
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks