Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
Looking for help...
I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
Not sure if you date is in column A and time in Column B or both the time and
date are in the same column Column B contain just time =SUMPRODUCT(--(B2:B17=TIME(15,0,0)),--(B2:B17<=TIME(18,0,0))) If the time and date are both in column A there is two spaces between the time and date which is causing a problem. I had to use the MID function to get past the extra space. If you could eliminate the extra space it would make the formula simplier. =SUMPRODUCT(--(TIMEVALUE(MID(A2:A17,FIND(" ",A2:A17)+1,LEN(A2:A17)))=TIME(15,0,0)),--(TIMEVALUE(MID(A2:A17,FIND(" ",A2:A17)+1,LEN(A2:A17)))<=TIME(18,0,0))) "Ken" wrote: Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
Hi Joel,
When I try the following it gives me a value error "A value used in the formula is of the wrong data type" =SUMPRODUCT(--(TIMEVALUE(MID(H2:H17,FIND(" ",H2:H17)+1,LEN(H2:H17)))=TIME(15,0,0)),--(TIMEVALUE(MID(H2:H17,FIND(" ",H2:H17)+1,LEN(H2:H17)))<=TIME(18,0,0))) Unfortunately, I cannot remove the spaces to make the formula simpler. What does the +1,LEN(H2:H17) portion do in this formula? Best Regards, "joel" wrote: Not sure if you date is in column A and time in Column B or both the time and date are in the same column Column B contain just time =SUMPRODUCT(--(B2:B17=TIME(15,0,0)),--(B2:B17<=TIME(18,0,0))) If the time and date are both in column A there is two spaces between the time and date which is causing a problem. I had to use the MID function to get past the extra space. If you could eliminate the extra space it would make the formula simplier. =SUMPRODUCT(--(TIMEVALUE(MID(A2:A17,FIND(" ",A2:A17)+1,LEN(A2:A17)))=TIME(15,0,0)),--(TIMEVALUE(MID(A2:A17,FIND(" ",A2:A17)+1,LEN(A2:A17)))<=TIME(18,0,0))) "Ken" wrote: Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
Assuming you dates/times are all in the same column and they're really TEXT
strings: Use cells to hold your time boundaries: D2 = 3:00 PM E2 = 6:00 PM =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
The MID function requires 3 parameters the last being the length. MID will
return either the number of character to the end of the string, or the number of characters specified by the 3rd parameter (len in this case). I want to reutrn all the character to the end of the string so I could of specified 256 characters to make the code simplier. I choose the length to use the length of the string which will always be greater than the number of characters I really need. What the formula does is searches for the first space in the string and returns all the character after the space. the FIND() part of the function returns the character position of the space. I don't want the space in the final results so I add 1 (+1) so I ignore the space. I think the error is because of the 2 spaces. The TIMEVALUE isn't recognizing the the time with the extra space. Try this change. I assume your times are in cell H2 to H17. I tested both my old formula and new formula with your posted data and didn't get an error. It is possible there is some white characters (looks like spaces but aren't) in you string (like tabs). I checked and my suspicion were correct. the two characters between the date and time are 32 and 160 (32 + 128). Both are spaces. I have no idea how you got a charcter 160! This formula will work in either case. =SUMPRODUCT(--(TIMEVALUE(Trim(MID(H2:H17,FIND(" ",H2:H17),LEN(H2:H17))))=TIME(15,0,0)),--(TIMEVALUE(trim(MID(H2:H17,FIND(" ",H2:H17),LEN(H2:H17))))<=TIME(18,0,0))) This is what I used to find the character 160 =CODE(MID($H$2,11,1)) - return 160 =CODE(MID($H$2,12,1)) - return 32 =CODE(MID($H$2,13,1)) - return 53 the character 5 "Ken" wrote: Hi Joel, When I try the following it gives me a value error "A value used in the formula is of the wrong data type" =SUMPRODUCT(--(TIMEVALUE(MID(H2:H17,FIND(" ",H2:H17)+1,LEN(H2:H17)))=TIME(15,0,0)),--(TIMEVALUE(MID(H2:H17,FIND(" ",H2:H17)+1,LEN(H2:H17)))<=TIME(18,0,0))) Unfortunately, I cannot remove the spaces to make the formula simpler. What does the +1,LEN(H2:H17) portion do in this formula? Best Regards, "joel" wrote: Not sure if you date is in column A and time in Column B or both the time and date are in the same column Column B contain just time =SUMPRODUCT(--(B2:B17=TIME(15,0,0)),--(B2:B17<=TIME(18,0,0))) If the time and date are both in column A there is two spaces between the time and date which is causing a problem. I had to use the MID function to get past the extra space. If you could eliminate the extra space it would make the formula simplier. =SUMPRODUCT(--(TIMEVALUE(MID(A2:A17,FIND(" ",A2:A17)+1,LEN(A2:A17)))=TIME(15,0,0)),--(TIMEVALUE(MID(A2:A17,FIND(" ",A2:A17)+1,LEN(A2:A17)))<=TIME(18,0,0))) "Ken" wrote: Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
Hi T. Valko,
Am I the only one having probems with this web forum? I'm having problems expanding the discussion list and when I click on the names it shows blank. Anyway, I need to get back to the problem at hand. Your suggestion is perfect ... if I could get it to work. Can you help me understand what this portion of the formula is doing ",13,12"? When I tried your formula it returned a value of 0 and gave me the following error: "The formula in this cell refers to a range that has additional numbers adjacent to it." Appreciate the help! Ken "T. Valko" wrote: Assuming you dates/times are all in the same column and they're really TEXT strings: Use cells to hold your time boundaries: D2 = 3:00 PM E2 = 6:00 PM =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
Hi Joel,
I tried your suggested formula in the actual spreadsheet and in a blank spreadsheet with the data below with no success. I'm still getting the same error message even in the new spreadsheet using column A2-A17. Your thoughts? Best Regards, "joel" wrote: The MID function requires 3 parameters the last being the length. MID will return either the number of character to the end of the string, or the number of characters specified by the 3rd parameter (len in this case). I want to reutrn all the character to the end of the string so I could of specified 256 characters to make the code simplier. I choose the length to use the length of the string which will always be greater than the number of characters I really need. What the formula does is searches for the first space in the string and returns all the character after the space. the FIND() part of the function returns the character position of the space. I don't want the space in the final results so I add 1 (+1) so I ignore the space. I think the error is because of the 2 spaces. The TIMEVALUE isn't recognizing the the time with the extra space. Try this change. I assume your times are in cell H2 to H17. I tested both my old formula and new formula with your posted data and didn't get an error. It is possible there is some white characters (looks like spaces but aren't) in you string (like tabs). I checked and my suspicion were correct. the two characters between the date and time are 32 and 160 (32 + 128). Both are spaces. I have no idea how you got a charcter 160! This formula will work in either case. =SUMPRODUCT(--(TIMEVALUE(Trim(MID(H2:H17,FIND(" ",H2:H17),LEN(H2:H17))))=TIME(15,0,0)),--(TIMEVALUE(trim(MID(H2:H17,FIND(" ",H2:H17),LEN(H2:H17))))<=TIME(18,0,0))) This is what I used to find the character 160 =CODE(MID($H$2,11,1)) - return 160 =CODE(MID($H$2,12,1)) - return 32 =CODE(MID($H$2,13,1)) - return 53 the character 5 "Ken" wrote: Hi Joel, When I try the following it gives me a value error "A value used in the formula is of the wrong data type" =SUMPRODUCT(--(TIMEVALUE(MID(H2:H17,FIND(" ",H2:H17)+1,LEN(H2:H17)))=TIME(15,0,0)),--(TIMEVALUE(MID(H2:H17,FIND(" ",H2:H17)+1,LEN(H2:H17)))<=TIME(18,0,0))) Unfortunately, I cannot remove the spaces to make the formula simpler. What does the +1,LEN(H2:H17) portion do in this formula? Best Regards, "joel" wrote: Not sure if you date is in column A and time in Column B or both the time and date are in the same column Column B contain just time =SUMPRODUCT(--(B2:B17=TIME(15,0,0)),--(B2:B17<=TIME(18,0,0))) If the time and date are both in column A there is two spaces between the time and date which is causing a problem. I had to use the MID function to get past the extra space. If you could eliminate the extra space it would make the formula simplier. =SUMPRODUCT(--(TIMEVALUE(MID(A2:A17,FIND(" ",A2:A17)+1,LEN(A2:A17)))=TIME(15,0,0)),--(TIMEVALUE(MID(A2:A17,FIND(" ",A2:A17)+1,LEN(A2:A17)))<=TIME(18,0,0))) "Ken" wrote: Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
The 13,12 are the second and third arguments of the MID function. MID is a
standard Excel function and its syntax is explained (with examples) in Excel help. You probably don't need to worry about the warning "The formula in this cell refers to a range that has additional numbers adjacent to it.", providing that you are confident that you have included the correct cells in your formula. -- David Biddulph "Ken" wrote in message ... Hi T. Valko, Am I the only one having probems with this web forum? I'm having problems expanding the discussion list and when I click on the names it shows blank. Anyway, I need to get back to the problem at hand. Your suggestion is perfect ... if I could get it to work. Can you help me understand what this portion of the formula is doing ",13,12"? When I tried your formula it returned a value of 0 and gave me the following error: "The formula in this cell refers to a range that has additional numbers adjacent to it." Appreciate the help! Ken "T. Valko" wrote: Assuming you dates/times are all in the same column and they're really TEXT strings: Use cells to hold your time boundaries: D2 = 3:00 PM E2 = 6:00 PM =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
Hi T.Valko,
I was able to get your formula to work in a basic spreadsheet with just the data below. Thanks for your help. Now I need to find out why it won't work in my active spreadsheet. I'll keep you posted. Regards, Ken "T. Valko" wrote: Assuming you dates/times are all in the same column and they're really TEXT strings: Use cells to hold your time boundaries: D2 = 3:00 PM E2 = 6:00 PM =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
=SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2))
When I tried your formula it returned a value of 0 and gave me the following error: "The formula in this cell refers to a range that has additional numbers adjacent to it." Just ignore that. It's not an error. Can you help me understand what this portion of the formula is doing ",13,12"? Let's look at your data: 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM Your data is TEXT. It is not true Excel date/time values (which are really just numbers formatted to look like dates/times). Assuming the date *always* follows this format (which is how your sample data is) dd/mm/yyyy. So the date portion of the string is *always* 10 characters long. Then the date is followed by 2 whitespace characters. This means that the time portion of the string starts at character number 13. So we tell the MID function to start at character number 13 and extract the next 12 characters. The time portion of the string can be either 10 or 11 characters long. Extracting 12 characters ensures that we get the whole time portion extracted. So, this is what the MID function looks like when it extracts the time portion: MID ( "5:09:32 PM" "10:48:03 AM" ) The MID function returns a TEXT string even if it's a number so we have to convert that TEXT number into a numeric number. The double unary "--" coerces these TEXT numbers into the numeric values that represent the times: --"5:09:32 PM" = 0.714953703703704 --"10:48:03 AM" = 0.450034722222222 So, with D2 and E2 holding the time boundaries: D2 = 3:00:00 PM = 0.625 E2 = 6:00:00 PM = 0.75 The formula is comparing: 0.714953703703704; 0.450034722222222 = 0.625 0.714953703703704; 0.450034722222222 <= 0.75 Based on this limited sample the result is 1. 0.714953703703704 ("5:09:32 PM") is = 0.625 (3:00 PM) and <= 0.75 (6:00 PM) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Hi T. Valko, Am I the only one having probems with this web forum? I'm having problems expanding the discussion list and when I click on the names it shows blank. Anyway, I need to get back to the problem at hand. Your suggestion is perfect ... if I could get it to work. Can you help me understand what this portion of the formula is doing ",13,12"? When I tried your formula it returned a value of 0 and gave me the following error: "The formula in this cell refers to a range that has additional numbers adjacent to it." Appreciate the help! Ken "T. Valko" wrote: Assuming you dates/times are all in the same column and they're really TEXT strings: Use cells to hold your time boundaries: D2 = 3:00 PM E2 = 6:00 PM =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
Hi David,
I need to point the formula to a separate "Data (RAW)" tab in the formula, do you know how I would write it? If column A resides on a different tab named Data (Raw) how would I add it to the formula below? =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) Appreciate the help! Regards, "David Biddulph" wrote: The 13,12 are the second and third arguments of the MID function. MID is a standard Excel function and its syntax is explained (with examples) in Excel help. You probably don't need to worry about the warning "The formula in this cell refers to a range that has additional numbers adjacent to it.", providing that you are confident that you have included the correct cells in your formula. -- David Biddulph "Ken" wrote in message ... Hi T. Valko, Am I the only one having probems with this web forum? I'm having problems expanding the discussion list and when I click on the names it shows blank. Anyway, I need to get back to the problem at hand. Your suggestion is perfect ... if I could get it to work. Can you help me understand what this portion of the formula is doing ",13,12"? When I tried your formula it returned a value of 0 and gave me the following error: "The formula in this cell refers to a range that has additional numbers adjacent to it." Appreciate the help! Ken "T. Valko" wrote: Assuming you dates/times are all in the same column and they're really TEXT strings: Use cells to hold your time boundaries: D2 = 3:00 PM E2 = 6:00 PM =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
Try this:
=SUMPRODUCT(--(--MID('Data (RAW)'!A2:A17,13,12)=D2),--(--MID('Data (RAW)'!A2:A17,13,12)<=E2)) Does this data come from another application or the web? That would explain why the data is TEXT and has extra whitspace characters. If this is the case it would be easier to "clean" the data. Let me know if you are interested in this. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Hi David, I need to point the formula to a separate "Data (RAW)" tab in the formula, do you know how I would write it? If column A resides on a different tab named Data (Raw) how would I add it to the formula below? =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) Appreciate the help! Regards, "David Biddulph" wrote: The 13,12 are the second and third arguments of the MID function. MID is a standard Excel function and its syntax is explained (with examples) in Excel help. You probably don't need to worry about the warning "The formula in this cell refers to a range that has additional numbers adjacent to it.", providing that you are confident that you have included the correct cells in your formula. -- David Biddulph "Ken" wrote in message ... Hi T. Valko, Am I the only one having probems with this web forum? I'm having problems expanding the discussion list and when I click on the names it shows blank. Anyway, I need to get back to the problem at hand. Your suggestion is perfect ... if I could get it to work. Can you help me understand what this portion of the formula is doing ",13,12"? When I tried your formula it returned a value of 0 and gave me the following error: "The formula in this cell refers to a range that has additional numbers adjacent to it." Appreciate the help! Ken "T. Valko" wrote: Assuming you dates/times are all in the same column and they're really TEXT strings: Use cells to hold your time boundaries: D2 = 3:00 PM E2 = 6:00 PM =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
Thanks for taking the time to explain everything.... it makes sense now!
I'm going to try the augmented formula now. Best Regards, "T. Valko" wrote: =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) When I tried your formula it returned a value of 0 and gave me the following error: "The formula in this cell refers to a range that has additional numbers adjacent to it." Just ignore that. It's not an error. Can you help me understand what this portion of the formula is doing ",13,12"? Let's look at your data: 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM Your data is TEXT. It is not true Excel date/time values (which are really just numbers formatted to look like dates/times). Assuming the date *always* follows this format (which is how your sample data is) dd/mm/yyyy. So the date portion of the string is *always* 10 characters long. Then the date is followed by 2 whitespace characters. This means that the time portion of the string starts at character number 13. So we tell the MID function to start at character number 13 and extract the next 12 characters. The time portion of the string can be either 10 or 11 characters long. Extracting 12 characters ensures that we get the whole time portion extracted. So, this is what the MID function looks like when it extracts the time portion: MID ( "5:09:32 PM" "10:48:03 AM" ) The MID function returns a TEXT string even if it's a number so we have to convert that TEXT number into a numeric number. The double unary "--" coerces these TEXT numbers into the numeric values that represent the times: --"5:09:32 PM" = 0.714953703703704 --"10:48:03 AM" = 0.450034722222222 So, with D2 and E2 holding the time boundaries: D2 = 3:00:00 PM = 0.625 E2 = 6:00:00 PM = 0.75 The formula is comparing: 0.714953703703704; 0.450034722222222 = 0.625 0.714953703703704; 0.450034722222222 <= 0.75 Based on this limited sample the result is 1. 0.714953703703704 ("5:09:32 PM") is = 0.625 (3:00 PM) and <= 0.75 (6:00 PM) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Hi T. Valko, Am I the only one having probems with this web forum? I'm having problems expanding the discussion list and when I click on the names it shows blank. Anyway, I need to get back to the problem at hand. Your suggestion is perfect ... if I could get it to work. Can you help me understand what this portion of the formula is doing ",13,12"? When I tried your formula it returned a value of 0 and gave me the following error: "The formula in this cell refers to a range that has additional numbers adjacent to it." Appreciate the help! Ken "T. Valko" wrote: Assuming you dates/times are all in the same column and they're really TEXT strings: Use cells to hold your time boundaries: D2 = 3:00 PM E2 = 6:00 PM =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
The data comes from a client's call database so I guess you could say it
comes from another application. The formula you provided should work ... it all makes sense but I cannot get past the following error: "A value used in the formula is of the wrong data type" This is the formula that I'm using... =SUMPRODUCT(--(--MID('DATA (RAW)'!H2:H302,13,12)=D2),--(--MID('Data (RAW)'!H2:H302,13,12)<E2)) Do you see anything that I may be doing wrong? Regards, Ken "T. Valko" wrote: Try this: =SUMPRODUCT(--(--MID('Data (RAW)'!A2:A17,13,12)=D2),--(--MID('Data (RAW)'!A2:A17,13,12)<=E2)) Does this data come from another application or the web? That would explain why the data is TEXT and has extra whitspace characters. If this is the case it would be easier to "clean" the data. Let me know if you are interested in this. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Hi David, I need to point the formula to a separate "Data (RAW)" tab in the formula, do you know how I would write it? If column A resides on a different tab named Data (Raw) how would I add it to the formula below? =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) Appreciate the help! Regards, "David Biddulph" wrote: The 13,12 are the second and third arguments of the MID function. MID is a standard Excel function and its syntax is explained (with examples) in Excel help. You probably don't need to worry about the warning "The formula in this cell refers to a range that has additional numbers adjacent to it.", providing that you are confident that you have included the correct cells in your formula. -- David Biddulph "Ken" wrote in message ... Hi T. Valko, Am I the only one having probems with this web forum? I'm having problems expanding the discussion list and when I click on the names it shows blank. Anyway, I need to get back to the problem at hand. Your suggestion is perfect ... if I could get it to work. Can you help me understand what this portion of the formula is doing ",13,12"? When I tried your formula it returned a value of 0 and gave me the following error: "The formula in this cell refers to a range that has additional numbers adjacent to it." Appreciate the help! Ken "T. Valko" wrote: Assuming you dates/times are all in the same column and they're really TEXT strings: Use cells to hold your time boundaries: D2 = 3:00 PM E2 = 6:00 PM =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
So far, the formulas are all correct. It's your data that's the problem!
There may be additional unseen whitespace characters that we haven't been able to pinpoint. Instead of "farting around" and trying to get a convoluted formula to handle the data why don't you do this... There is a macro by MVP David McRitchie that will clean all these whitespaces from your data and in the process will convert your TEXT date/times to true Excel numeric date/times. I have put together a small sample file that demonstrates how this works. I copied/pasted your sample data into the sample file. I installed the macro and entered the formula in a cell. The formula currently returns an error because the data is TEXT. Once you run the macro the formula will return the correct result. For this to work... Macros must be enabled. To view the macro code: Hit ALT F11. This will open the VBE. Hit CTRL R. This will open the Project Explorer pane on the left side of the window. In the Project Explorer look for the file name VBAProject (xKen.xls). In the subdirectories of that file you'll see Module1 Double click Module1 The macro code will appear in the window on the right. After you've examined the code return to Excel, hit ALT Q To run the macro Select the range of data you want to clean, A2:A17. Goto the menu ToolsMacroMacros Select the TrimALL macro Click RUN Watch what happens. I do a lot of copy/pasting from the web and the data always contains these unseen whitespace characters. So I use this macro all the time. I use it so often I put a button on one of my toolbars and attached this macro to the button so it's easy to use. This macro is a real time (and headache) saver! Here's the sample file: xKen.xls 24kb http://cjoint.com/?dAv7mcvljA -- Biff Microsoft Excel MVP "Ken" wrote in message ... The data comes from a client's call database so I guess you could say it comes from another application. The formula you provided should work ... it all makes sense but I cannot get past the following error: "A value used in the formula is of the wrong data type" This is the formula that I'm using... =SUMPRODUCT(--(--MID('DATA (RAW)'!H2:H302,13,12)=D2),--(--MID('Data (RAW)'!H2:H302,13,12)<E2)) Do you see anything that I may be doing wrong? Regards, Ken "T. Valko" wrote: Try this: =SUMPRODUCT(--(--MID('Data (RAW)'!A2:A17,13,12)=D2),--(--MID('Data (RAW)'!A2:A17,13,12)<=E2)) Does this data come from another application or the web? That would explain why the data is TEXT and has extra whitspace characters. If this is the case it would be easier to "clean" the data. Let me know if you are interested in this. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Hi David, I need to point the formula to a separate "Data (RAW)" tab in the formula, do you know how I would write it? If column A resides on a different tab named Data (Raw) how would I add it to the formula below? =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) Appreciate the help! Regards, "David Biddulph" wrote: The 13,12 are the second and third arguments of the MID function. MID is a standard Excel function and its syntax is explained (with examples) in Excel help. You probably don't need to worry about the warning "The formula in this cell refers to a range that has additional numbers adjacent to it.", providing that you are confident that you have included the correct cells in your formula. -- David Biddulph "Ken" wrote in message ... Hi T. Valko, Am I the only one having probems with this web forum? I'm having problems expanding the discussion list and when I click on the names it shows blank. Anyway, I need to get back to the problem at hand. Your suggestion is perfect ... if I could get it to work. Can you help me understand what this portion of the formula is doing ",13,12"? When I tried your formula it returned a value of 0 and gave me the following error: "The formula in this cell refers to a range that has additional numbers adjacent to it." Appreciate the help! Ken "T. Valko" wrote: Assuming you dates/times are all in the same column and they're really TEXT strings: Use cells to hold your time boundaries: D2 = 3:00 PM E2 = 6:00 PM =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
Ok my findings are as follows:
I tried your macro and was able to get it working when selecting the data A2:A17 within your spreadsheet but when I tried it within my spreadsheet on the Data (Raw) it didn't do anything but blink. My Troubleshooting Steps... I copied the Data (Raw) tab from my spreadsheet to yours "dAv7mcvljA_xKen.xls" and ran the "TrimAll" macro but got the same result, it just blinked. I was able to get the formula you provided and the macro to work using the data when it was in a Text Format but when trying it on the client data it doesn't see it. I tried changing the cell format (right click Format Cells) to General but it just changes the date/time to a number (i.e. 02/09/2009 6:53:36 PM turns into 39853.78722) My guess is that the underlying data is in numerical format with no spaces which is why the macro doesn't do anything. When I right click the client data and select Format Cells it shows Custom Format: mm/dd/yyyy" "h\:mm\:ss AM/PM". Is there something that can be added to the formula to detect/recognize the time portion of the data in numerical format? Hopefully, what I wrote makes sense to you. Ken "T. Valko" wrote: So far, the formulas are all correct. It's your data that's the problem! There may be additional unseen whitespace characters that we haven't been able to pinpoint. Instead of "farting around" and trying to get a convoluted formula to handle the data why don't you do this... There is a macro by MVP David McRitchie that will clean all these whitespaces from your data and in the process will convert your TEXT date/times to true Excel numeric date/times. I have put together a small sample file that demonstrates how this works. I copied/pasted your sample data into the sample file. I installed the macro and entered the formula in a cell. The formula currently returns an error because the data is TEXT. Once you run the macro the formula will return the correct result. For this to work... Macros must be enabled. To view the macro code: Hit ALT F11. This will open the VBE. Hit CTRL R. This will open the Project Explorer pane on the left side of the window. In the Project Explorer look for the file name VBAProject (xKen.xls). In the subdirectories of that file you'll see Module1 Double click Module1 The macro code will appear in the window on the right. After you've examined the code return to Excel, hit ALT Q To run the macro Select the range of data you want to clean, A2:A17. Goto the menu ToolsMacroMacros Select the TrimALL macro Click RUN Watch what happens. I do a lot of copy/pasting from the web and the data always contains these unseen whitespace characters. So I use this macro all the time. I use it so often I put a button on one of my toolbars and attached this macro to the button so it's easy to use. This macro is a real time (and headache) saver! Here's the sample file: xKen.xls 24kb http://cjoint.com/?dAv7mcvljA -- Biff Microsoft Excel MVP "Ken" wrote in message ... The data comes from a client's call database so I guess you could say it comes from another application. The formula you provided should work ... it all makes sense but I cannot get past the following error: "A value used in the formula is of the wrong data type" This is the formula that I'm using... =SUMPRODUCT(--(--MID('DATA (RAW)'!H2:H302,13,12)=D2),--(--MID('Data (RAW)'!H2:H302,13,12)<E2)) Do you see anything that I may be doing wrong? Regards, Ken "T. Valko" wrote: Try this: =SUMPRODUCT(--(--MID('Data (RAW)'!A2:A17,13,12)=D2),--(--MID('Data (RAW)'!A2:A17,13,12)<=E2)) Does this data come from another application or the web? That would explain why the data is TEXT and has extra whitspace characters. If this is the case it would be easier to "clean" the data. Let me know if you are interested in this. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Hi David, I need to point the formula to a separate "Data (RAW)" tab in the formula, do you know how I would write it? If column A resides on a different tab named Data (Raw) how would I add it to the formula below? =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) Appreciate the help! Regards, "David Biddulph" wrote: The 13,12 are the second and third arguments of the MID function. MID is a standard Excel function and its syntax is explained (with examples) in Excel help. You probably don't need to worry about the warning "The formula in this cell refers to a range that has additional numbers adjacent to it.", providing that you are confident that you have included the correct cells in your formula. -- David Biddulph "Ken" wrote in message ... Hi T. Valko, Am I the only one having probems with this web forum? I'm having problems expanding the discussion list and when I click on the names it shows blank. Anyway, I need to get back to the problem at hand. Your suggestion is perfect ... if I could get it to work. Can you help me understand what this portion of the formula is doing ",13,12"? When I tried your formula it returned a value of 0 and gave me the following error: "The formula in this cell refers to a range that has additional numbers adjacent to it." Appreciate the help! Ken "T. Valko" wrote: Assuming you dates/times are all in the same column and they're really TEXT strings: Use cells to hold your time boundaries: D2 = 3:00 PM E2 = 6:00 PM =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
Can you send me a copy of your file with the original data? If it contains
sensitive data you can delete it. All I really need to see are these mysterous dates/times. If you want to do that my address is: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Ok my findings are as follows: I tried your macro and was able to get it working when selecting the data A2:A17 within your spreadsheet but when I tried it within my spreadsheet on the Data (Raw) it didn't do anything but blink. My Troubleshooting Steps... I copied the Data (Raw) tab from my spreadsheet to yours "dAv7mcvljA_xKen.xls" and ran the "TrimAll" macro but got the same result, it just blinked. I was able to get the formula you provided and the macro to work using the data when it was in a Text Format but when trying it on the client data it doesn't see it. I tried changing the cell format (right click Format Cells) to General but it just changes the date/time to a number (i.e. 02/09/2009 6:53:36 PM turns into 39853.78722) My guess is that the underlying data is in numerical format with no spaces which is why the macro doesn't do anything. When I right click the client data and select Format Cells it shows Custom Format: mm/dd/yyyy" "h\:mm\:ss AM/PM". Is there something that can be added to the formula to detect/recognize the time portion of the data in numerical format? Hopefully, what I wrote makes sense to you. Ken "T. Valko" wrote: So far, the formulas are all correct. It's your data that's the problem! There may be additional unseen whitespace characters that we haven't been able to pinpoint. Instead of "farting around" and trying to get a convoluted formula to handle the data why don't you do this... There is a macro by MVP David McRitchie that will clean all these whitespaces from your data and in the process will convert your TEXT date/times to true Excel numeric date/times. I have put together a small sample file that demonstrates how this works. I copied/pasted your sample data into the sample file. I installed the macro and entered the formula in a cell. The formula currently returns an error because the data is TEXT. Once you run the macro the formula will return the correct result. For this to work... Macros must be enabled. To view the macro code: Hit ALT F11. This will open the VBE. Hit CTRL R. This will open the Project Explorer pane on the left side of the window. In the Project Explorer look for the file name VBAProject (xKen.xls). In the subdirectories of that file you'll see Module1 Double click Module1 The macro code will appear in the window on the right. After you've examined the code return to Excel, hit ALT Q To run the macro Select the range of data you want to clean, A2:A17. Goto the menu ToolsMacroMacros Select the TrimALL macro Click RUN Watch what happens. I do a lot of copy/pasting from the web and the data always contains these unseen whitespace characters. So I use this macro all the time. I use it so often I put a button on one of my toolbars and attached this macro to the button so it's easy to use. This macro is a real time (and headache) saver! Here's the sample file: xKen.xls 24kb http://cjoint.com/?dAv7mcvljA -- Biff Microsoft Excel MVP "Ken" wrote in message ... The data comes from a client's call database so I guess you could say it comes from another application. The formula you provided should work ... it all makes sense but I cannot get past the following error: "A value used in the formula is of the wrong data type" This is the formula that I'm using... =SUMPRODUCT(--(--MID('DATA (RAW)'!H2:H302,13,12)=D2),--(--MID('Data (RAW)'!H2:H302,13,12)<E2)) Do you see anything that I may be doing wrong? Regards, Ken "T. Valko" wrote: Try this: =SUMPRODUCT(--(--MID('Data (RAW)'!A2:A17,13,12)=D2),--(--MID('Data (RAW)'!A2:A17,13,12)<=E2)) Does this data come from another application or the web? That would explain why the data is TEXT and has extra whitspace characters. If this is the case it would be easier to "clean" the data. Let me know if you are interested in this. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Hi David, I need to point the formula to a separate "Data (RAW)" tab in the formula, do you know how I would write it? If column A resides on a different tab named Data (Raw) how would I add it to the formula below? =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) Appreciate the help! Regards, "David Biddulph" wrote: The 13,12 are the second and third arguments of the MID function. MID is a standard Excel function and its syntax is explained (with examples) in Excel help. You probably don't need to worry about the warning "The formula in this cell refers to a range that has additional numbers adjacent to it.", providing that you are confident that you have included the correct cells in your formula. -- David Biddulph "Ken" wrote in message ... Hi T. Valko, Am I the only one having probems with this web forum? I'm having problems expanding the discussion list and when I click on the names it shows blank. Anyway, I need to get back to the problem at hand. Your suggestion is perfect ... if I could get it to work. Can you help me understand what this portion of the formula is doing ",13,12"? When I tried your formula it returned a value of 0 and gave me the following error: "The formula in this cell refers to a range that has additional numbers adjacent to it." Appreciate the help! Ken "T. Valko" wrote: Assuming you dates/times are all in the same column and they're really TEXT strings: Use cells to hold your time boundaries: D2 = 3:00 PM E2 = 6:00 PM =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
Sure thing...
I sent you a test email, just respond back to it so I know I have the right addy. Appreciate the help! Regards, Ken "T. Valko" wrote: Can you send me a copy of your file with the original data? If it contains sensitive data you can delete it. All I really need to see are these mysterous dates/times. If you want to do that my address is: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Ok my findings are as follows: I tried your macro and was able to get it working when selecting the data A2:A17 within your spreadsheet but when I tried it within my spreadsheet on the Data (Raw) it didn't do anything but blink. My Troubleshooting Steps... I copied the Data (Raw) tab from my spreadsheet to yours "dAv7mcvljA_xKen.xls" and ran the "TrimAll" macro but got the same result, it just blinked. I was able to get the formula you provided and the macro to work using the data when it was in a Text Format but when trying it on the client data it doesn't see it. I tried changing the cell format (right click Format Cells) to General but it just changes the date/time to a number (i.e. 02/09/2009 6:53:36 PM turns into 39853.78722) My guess is that the underlying data is in numerical format with no spaces which is why the macro doesn't do anything. When I right click the client data and select Format Cells it shows Custom Format: mm/dd/yyyy" "h\:mm\:ss AM/PM". Is there something that can be added to the formula to detect/recognize the time portion of the data in numerical format? Hopefully, what I wrote makes sense to you. Ken "T. Valko" wrote: So far, the formulas are all correct. It's your data that's the problem! There may be additional unseen whitespace characters that we haven't been able to pinpoint. Instead of "farting around" and trying to get a convoluted formula to handle the data why don't you do this... There is a macro by MVP David McRitchie that will clean all these whitespaces from your data and in the process will convert your TEXT date/times to true Excel numeric date/times. I have put together a small sample file that demonstrates how this works. I copied/pasted your sample data into the sample file. I installed the macro and entered the formula in a cell. The formula currently returns an error because the data is TEXT. Once you run the macro the formula will return the correct result. For this to work... Macros must be enabled. To view the macro code: Hit ALT F11. This will open the VBE. Hit CTRL R. This will open the Project Explorer pane on the left side of the window. In the Project Explorer look for the file name VBAProject (xKen.xls). In the subdirectories of that file you'll see Module1 Double click Module1 The macro code will appear in the window on the right. After you've examined the code return to Excel, hit ALT Q To run the macro Select the range of data you want to clean, A2:A17. Goto the menu ToolsMacroMacros Select the TrimALL macro Click RUN Watch what happens. I do a lot of copy/pasting from the web and the data always contains these unseen whitespace characters. So I use this macro all the time. I use it so often I put a button on one of my toolbars and attached this macro to the button so it's easy to use. This macro is a real time (and headache) saver! Here's the sample file: xKen.xls 24kb http://cjoint.com/?dAv7mcvljA -- Biff Microsoft Excel MVP "Ken" wrote in message ... The data comes from a client's call database so I guess you could say it comes from another application. The formula you provided should work ... it all makes sense but I cannot get past the following error: "A value used in the formula is of the wrong data type" This is the formula that I'm using... =SUMPRODUCT(--(--MID('DATA (RAW)'!H2:H302,13,12)=D2),--(--MID('Data (RAW)'!H2:H302,13,12)<E2)) Do you see anything that I may be doing wrong? Regards, Ken "T. Valko" wrote: Try this: =SUMPRODUCT(--(--MID('Data (RAW)'!A2:A17,13,12)=D2),--(--MID('Data (RAW)'!A2:A17,13,12)<=E2)) Does this data come from another application or the web? That would explain why the data is TEXT and has extra whitspace characters. If this is the case it would be easier to "clean" the data. Let me know if you are interested in this. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Hi David, I need to point the formula to a separate "Data (RAW)" tab in the formula, do you know how I would write it? If column A resides on a different tab named Data (Raw) how would I add it to the formula below? =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) Appreciate the help! Regards, "David Biddulph" wrote: The 13,12 are the second and third arguments of the MID function. MID is a standard Excel function and its syntax is explained (with examples) in Excel help. You probably don't need to worry about the warning "The formula in this cell refers to a range that has additional numbers adjacent to it.", providing that you are confident that you have included the correct cells in your formula. -- David Biddulph "Ken" wrote in message ... Hi T. Valko, Am I the only one having probems with this web forum? I'm having problems expanding the discussion list and when I click on the names it shows blank. Anyway, I need to get back to the problem at hand. Your suggestion is perfect ... if I could get it to work. Can you help me understand what this portion of the formula is doing ",13,12"? When I tried your formula it returned a value of 0 and gave me the following error: "The formula in this cell refers to a range that has additional numbers adjacent to it." Appreciate the help! Ken "T. Valko" wrote: Assuming you dates/times are all in the same column and they're really TEXT strings: Use cells to hold your time boundaries: D2 = 3:00 PM E2 = 6:00 PM =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
OK, received the email and replied.
-- Biff Microsoft Excel MVP "Ken" wrote in message ... Sure thing... I sent you a test email, just respond back to it so I know I have the right addy. Appreciate the help! Regards, Ken "T. Valko" wrote: Can you send me a copy of your file with the original data? If it contains sensitive data you can delete it. All I really need to see are these mysterous dates/times. If you want to do that my address is: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Ok my findings are as follows: I tried your macro and was able to get it working when selecting the data A2:A17 within your spreadsheet but when I tried it within my spreadsheet on the Data (Raw) it didn't do anything but blink. My Troubleshooting Steps... I copied the Data (Raw) tab from my spreadsheet to yours "dAv7mcvljA_xKen.xls" and ran the "TrimAll" macro but got the same result, it just blinked. I was able to get the formula you provided and the macro to work using the data when it was in a Text Format but when trying it on the client data it doesn't see it. I tried changing the cell format (right click Format Cells) to General but it just changes the date/time to a number (i.e. 02/09/2009 6:53:36 PM turns into 39853.78722) My guess is that the underlying data is in numerical format with no spaces which is why the macro doesn't do anything. When I right click the client data and select Format Cells it shows Custom Format: mm/dd/yyyy" "h\:mm\:ss AM/PM". Is there something that can be added to the formula to detect/recognize the time portion of the data in numerical format? Hopefully, what I wrote makes sense to you. Ken "T. Valko" wrote: So far, the formulas are all correct. It's your data that's the problem! There may be additional unseen whitespace characters that we haven't been able to pinpoint. Instead of "farting around" and trying to get a convoluted formula to handle the data why don't you do this... There is a macro by MVP David McRitchie that will clean all these whitespaces from your data and in the process will convert your TEXT date/times to true Excel numeric date/times. I have put together a small sample file that demonstrates how this works. I copied/pasted your sample data into the sample file. I installed the macro and entered the formula in a cell. The formula currently returns an error because the data is TEXT. Once you run the macro the formula will return the correct result. For this to work... Macros must be enabled. To view the macro code: Hit ALT F11. This will open the VBE. Hit CTRL R. This will open the Project Explorer pane on the left side of the window. In the Project Explorer look for the file name VBAProject (xKen.xls). In the subdirectories of that file you'll see Module1 Double click Module1 The macro code will appear in the window on the right. After you've examined the code return to Excel, hit ALT Q To run the macro Select the range of data you want to clean, A2:A17. Goto the menu ToolsMacroMacros Select the TrimALL macro Click RUN Watch what happens. I do a lot of copy/pasting from the web and the data always contains these unseen whitespace characters. So I use this macro all the time. I use it so often I put a button on one of my toolbars and attached this macro to the button so it's easy to use. This macro is a real time (and headache) saver! Here's the sample file: xKen.xls 24kb http://cjoint.com/?dAv7mcvljA -- Biff Microsoft Excel MVP "Ken" wrote in message ... The data comes from a client's call database so I guess you could say it comes from another application. The formula you provided should work ... it all makes sense but I cannot get past the following error: "A value used in the formula is of the wrong data type" This is the formula that I'm using... =SUMPRODUCT(--(--MID('DATA (RAW)'!H2:H302,13,12)=D2),--(--MID('Data (RAW)'!H2:H302,13,12)<E2)) Do you see anything that I may be doing wrong? Regards, Ken "T. Valko" wrote: Try this: =SUMPRODUCT(--(--MID('Data (RAW)'!A2:A17,13,12)=D2),--(--MID('Data (RAW)'!A2:A17,13,12)<=E2)) Does this data come from another application or the web? That would explain why the data is TEXT and has extra whitspace characters. If this is the case it would be easier to "clean" the data. Let me know if you are interested in this. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Hi David, I need to point the formula to a separate "Data (RAW)" tab in the formula, do you know how I would write it? If column A resides on a different tab named Data (Raw) how would I add it to the formula below? =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) Appreciate the help! Regards, "David Biddulph" wrote: The 13,12 are the second and third arguments of the MID function. MID is a standard Excel function and its syntax is explained (with examples) in Excel help. You probably don't need to worry about the warning "The formula in this cell refers to a range that has additional numbers adjacent to it.", providing that you are confident that you have included the correct cells in your formula. -- David Biddulph "Ken" wrote in message ... Hi T. Valko, Am I the only one having probems with this web forum? I'm having problems expanding the discussion list and when I click on the names it shows blank. Anyway, I need to get back to the problem at hand. Your suggestion is perfect ... if I could get it to work. Can you help me understand what this portion of the formula is doing ",13,12"? When I tried your formula it returned a value of 0 and gave me the following error: "The formula in this cell refers to a range that has additional numbers adjacent to it." Appreciate the help! Ken "T. Valko" wrote: Assuming you dates/times are all in the same column and they're really TEXT strings: Use cells to hold your time boundaries: D2 = 3:00 PM E2 = 6:00 PM =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Looking for help... I manage a closed ticket report and I need to find a formula that will find all tickets in a certain time range. For instance, in the list below, how would I write a formula to count all tickets that came in between 3:00:00 PM and 6:00:00 PM (The date is not important)? H TIME_ACTION_TAKEN 02/10/2009 5:09:32 PM 02/02/2009 10:48:03 AM 02/04/2009 8:46:08 AM 02/02/2009 4:29:31 PM 02/02/2009 6:59:39 PM 02/02/2009 6:24:48 PM 02/03/2009 1:46:16 AM 02/06/2009 3:49:10 PM 02/04/2009 10:40:35 AM 02/05/2009 2:04:34 PM 02/05/2009 2:11:17 PM 02/10/2009 6:18:17 PM 02/04/2009 7:56:45 PM 02/04/2009 8:27:22 PM 02/05/2009 7:03:33 PM 02/13/2009 9:51:25 AM Appreciate the help! Regards, Ken |
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
Hi Biff,
My findings/update was sent to you this morning. I've made some progress. Regards, Ken "T. Valko" wrote: OK, received the email and replied. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Sure thing... I sent you a test email, just respond back to it so I know I have the right addy. Appreciate the help! Regards, Ken "T. Valko" wrote: Can you send me a copy of your file with the original data? If it contains sensitive data you can delete it. All I really need to see are these mysterous dates/times. If you want to do that my address is: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Ok my findings are as follows: I tried your macro and was able to get it working when selecting the data A2:A17 within your spreadsheet but when I tried it within my spreadsheet on the Data (Raw) it didn't do anything but blink. My Troubleshooting Steps... I copied the Data (Raw) tab from my spreadsheet to yours "dAv7mcvljA_xKen.xls" and ran the "TrimAll" macro but got the same result, it just blinked. I was able to get the formula you provided and the macro to work using the data when it was in a Text Format but when trying it on the client data it doesn't see it. I tried changing the cell format (right click Format Cells) to General but it just changes the date/time to a number (i.e. 02/09/2009 6:53:36 PM turns into 39853.78722) My guess is that the underlying data is in numerical format with no spaces which is why the macro doesn't do anything. When I right click the client data and select Format Cells it shows Custom Format: mm/dd/yyyy" "h\:mm\:ss AM/PM". Is there something that can be added to the formula to detect/recognize the time portion of the data in numerical format? Hopefully, what I wrote makes sense to you. Ken "T. Valko" wrote: So far, the formulas are all correct. It's your data that's the problem! There may be additional unseen whitespace characters that we haven't been able to pinpoint. Instead of "farting around" and trying to get a convoluted formula to handle the data why don't you do this... There is a macro by MVP David McRitchie that will clean all these whitespaces from your data and in the process will convert your TEXT date/times to true Excel numeric date/times. I have put together a small sample file that demonstrates how this works. I copied/pasted your sample data into the sample file. I installed the macro and entered the formula in a cell. The formula currently returns an error because the data is TEXT. Once you run the macro the formula will return the correct result. For this to work... Macros must be enabled. To view the macro code: Hit ALT F11. This will open the VBE. Hit CTRL R. This will open the Project Explorer pane on the left side of the window. In the Project Explorer look for the file name VBAProject (xKen.xls). In the subdirectories of that file you'll see Module1 Double click Module1 The macro code will appear in the window on the right. After you've examined the code return to Excel, hit ALT Q To run the macro Select the range of data you want to clean, A2:A17. Goto the menu ToolsMacroMacros Select the TrimALL macro Click RUN Watch what happens. I do a lot of copy/pasting from the web and the data always contains these unseen whitespace characters. So I use this macro all the time. I use it so often I put a button on one of my toolbars and attached this macro to the button so it's easy to use. This macro is a real time (and headache) saver! Here's the sample file: xKen.xls 24kb http://cjoint.com/?dAv7mcvljA -- Biff Microsoft Excel MVP "Ken" wrote in message ... The data comes from a client's call database so I guess you could say it comes from another application. The formula you provided should work ... it all makes sense but I cannot get past the following error: "A value used in the formula is of the wrong data type" This is the formula that I'm using... =SUMPRODUCT(--(--MID('DATA (RAW)'!H2:H302,13,12)=D2),--(--MID('Data (RAW)'!H2:H302,13,12)<E2)) Do you see anything that I may be doing wrong? Regards, Ken "T. Valko" wrote: Try this: =SUMPRODUCT(--(--MID('Data (RAW)'!A2:A17,13,12)=D2),--(--MID('Data (RAW)'!A2:A17,13,12)<=E2)) Does this data come from another application or the web? That would explain why the data is TEXT and has extra whitspace characters. If this is the case it would be easier to "clean" the data. Let me know if you are interested in this. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Hi David, I need to point the formula to a separate "Data (RAW)" tab in the formula, do you know how I would write it? If column A resides on a different tab named Data (Raw) how would I add it to the formula below? =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) Appreciate the help! Regards, "David Biddulph" wrote: The 13,12 are the second and third arguments of the MID function. MID is a standard Excel function and its syntax is explained (with examples) in Excel help. You probably don't need to worry about the warning "The formula in this cell refers to a range that has additional numbers adjacent to it.", providing that you are confident that you have included the correct cells in your formula. -- David Biddulph "Ken" wrote in message ... Hi T. Valko, Am I the only one having probems with this web forum? I'm having problems expanding the discussion list and when I click on the names it shows blank. Anyway, I need to get back to the problem at hand. Your suggestion is perfect ... if I could get it to work. Can you help me understand what this portion of the formula is doing ",13,12"? When I tried your formula it returned a value of 0 and gave me the following error: "The formula in this cell refers to a range that has additional numbers adjacent to it." Appreciate the help! Ken "T. Valko" wrote: Assuming you dates/times are all in the same column and they're really TEXT strings: Use cells to hold your time boundaries: D2 = 3:00 PM E2 = 6:00 PM =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) -- Biff Microsoft Excel MVP |
#21
Posted to microsoft.public.excel.misc
|
|||
|
|||
Looking for formula
If anyone is still following this thread...
Problem solved! The OP's data *already* was true Excel dates/times. When this data was pasted into the forum and then copy/pasted into a test file it was pasted as TEXT which lead those of us that replied to craft formulas based on the data being text (which it was in *our* test files). The working formula: =SUMPRODUCT(--(MOD(range,1)=x),--(MOD(range,1)<x)) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Hi Biff, My findings/update was sent to you this morning. I've made some progress. Regards, Ken "T. Valko" wrote: OK, received the email and replied. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Sure thing... I sent you a test email, just respond back to it so I know I have the right addy. Appreciate the help! Regards, Ken "T. Valko" wrote: Can you send me a copy of your file with the original data? If it contains sensitive data you can delete it. All I really need to see are these mysterous dates/times. If you want to do that my address is: xl can help at comcast period net Remove "can" and change the obvious. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Ok my findings are as follows: I tried your macro and was able to get it working when selecting the data A2:A17 within your spreadsheet but when I tried it within my spreadsheet on the Data (Raw) it didn't do anything but blink. My Troubleshooting Steps... I copied the Data (Raw) tab from my spreadsheet to yours "dAv7mcvljA_xKen.xls" and ran the "TrimAll" macro but got the same result, it just blinked. I was able to get the formula you provided and the macro to work using the data when it was in a Text Format but when trying it on the client data it doesn't see it. I tried changing the cell format (right click Format Cells) to General but it just changes the date/time to a number (i.e. 02/09/2009 6:53:36 PM turns into 39853.78722) My guess is that the underlying data is in numerical format with no spaces which is why the macro doesn't do anything. When I right click the client data and select Format Cells it shows Custom Format: mm/dd/yyyy" "h\:mm\:ss AM/PM". Is there something that can be added to the formula to detect/recognize the time portion of the data in numerical format? Hopefully, what I wrote makes sense to you. Ken "T. Valko" wrote: So far, the formulas are all correct. It's your data that's the problem! There may be additional unseen whitespace characters that we haven't been able to pinpoint. Instead of "farting around" and trying to get a convoluted formula to handle the data why don't you do this... There is a macro by MVP David McRitchie that will clean all these whitespaces from your data and in the process will convert your TEXT date/times to true Excel numeric date/times. I have put together a small sample file that demonstrates how this works. I copied/pasted your sample data into the sample file. I installed the macro and entered the formula in a cell. The formula currently returns an error because the data is TEXT. Once you run the macro the formula will return the correct result. For this to work... Macros must be enabled. To view the macro code: Hit ALT F11. This will open the VBE. Hit CTRL R. This will open the Project Explorer pane on the left side of the window. In the Project Explorer look for the file name VBAProject (xKen.xls). In the subdirectories of that file you'll see Module1 Double click Module1 The macro code will appear in the window on the right. After you've examined the code return to Excel, hit ALT Q To run the macro Select the range of data you want to clean, A2:A17. Goto the menu ToolsMacroMacros Select the TrimALL macro Click RUN Watch what happens. I do a lot of copy/pasting from the web and the data always contains these unseen whitespace characters. So I use this macro all the time. I use it so often I put a button on one of my toolbars and attached this macro to the button so it's easy to use. This macro is a real time (and headache) saver! Here's the sample file: xKen.xls 24kb http://cjoint.com/?dAv7mcvljA -- Biff Microsoft Excel MVP "Ken" wrote in message ... The data comes from a client's call database so I guess you could say it comes from another application. The formula you provided should work ... it all makes sense but I cannot get past the following error: "A value used in the formula is of the wrong data type" This is the formula that I'm using... =SUMPRODUCT(--(--MID('DATA (RAW)'!H2:H302,13,12)=D2),--(--MID('Data (RAW)'!H2:H302,13,12)<E2)) Do you see anything that I may be doing wrong? Regards, Ken "T. Valko" wrote: Try this: =SUMPRODUCT(--(--MID('Data (RAW)'!A2:A17,13,12)=D2),--(--MID('Data (RAW)'!A2:A17,13,12)<=E2)) Does this data come from another application or the web? That would explain why the data is TEXT and has extra whitspace characters. If this is the case it would be easier to "clean" the data. Let me know if you are interested in this. -- Biff Microsoft Excel MVP "Ken" wrote in message ... Hi David, I need to point the formula to a separate "Data (RAW)" tab in the formula, do you know how I would write it? If column A resides on a different tab named Data (Raw) how would I add it to the formula below? =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) Appreciate the help! Regards, "David Biddulph" wrote: The 13,12 are the second and third arguments of the MID function. MID is a standard Excel function and its syntax is explained (with examples) in Excel help. You probably don't need to worry about the warning "The formula in this cell refers to a range that has additional numbers adjacent to it.", providing that you are confident that you have included the correct cells in your formula. -- David Biddulph "Ken" wrote in message ... Hi T. Valko, Am I the only one having probems with this web forum? I'm having problems expanding the discussion list and when I click on the names it shows blank. Anyway, I need to get back to the problem at hand. Your suggestion is perfect ... if I could get it to work. Can you help me understand what this portion of the formula is doing ",13,12"? When I tried your formula it returned a value of 0 and gave me the following error: "The formula in this cell refers to a range that has additional numbers adjacent to it." Appreciate the help! Ken "T. Valko" wrote: Assuming you dates/times are all in the same column and they're really TEXT strings: Use cells to hold your time boundaries: D2 = 3:00 PM E2 = 6:00 PM =SUMPRODUCT(--(--MID(A2:A17,13,12)=D2),--(--MID(A2:A17,13,12)<=E2)) -- Biff Microsoft Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|