If Statement with Time

I am trying to write IF THEN Statement using time numbers (i.e. 16:00, 13:30, 
6:30).  It doesn't appear to be working.  Here is a sample

=IF(AND(E4<17,E4>4),"NP", "P")  where E4 is a cell that contains the 
military time.  I keep only getting a value of P returned even when it should 
be NP.  I tried using 17:00 and 4:00 in the statement and got an error

-- 
Court
0
Court8162 (4)
11/15/2004 5:59:03 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
418 Views

Similar Articles

[PageSpeed] 48

Hi
try
=IF(AND(E4<17/24,E4>4/24),"NP", "P")

or
=IF(AND(E4<TIME(17,0),E4>TIME(4,0)),"NP", "P")

--
Regards
Frank Kabel
Frankfurt, Germany

"Court" <Court@discussions.microsoft.com> schrieb im Newsbeitrag
news:C2A75B59-966A-4A4A-B756-2C1199DC09CA@microsoft.com...
> I am trying to write IF THEN Statement using time numbers (i.e.
16:00, 13:30,
> 6:30).  It doesn't appear to be working.  Here is a sample
>
> =IF(AND(E4<17,E4>4),"NP", "P")  where E4 is a cell that contains the
> military time.  I keep only getting a value of P returned even when
it should
> be NP.  I tried using 17:00 and 4:00 in the statement and got an
error
>
> --
> Court

0
frank.kabel (11126)
11/15/2004 6:11:25 PM
Reply:

Similar Artilces:

Help
I am running an Exchange 2000 Server and one of thje internal users has sent an email to internal and external recipients. The To address was an email and the CC address was a mixed internal and external distribution group. We also run MCaffee GroupShield for Exchange AV Software. The out queue has hundreds of emails and the internal users mailboxes are filling up PLEASE HELP!! First stage how can I remove this mail so it isn't sent anymore and second whats caused it and how do I prevent it! ...

Fax Line utilization with Start and END time data
I have large list from our fax servers that produces a lof with : Start of FAX Connect date/time, End of Fax Connect date/time I know I have 24 lines but need to find how many lines have I bee using historicaly to do capacity planning. Any suggestions? M -- Message posted from http://www.ExcelForum.com ...

Excel should let me enter time in one hundreds of a second.
I am a volunteer Track & Field coach for 13 years, and would like to use Excel to keep my team's satistics. But am unable to enter time as minutes, seconds, tenth of seconds, or hundreth of seconds. Probably because I don't know how to do it without converting minutes to seconds. Sure would be nice to be able to enter exact times and query for totals and averages. Hopefully the newer version of Excel/Office will have this capability, and that I can afford it. I would rather use Office then have to purchase a seperate program called "Team Manager" ------------...

Data in a text box depending on the type of data choosing "text" (i.e. Drop ship) or "a time (.i.e. 05:00 PM)
Hi all, If there a way I can have a something on my form that allow me to choose which type of data I will enter into a feild? And whatever the data type entered, the data will appear on my detail section report. I'm explaining, I have form made from a query and that same query came from a table, and have two feilds names in my table which I will like to choose to enter one or the other type od data (i.e. Drop ship) or "a time (.i.e. 05:00 PM) Does anyone have an idea, how this kind of function? can be made. Thanking you in advance for your continuous free support Fred's A...

Outlook email
When I want to write an email, I click on 'new' then 'TO:'. On the right side of the screen, it says 'Show Names from the:', and in the white box below, it says Contacts. Problem is, that 'Contacts' file is empty. If I click on the drop down arrow just to the right of the word Contacts, I get a list. The top item in the list, indented slightly to the left, is "Outlook Address Book", which is empty. The other three items in the drop down list are all labeled 'Contacts', but the first one is blank, and the other two have my full contact li...

need help with
Can anyone help me with this problem? I am making a table that will calculate hrs worked. Column B has the hr started. Column C has the end time. Note - the start time can be in the evening and the end time can be in the am of the next day - giving me a negetive number. I would need the easiest way to find the time difference between the two. This would be my column D. I would then need to add these two columns up to figure the total number hrs worked... Thanks! :) -- rvnwdr ------------------------------------------------------------------------ rvnwdr's Profile: http://www.e...

Expand capabilities of Time Off requests in Business Portal.
The current capabilities of managing time-off requests in Great Plains is very limited. I would like the ability to set-up more than Vacation/Sick time-off codes, and this should flow through to Business Portal so employees can specify if the time-off is vacation, sick, personal, floating holiday, etc... ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the ...

