Custom validation rule - must be 15 increment of time

Hi,

I have been using this formula to round time values to  the nearest 15 
minute increment:
=ROUND(E10*96,0)/96

This works fine for cells where the value is auto-calculated.  However, I 
have one cell where a value has to be manually entered are strictly 15 
minute increments?  What I thought I might do is create a Data Validation 
rule that checks to see if the value * 96 is an integer.  If it isn't then 
it fails.  How would I do this?  I know it can be done, I am just having a 
bit of a block.

TIA,

Jarryd 

0
Jarryd
5/19/2010 12:11:36 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
1381 Views

Similar Articles

[PageSpeed] 15

=INT(F13*96)/(F13*96)=1

"Simples"!

Cheers,

Jarryd

"Jarryd" <jarryd@nodomain.com> wrote in message 
news:8A788E92-80FB-4AD2-A1A3-2BA1BA6A1D78@microsoft.com...
> Hi,
>
> I have been using this formula to round time values to  the nearest 15 
> minute increment:
> =ROUND(E10*96,0)/96
>
> This works fine for cells where the value is auto-calculated.  However, I 
> have one cell where a value has to be manually entered are strictly 15 
> minute increments?  What I thought I might do is create a Data Validation 
> rule that checks to see if the value * 96 is an integer.  If it isn't then 
> it fails.  How would I do this?  I know it can be done, I am just having a 
> bit of a block.
>
> TIA,
>
> Jarryd 

0
Jarryd
5/19/2010 12:17:24 PM
Reply:

Similar Artilces:

How to create a CustomerID field for custom entities
Hi, The customerid field is used in opportunity and case and it allows you to select an account or contact, Great. I want to create this field in a form for a new entity i have made, but i cant work out how. Please help. Hi Michael, Customer is special composite type which is not available for custom entities in CRM 3. You will need to add two separete lookup fields for account and contact. -- uMar Khan :: MS CRM MVP CRM Freelance Consultant Email :: imumar at gmail dot com Blog :: http://umarkhan.wordpress.com MVP :: https://mvp.support.microsoft.com/default.aspx/profile/umar.khan &...

Appointment times won't post correctly
On the calendar, appointments, meetings, etc. made or received show up on the date and time received or sent; regardless of the actual date or time of the appointment. Is there a setting that will correct this? I've exhausted myself trying to find a fix. Outlook 2003 There is a seperate calendar time zone setting that has to be set in addition to your system clock. Pain in the ass. Go to Options and then select Calendar. You should see the Time Zone button there. Set it accordingly, but be aware that everything in your calendar up to that point will change. "Keith Wilso...

office 2003 w/ windows 7 requires reacceptance ot T&C's each time
office 2003 w/ windows 7 requires reacceptance of T&C's each time i open work or excell or any office program. How do I get rid of this? Boot into Safe mode in Windows 7,open any Office app and then accept the terms -- Peter Please Reply to Newsgroup for the benefit of others Requests for assistance by email can not and will not be acknowledged. "mouser830" <mouser830@discussions.microsoft.com> wrote in message news:E7B7602D-F5D8-4559-98EF-A2CCC1E2D6FD@microsoft.com... > office 2003 w/ windows 7 requires reacceptance of T&C's each time i op...

Using crm:5555 rather than a custom hostheader
All I would just like to know what's the recommended solution for a CRM (v3) deployment:- use the standard crm:5555 or a custom hostheader e.g. crm.mycompany.net. Will the custom hostheader cause me any problems? Will external users be able to logon remotely using this host header? Many thanks, Nick. ...

Custom Item Movement Report
Anyone out there have a custom item movement report and item movement history report that includes extended description that they would be willing to share? Thanks! -JIM That was a pretty tricky request but I was able to make the change. Download the reports here: http://www.newestech.com/downloads/itemmovement.zip If you cannot download from there shoort me an email and I will send over. Check out our RMS add-ons website in exchange.. http://www.newestech.com/POS/extensions.htm Casey Hanson New West Technologies chanson@newestech.com Hi, Can you do one with "Transferred To"...

How many Server-side rules can you put on one mailbox?
I am using Exchange Server 2003 and want to setup 52 rules on one of the mailboxes, is this possible? the hardcoded limit on space for rules is 32K...i doubt you'll be able to get 52 rules on one mailbox... -- Susan Conkey [MVP] "jmareel" <jmareel@discussions.microsoft.com> wrote in message news:6ADDDCD7-948C-415F-B194-1944C9936BC2@microsoft.com... > I am using Exchange Server 2003 and want to setup 52 rules on one of the > mailboxes, is this possible? http://support.microsoft.com/default.aspx?scid=kb;en-us;147298 -- Bharat Suneja MVP - Exchange www.zenpris...

