I am trying to build a report which shows if a date and time is between 9-5 Column 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

How about this: =IF(AND(HOUR(A1)>8, HOUR(A1)<18),"9 to 5","Not 9 to 5")

Hi

Thanks 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?

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.

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

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.

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

Based on what you said in Response 4, I don't think that's what you want to use.

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")

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History