Excel - During Business hours formula 1

[TrentSteel]

Hello All

I have a spread sheet of the entrance/exits to our security areas over monthly period laid out as below:

|User | Place | Time | Action |

I need a formula that examines C2-Cx (a period that will cover from the start of the month to the end - 06/01/04 to 06/30/04), determines if the action was during business hours(8:00am - 5:00pm, returning 1 if it was during business hours and returns 0 if it was outside business hours. I am going to use the 1's & 0's for all sorts of formatting/charts, but I just can't figure out the IF statement.

If anyone can help or point me in the right direction I would really appreciate it

TIA

Trent

 

[SkipVought]

Trent,

Might be nice to post a small sample of your data.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SkipVought]

Might be something like this
[tt]
=IF(AND(C2>=8/24,C2<=17/24),1,0)
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[TrentSteel]

Skip

As per your request:
Row |USER | PLACE | TIME | ACTION
1. |J. Doe |Datacentre | 6/1/04 8:06 | open
2. |L. Roy |Cabinet | 6/1/04 8:06 | open
3. |J. Doe |Office | 6/1/04 8:09 | close

thru

567.|R.Handy |Datacentre | 6/30/04 19:04 | close

Does this help?
(first formula did not work, only returned 0's - what's the 8/24?)

TIA

Trent

 

[SkipVought]

You have DATE & Time.

8/24 is the TIME VALUE for 8:00.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SkipVought]

[tt]
=IF(AND(C2-INT(C2)>=8/24,C2-INT(C2)<=17/24),1,0)
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[TrentSteel]

Saved once again by Skip!!

Thanks man!

Trent

 

[SkipVought]

Clark Trent, Man of Steel! ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[TrentSteel]

Skip

I don't suppose it would be possible to figure out if it was a Saturday or a Sunday? I would also like to give any entry on these days 0's

Also, I probably should have mentioned that first....my bad

Trent

 

[SkipVought]

[tt]
=IF(AND(C2-INT(C2)>=8/24,C2-INT(C2)<=17/24,TEXT(C2,"DDD")<>"Sat",TEXT(C2,"DDD")<>"Sun"),1,0)
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SkipVought]

This one might be "easier" to understand
[tt]
=IF(AND(TEXT(C2-INT(C2),"HH:MM")>="08:00",TEXT(C2-INT(C2),"HH:MM")<="17:00",TEXT(C2,"DDD")<>"Sat",TEXT(C2,"DDD")<>"Sun"),1,0)
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SkipVought]

I had some extraneous stuff in th last one
[tt]
=IF(AND(TEXT(C2,"HH:MM")>="08:00",TEXT(C2,"HH:MM")<="17:00",TEXT(C2,"DDD")<>"Sat",TEXT(C2,"DDD")<>"Sun"),1,0)
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[bluedragon2]

Just a thought Skip: =OR(WEEKDAY(C2)=7,WEEKDAY(C2)=1)



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

but then you have to KNOW that 1 is equivalent to Sat and 7 is equivalent to Sun.

I like the more Self-Documenting approch, whenever possible.

;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[bluedragon2]

cool

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

See that, Blue, I mixed them up!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244