Custom cell formatting
I need to create a custom format for a series of cells that will begin like this. I can't figure out what the code character is fora volitile potentially alpha character. Can anyone help me?? -Monica, Dallas 000000 000001 000002 .... 000009 00000A 00000B 000010 000011 ...... Monica something like this might work for you but you'll need to put all the leading digits in for the entry with the alpha character Regards Trevor "MDavison" <davison@fr.com> wrote in message news:#SD0tUzTEHA.1652@TK2MSFTNGP09.phx.gbl... > I need to create a custom format for a series ...

custom address list for EXTERNAL email addresses
I know it is possible to create a custom address list in Exchange 2003 for internal use but how about for external email addresses outside of a company? Is this possible?? Any help would be appreciated. Thanks in advance. Create contacts for each of the external mail addresses and add them to the distribution list. If add a valid internet email address to the distribution list external people can also send to it. Glen "Steven Peters" <Steven Peters@discussions.microsoft.com> wrote in message news:CBF2F2AE-ABF4-491C-836C-59611375F9F1@microsoft.com... > I know it is pos...

STUMPED WITH OUTLOOK RULES!
Nobody has been able to figure this one out yet. Is there any possible way to set up a rule either on the local computer or the mail server that can call for an email to be automatically forwarded to someone without including the attachment? JJ <anonymous@discussions.microsoft.com> wrote: > Nobody has been able to figure this one out yet. Is there > any possible way to set up a rule either on the > local computer or the mail server that can call for an > email to be automatically forwarded to someone without > including the attachment? No need to ask the same que...

Set default time zone for all users
Is there a way to make the time zone default to a certain value so that I don't have to go into every user and change it to the correct zone? No. Each user must change their own timezone settings. I have no idea why Microsoft do not pick up your computers timezone. Hopefully this will be changed in a version 2.0 release "Jack Black" <anonymous@discussions.microsoft.com> wrote in message news:6F5F4A10-B6AD-420C-9ACB-F73A13FDABB9@microsoft.com... > Is there a way to make the time zone default to a certain value so that I don't have to go into every user and change i...

Keyboard shortcut for current date and time
Hi, Ctrl+ ; inserts current date and Ctrl+Shift+; inserts current ti me Ctrl+Shift+; inserts the current time with the date serial as 0 and not the current date's date serial. Presently I am adding the two (ie current date and time) to get the current date and time. Is there a keyboard shortcut that does this? Thanks in advance. Regards, Raj CTRL+; then SPACE then CTRL+SHIFT+; -or- =Now() -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware "Raj" <rspai9@gmail.com> wrote in message Hi, Ctrl+ ; inserts current dat...

Can I set my custom views on Pivot table
I want to get Pivot table data on my invoice, is it possible to set custom views so that I can get Pivot table results on invoice format, I've tried views that offer excel but it doesn't offer custom views please help Thanks ...

