I'm trying to setup a couple formulas to determine the number of calls that go to voice mail every week. Then if there are any calls that went to voice mail I want to be able to have a formula to determine if is was during business hours.
Part 1: I have a call log tab setup. I'm manually entering the calls that go to voice mail in this tab. Column A is the date (6/2/2008), Column B is time (6:50 AM). The formula I've come up with to calculate the number of calls each week is: =COUNTIFS('Call Log'!A4:A128,">=6/2/2008",A4:A128,"<=6/6/2008"). The problem with this formula is that when I copy it down the date range for the current week doesn't change. Is there anyway for the date to automatically change?
Part 2: After the first formula determines if there were any calls that made it to voice mail. I want to be able to tell if it was during business hours (AM-PM CST). Any idea how I can do that?
Thanks.
Part 1: I have a call log tab setup. I'm manually entering the calls that go to voice mail in this tab. Column A is the date (6/2/2008), Column B is time (6:50 AM). The formula I've come up with to calculate the number of calls each week is: =COUNTIFS('Call Log'!A4:A128,">=6/2/2008",A4:A128,"<=6/6/2008"). The problem with this formula is that when I copy it down the date range for the current week doesn't change. Is there anyway for the date to automatically change?
Part 2: After the first formula determines if there were any calls that made it to voice mail. I want to be able to tell if it was during business hours (AM-PM CST). Any idea how I can do that?
Thanks.