calculating quantity on hand based on date-time stamp
Hello: I am always unclear on the proper syntax for pulling date-related data. I need to pull a field in my query, below, based on the field called "IV00118.CHANGEDATE_I". This is the date in which an end user changed the cost of an item. To give you some background, the IV00102 table in my query below is the Item Quantity Master table, while the IV00118 table is the Item Cost Change History table. The field that I want to pull based on that date field is the IV00102.QTYONHND field. This field is the inventory quantity on hand field. How would I format the syntax bel...

Some date &/or Times will not format
Good afternoon, I've imported some Date & Time data into excell, but when I try to format the colums as either date or time, some of the cells will not formatt. I've been told that sometimes importing data will result in hidden characters that will prevent formatting. Any way to correct this using a formula or other means? ex: 11/27/2009 16:23:00 PM . Best regards, -- Keith Nascarfan88 wrote: > Good afternoon, > > I've imported some Date & Time data into excell, but when I try to format > the colums as either date or time, some o...

"multiple distances versus lowest time for a specific distance"
What is the conditional formatting formula for multiple distances eg 50, 100, 200 in 1 colum to a specific time in another colum. I'm looking for the lowest time for each ditance. see your other post -- Biff Microsoft Excel MVP "shell aw" <shellaw@discussions.microsoft.com> wrote in message news:D1FAF66C-CDE3-4459-A059-C5BC8AF8C518@microsoft.com... > What is the conditional formatting formula for multiple distances eg 50, > 100, > 200 in 1 colum to a specific time in another colum. I'm looking for the > lowest time for each ditance. ...

Last Login Time
Hello to all, first off apoligies if this has already been posted but I could not find any information on it. In the system manager a users mailbox will show who was the last person to log on to the mailbox with the date and time stamp. It appears that this information is overwritten if a user logs in, logs out and then logs back in again. I need to know if this logging in and out information is archived or kept somewhere on the server. I want to go back a couple of days and see what users have been logging into a specific mailbox. I've already checked the event logs on the exchange serv...

How to show current date and time
Hello, I want to show current date and current time in a single cell. I have tried by using today() and now() functions separately, i dont know how to use together in a sigle cell. I want like 28-Mar-06, 12.05 PM. Also I want to add text to it. Like Report for 28-Mar-06. Experts please help me Thanks Ravi -- vsr_kmb ------------------------------------------------------------------------ vsr_kmb's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32834 View this thread: http://www.excelforum.com/showthread.php?threadid=527066 Try this: ="Report for "...

Access Template for Maintaining In Time & Out Time of Employee's
I require any Access templates for Maintaining Employees In Time & Out Time (Break Out & In) Data. Hope someone can able to help. -------------------- (Ms-Exl-Learner) -------------------- Have you searched on-line, or at Microsoft's site for "time-clock" applications or templates? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in t...

not enough memory or disk space to display or print the picture after 1st time
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC I know this topic has been covered before, but not in the way I get the problem. I can open a document with a math formula or a graphic the FIRST time and it will correctly display. Any changes to the document or any subsequent openings I then get the error message, &quot;not enough memory or disk space to display or print the picture&quot;. <br><br>eMac 1.25 Gz 10.4.6, Word 2004 <br><br>Thanks. One possible cause: You're *way* behind on Tiger -- it's been at 10.4.11 for ...

hyperlinks to external website open new window every time
I have several hundred links in my spreadsheet all pointing to the same website. The problem is, when I click on any link, Excel opens a NEW browser window every time. This is especially annoying since the website they all go to requires me to login every time. Is there any way to prevent Excel from opening a new browser window every time? OS: Windows 2000 SP 4 App: Excel 2002 I don't believe that this is possible without programming a solution. I have overcome this limitation of Excel using mainly Windows API calls. If you're interested in this solution, drop me an EMail. If ...

Double If Statement help
Hi I am trying to create a formula that will look in column "b2" which will be a reference to a month i.e Jan then if the answer is Jan, then look in column "h2", to see if it contains a "1" (it will either contain a "1" or nothing). If it does i need it to take the value out of column G2 (which will be a number) and paste it in the cell im typing the formula into to. Hope This makes sense any help will be greatly appreciated. Thanks Submitted via EggHeadCafe - Software Developer Portal of Choice SharePoint Video Library Template Ava...