Supress col/row increment with copy
Question with (hopefully) an easy answer.... When you copy a cell that uses a formula referring to another cell, for simple illustration say "=C4" it will increment based on where you paste it. So, it will increment up to "=C5" if you paste it in the next cell, or "=D4" if you paste below. How can I suppress that, so that when I paste the new cell receives "=C4" as well. (I know I can simply copy the text and paste that in, but I want to copy a whole row of formulas to be the same. I thought Paste>Special>Formulas would work, but it seems to ...

Z Report Customer Count vs Hourly Sales
My Z report shows customer count of 380 and hourly sales total of 384. Why the difference? This happens all the time. Thanks Your glass is: Half-Empty : Do folks at register forget to ask "Is there anything else I can help you with?" Half-Full: Your impulse items at POS are working -- better late than never! sammy wrote: > My Z report shows customer count of 380 and hourly sales total of > 384. Why the difference? This happens all the time. > Thanks -- ______________________________________________________ Larry Leveen OlyBikes Locally-Owned B...

OWA & "User must change password at next logon"
Current Environment: Running EX2K3(SP2), and using OWA as the ONLY option to get e-mail; using certificates (https:\\owa.domain.com\exchange) & forms based Authentication. Problem: I create a NEW user in AD; e-mail enable it, and select "User must change password at next logon". Now when the user tries to log in (VIA OWA) s/he is not prompted to change the password / it won't even log them in. It use to work with our old exchange 2000 environment, where it gave them the opportunity to change the password at sign on. Answer: ? There is one property in MetaBase: ...

Outlook 2003 & Rule To Foward Email
Hello gang. We just moved over to Outlook 2003/Exchange Server 2003 a few months ago. I am having a problem of a simple rule that just forwards an email that I receive in my inbox to a webmail account - hotmail or yahoo. I have tried various rule adaptations and I have even put these addresses in my safe senders/safe recepiants junk mail list. We are running outlook in cached mode. Any help would greatly appreciated. Thanks. Ask your Exchange admin if they have replies and forwards to the Internet turned off - it is by default in Exchange 2003. --� Milly Staples [MVP - Outlook] ...

Custom CRect Question
I have a custom rectangle class that inherits from CRect: class CCustomRect : public CRect { private: CPoint m_maxPt; COLORREF m_color; TCHAR text[50]; public: CCustomRect(RECT* source, CPoint pt, COLORREF rgb); RECT* RectBase(); void Update(RECT* r); } Inheritance has worked well until I found myself needing to create the RectBase function (above) to return the rectangle dimensions. CRect does not seem to have any methods that can be called to return the base class's RECT value. I could take CRect::Size and construct a rectangle to return, but this seems a bit much. My...

why I see times new roman?
I have several html email messages composed in outlook express 6, I'm sure I highlited all text and set it to Arial 10. Then I drag the file to desktop, move it to vista windows mail draggin into inbox or other folder. The result is some parts of text shows arial 10 but some others Times new roman 10 or 12 what is annoying, because I use all arial 10, so I have to manually forward the email, highligh it again and set arial 10 and then, yes, I can see it properly. I checked the compose default font and everything is fine. Why is that? Is there any work around , helo...

Installing Outlook 2003 using Custom Wizard
I am installing Outlook 2003 using the custom installation wizard. This was working fine for some time. Now, someone in my group must have modified the settings because when I install Office using this MST file the local Outlook Contacts are not showing in the GAL. When I go to the properties and try to add it the check box for "Show this folder as an email address book" is greyed out. I have to go in and manually add the Outlook Address book. Can anyone tell me what setting I can change in my Outlook custom install settings for the MST to make this available by default? ...

Can't get rules to run.
I can't get Outlook 2003 rules to run. I am getting my mail from a pop3 account and would like to have new mail sorted to specific folders when it arrives. I set up the rules as I did in previous versions of outlook, but they do not run automatically. I can run the rules manually and they will move the mail. I have this rule (for example Apply this rule after the message arrive from some@email.address and on this machine only move it to the somefolder folde I have tried deselecting on this machine only Thanks. Do you have an Exchange service in this profile? Are you using Cache...

Rule to automatically mark header to download based on contact list?
Is there a way to do this, or an add-in? TIA. Rich On Tue, 26 Apr 2005 04:44:35 -1000, rmc <rmc@nospam.com> wrote: > Is there a way to do this, or an add-in? What version of Outlook are you using? -- -Ben- Ben M. Schorr, MVP Hi Ben: 2003 SP1 Rich "Ben M. Schorr - MVP" <bens@bogusaddress.mvp> wrote in message news:op.spuxh8sf6r4cx1@bms-tablet2.scgab.com... > On Tue, 26 Apr 2005 04:44:35 -1000, rmc <rmc@nospam.com> wrote: > > > Is there a way to do this, or an add-in? > > What version of Outlook are you using? > > > -- >...

time #11
I have set up a spreadsheet with time based on what our company truck drivers work. We work under a DOT rule of 70 hours / 8 days. That means that the driver can not work more than 70 hours in an 8 day period. The sheets I have (one for each month) goes back 7 days plus the current day and calculated how many hours a day the driver works and subtracts it from 70, leaving how many hours they can work the next day. If the calculation gets to 0 or above, the cell changes color to alert me that the driver's 70 hours are up and he/she has to have 34 hours off before he/she can be on duty ...

prompting to save workbook every time (even when no changes made)
i have 3 workbooks all very similar and every single time i open any o them, even when not changing anything, i am prompted to save th thing. its so bad that i can literally open the workbook then click the clos button straight away and it STILL prompts me to save it because i seems to think something has changed. as far as i can see i dont have any macros or anything that will ru every time it starts and change something (i did have a = today in on cell which kept the date as today but taking this out made n difference -- Message posted from http://www.ExcelForum.com Hi Neowok! You prob...

Times Subtraction Whith Access
Hi I need to know how to subtract between two times variants and to get a time result (With Access). Like this: VarTime=[time1]-[time2] 10:05:40=18:05:50-08:00:10 Thanks Access really isn't intended to do things like that, but ?Format(#18:05:50# - #08:00:10#, "hh:nn:ss") 10:05:40 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) <bennyzamir@gmail.com> wrote in message news:1175704084.566444.282820@b75g2000hsg.googlegroups.com... > Hi > > I need to know how to subtract between two times variants and to get a > time res...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...