# Excel IF statement for 9-5

Excel Excel 2007
June 10, 2010 at 04:16:40
Specs: Windows XP
 I am trying to build a report which shows if a date and time is between 9-5Column 5 has a date/time value of 04/01/2010 13:36:00, I need to output this as time and then find out if this time is between 09:00 and 17:00

See More: Excel IF statement for 9-5

#1
June 10, 2010 at 06:15:40
 How about this:=IF(AND(HOUR(A1)>8, HOUR(A1)<18),"9 to 5","Not 9 to 5")

Report •

#2
June 10, 2010 at 06:19:07
 HiThanks for the replyI managed to get this to work but I have an added factor which I am having difficulty with.I now have column (J5) which takes the create date 04/01/2010 13:36:00, converts it to hours =E5-INT(E5), thenI have a new column K5 with the following formula`=IF(AND(J5>=TIMEVALUE("09:00"),J5<=TIMEVALUE("17:00")),"1","0")`This gives me a value of 1 if it is between 9 and 5 and 0 if not but the added complexity is how do I do the same formula but only taking into account Network Days?

Report •

#3
June 10, 2010 at 06:38:51
 I'm not sure what you mean by "only taking into account Network Days".As per the Excel help files, the function NETWORKDAYS "Returns the number of whole working days between start_date and end_date".That's a "number" not a date and doesn't seem to fit what you are asking about.Please explain further.

Report •

Related Solutions

#4
June 10, 2010 at 06:43:15
 Sorry, I want to only include Monday to Friday in the 9 - 5 calculation.So 0 should be returned if the Create Date is either Saturday or Sunday

Report •

#5
June 10, 2010 at 06:51:41
 Just add a check for WEEKDAY() at the beginning of your IF statement. If WEEKDAY() returns 1 or 7, return 0, if not, continue with your HOUR() check for 9 to 5.Give it a try and get come on back if you have trouble with the "Nested IF". Look up the IF function in Excel help for some examples of Nested IF's and see if you can get it to work.

Report •

#6
June 10, 2010 at 07:01:59
 Think I got it ...`=IF(AND(K6>=TIMEVALUE("09:00"),K6<=TIMEVALUE("17:00"),WEEKDAY(E6)>1),"1","0")`Thanks a million for your help

Report •

#7
June 10, 2010 at 07:07:27
 Based on what you said in Response 4, I don't think that's what you want to use.

Report •

#8
June 10, 2010 at 07:19:24
 I missed Saturday .. The results look to be what I am looking for now, I modiifed the Create Date so it was a Sunday at 09.25 and it gives me a value of 0, if I modify it again to be a Fri it gives me 1 similarily if I change the time to 08.45 on Fri I get 0`=IF(AND(K6>=TIMEVALUE("09:00"),K6<=TIMEVALUE("17:00"),WEEKDAY(E6)>1,WEEKDAY(E6)<7),"1","0")`

Report •