combine date and time cells
I have a data recorder that records start and start time. When I export the data out of the device it comes across with date in one cell [=DATE(104,1,27)] January 27, 2004, and time in another [(=TIME(3,16,12)] 03:16:12 AM in Lotus 123 format. I open in excel and have tried to combine the two cells using the "&" key but can't get the result to yield a date and time in Excel. The formula that I used to combine the two cells is "=U7&" " & V7" where U7 and V7 are date and time respectively. I have also tried the formula as "=U7&...

Hid and unhide and clear contents for specific columns at the same time
I have a serious problem with excel programming and i am not able to figureout how to do the following funtions. I have 24 columns. names Jan -Dec ( Actual) then Jan- Dec (Forecast). There is one reference cell A1 ( for current month). - If A1= Jan, I want macro to hide FEB-DEC 9 Actual , and leave FEB-DEC (forecast). -If A1= FEB , I want macro to unhide FEB Actual and hide MAR-Dec ( actual) and at the same time clear contents in FEB (forecast) and hide feb (forecast ). ANd so on I would really appreciate if somene could help me figure out this problem. Thanks Use one of these in the...

Trim Time off Date
Access 2007 linked to SQL 2008 database I have a query with a date field that has the time included on it. How do I trim, truncate, remove the time part of the date in the query field named cndate? I might use an expression like the following: IIF(IsDate(cnDate),DateValue(cnDate),Null) Or perhaps a bit faster IIF(CNDate is Not null, DateValue(cnDate),Null) John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County GMC -LSND wrote: > Access 2007 linked to SQL 2008 database > > I have a query with ...

if and and statements
Looking for help with a 2 x 4 matrix with the following conditions A1 B1 C1 Answer 3 0 1 A1*4+2 3 0 2 0 3 1 1 A1*2+1 3 1 2 0 =IF(AND(B1=0,C1=1),+A1*4*2,IF(AND(B1=0,C...

Convert time stored as decimalised number to time format
Hello How can I convert 3.5 hours to 3:30:00? -- Emma Emma A1/24 and formate as time Mike Rogers "Emma" wrote: > Hello > How can I convert 3.5 hours to 3:30:00? > -- > Emma ...

Date and time onto a form
Hi, I am designing a form that my client would like the date and time automatically recorded in a field and stored so that when they refer back it shows when the record was done. The 'NOW' function only shows the current date & time and keeps updating. Is there a way of putting the 'NOW' value into a field, then locking it so it doesn't change when the record is reopened. Hope that makes sense! Kazlou The form stores data in a table. You need to add a date/time field to your table to record the date and time a record was last updated. 1. Open the table in desig...

Configuration restarts each time an Office module is opened
Hi All I am going quite mad. I have installed Microsoft Office Home and Student 2007 on one Vista notebook. Well and good. I try it on another notebook, register it successfully and now each time I open Word it tries to perform a setup again, with a Configuration Progress dialogue popping up. A few decades later Word appears. There is absolutely no indication what the problem may be. Any ideas are welcome. Cheers Andre Never mind. Problem solved. The registry needed manual editing. "J Andr´┐Ż Labuschagn´┐Ż" <technical@eduadmin.com> wrote in messa...

calculating date/time #2
Hope someone is able to help. This is to calculate the chargeable hours for a SAR boat in the water Date In| Time In| Date Out| Time Out|Conversion Time| Total Conversion time is value of ((DateOut+TimeOut)-(DateIn+TimeIn)*24) but needs to be rounded up to the nearest 0.1 i.e. 0-6=.1, 7-12=.2, 13-18=.3 etc. with a minimum chargeable time of 2 hours. Have tried ROUNDUP and CEILING but obviously not the correct way! Thanks Rick -- relaing ------------------------------------------------------------------------ relaing's Profile: http://www.excelforum.com/member.php?action=getinfo&am...

HELP! I want to start a macro at a certain time
Hi, I really need help. I want to start a Excel macro at a certain predetermined time. At that time I will not be at the computer. Does anybody know how to do that? Thanks a lot Bye Joerg you can use the taskplan of the windows system. "Joerg" <sports_guy@gmx.de> ???? news:55dbecf2.0410192033.735c14b4@posting.google.com... > Hi, > > I really need help. I want to start a Excel macro at a certain > predetermined time. At that time I will not be at the computer. Does > anybody know how to do that? > > Thanks a lot > > Bye Joerg You can also use ...