Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PM time in formula...
I have the following array formula:
{=SUM((B$1:B$100=DATEVALUE("3/10/2007"))*((C$1:C$100)=TIMEVALUE("6:00 AM")))}. Works fine for AM time values, but when formula runs across a PM time value in the range, its ignored - any clues as to why this is happending? Thank you. Mark :) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PM time in formula...
any clues as to why this is happending?
Because the formula is written to check only a single specific time: 6:00 AM. That's what you asked for in your original post. There are better ways to do this. See your original post. Biff "NWO" wrote in message ... I have the following array formula: {=SUM((B$1:B$100=DATEVALUE("3/10/2007"))*((C$1:C$100)=TIMEVALUE("6:00 AM")))}. Works fine for AM time values, but when formula runs across a PM time value in the range, its ignored - any clues as to why this is happending? Thank you. Mark :) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PM time in formula...
Thnak you T.
I understand, but the formula dosn't count properly when I replace the 6:00 AM with 6:00 PM - still a single value (i.e. give me the total count of all those entires where the date is "3/10/2007" and time is "6:00 PM"). Mark "T. Valko" wrote: any clues as to why this is happending? Because the formula is written to check only a single specific time: 6:00 AM. That's what you asked for in your original post. There are better ways to do this. See your original post. Biff "NWO" wrote in message ... I have the following array formula: {=SUM((B$1:B$100=DATEVALUE("3/10/2007"))*((C$1:C$100)=TIMEVALUE("6:00 AM")))}. Works fine for AM time values, but when formula runs across a PM time value in the range, its ignored - any clues as to why this is happending? Thank you. Mark :) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PM time in formula...
Thnak you again. I should have clarified in my original post. Bottom line
is the fomrula returns a 0 count when a PM value is used in the fomrula and there is a coresponding PM value in the time range - why? Mark :) "NWO" wrote: Thnak you T. I understand, but the formula dosn't count properly when I replace the 6:00 AM with 6:00 PM - still a single value (i.e. give me the total count of all those entires where the date is "3/10/2007" and time is "6:00 PM"). Mark "T. Valko" wrote: any clues as to why this is happending? Because the formula is written to check only a single specific time: 6:00 AM. That's what you asked for in your original post. There are better ways to do this. See your original post. Biff "NWO" wrote in message ... I have the following array formula: {=SUM((B$1:B$100=DATEVALUE("3/10/2007"))*((C$1:C$100)=TIMEVALUE("6:00 AM")))}. Works fine for AM time values, but when formula runs across a PM time value in the range, its ignored - any clues as to why this is happending? Thank you. Mark :) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PM time in formula...
Ok....
You don't need to use an array formula to do this. Sometimes people forget that when they edit an array formula it must be re-entered as an array. Since this can be done without an array formula that will eliminate at least that much of the potential problems. You'd be better off using cells to hold the criteria. Then, you only need to change the cells instead of editing the formula. So: A1 = some date like 3/10/2007 A2 = some time like 6:00 PM =SUMPRODUCT(--(B1:B10=A1),--(C1:C10=A2)) If this worked on 6:00 AM but does not work on 6:00 PM then there is something going on with your times (or even the dates). Are you sure they're all times/dates? Although in the formula you posted the Datevalue/Timevalue function would convert TEXT date/times into NUMERIC date/times. Are these date/times calculated? Are they filled by a series fill or drag copied? I've seen situations where 6:00 PM does not equal 6:00 PM due to series filling and drag copying that causes rounding issues. Biff "NWO" wrote in message ... Thnak you T. I understand, but the formula dosn't count properly when I replace the 6:00 AM with 6:00 PM - still a single value (i.e. give me the total count of all those entires where the date is "3/10/2007" and time is "6:00 PM"). Mark "T. Valko" wrote: any clues as to why this is happending? Because the formula is written to check only a single specific time: 6:00 AM. That's what you asked for in your original post. There are better ways to do this. See your original post. Biff "NWO" wrote in message ... I have the following array formula: {=SUM((B$1:B$100=DATEVALUE("3/10/2007"))*((C$1:C$100)=TIMEVALUE("6:00 AM")))}. Works fine for AM time values, but when formula runs across a PM time value in the range, its ignored - any clues as to why this is happending? Thank you. Mark :) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PM time in formula...
Works fine on my computer
"NWO" wrote: I have the following array formula: {=SUM((B$1:B$100=DATEVALUE("3/10/2007"))*((C$1:C$100)=TIMEVALUE("6:00 AM")))}. Works fine for AM time values, but when formula runs across a PM time value in the range, its ignored - any clues as to why this is happending? Thank you. Mark :) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PM time in formula...
In article ,
"T. Valko" wrote: You don't need to use an array formula to do this. Well, technically SUMPRODUCT() *is* an array function (one reason you can't use entire columns pre-XL07). You just don't have to use CTRL-SHIFT-ENTER with it. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PM time in formula...
Thank you.
I went back an manually re-entered the time values in my range (they were filled) and the fomrula now works. Thank you again. Mark :) "T. Valko" wrote: Ok.... You don't need to use an array formula to do this. Sometimes people forget that when they edit an array formula it must be re-entered as an array. Since this can be done without an array formula that will eliminate at least that much of the potential problems. You'd be better off using cells to hold the criteria. Then, you only need to change the cells instead of editing the formula. So: A1 = some date like 3/10/2007 A2 = some time like 6:00 PM =SUMPRODUCT(--(B1:B10=A1),--(C1:C10=A2)) If this worked on 6:00 AM but does not work on 6:00 PM then there is something going on with your times (or even the dates). Are you sure they're all times/dates? Although in the formula you posted the Datevalue/Timevalue function would convert TEXT date/times into NUMERIC date/times. Are these date/times calculated? Are they filled by a series fill or drag copied? I've seen situations where 6:00 PM does not equal 6:00 PM due to series filling and drag copying that causes rounding issues. Biff "NWO" wrote in message ... Thnak you T. I understand, but the formula dosn't count properly when I replace the 6:00 AM with 6:00 PM - still a single value (i.e. give me the total count of all those entires where the date is "3/10/2007" and time is "6:00 PM"). Mark "T. Valko" wrote: any clues as to why this is happending? Because the formula is written to check only a single specific time: 6:00 AM. That's what you asked for in your original post. There are better ways to do this. See your original post. Biff "NWO" wrote in message ... I have the following array formula: {=SUM((B$1:B$100=DATEVALUE("3/10/2007"))*((C$1:C$100)=TIMEVALUE("6:00 AM")))}. Works fine for AM time values, but when formula runs across a PM time value in the range, its ignored - any clues as to why this is happending? Thank you. Mark :) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PM time in formula...
Glad to hear you got it to work!
Depending on how you want to fill a series of times it's best to use an incremental formula but I've even seen this method cause rounding issues. The only guaranteed way to avoid this issue is to manually enter the series which can be a real PITA. Biff "NWO" wrote in message ... Thank you. I went back an manually re-entered the time values in my range (they were filled) and the fomrula now works. Thank you again. Mark :) "T. Valko" wrote: Ok.... You don't need to use an array formula to do this. Sometimes people forget that when they edit an array formula it must be re-entered as an array. Since this can be done without an array formula that will eliminate at least that much of the potential problems. You'd be better off using cells to hold the criteria. Then, you only need to change the cells instead of editing the formula. So: A1 = some date like 3/10/2007 A2 = some time like 6:00 PM =SUMPRODUCT(--(B1:B10=A1),--(C1:C10=A2)) If this worked on 6:00 AM but does not work on 6:00 PM then there is something going on with your times (or even the dates). Are you sure they're all times/dates? Although in the formula you posted the Datevalue/Timevalue function would convert TEXT date/times into NUMERIC date/times. Are these date/times calculated? Are they filled by a series fill or drag copied? I've seen situations where 6:00 PM does not equal 6:00 PM due to series filling and drag copying that causes rounding issues. Biff "NWO" wrote in message ... Thnak you T. I understand, but the formula dosn't count properly when I replace the 6:00 AM with 6:00 PM - still a single value (i.e. give me the total count of all those entires where the date is "3/10/2007" and time is "6:00 PM"). Mark "T. Valko" wrote: any clues as to why this is happending? Because the formula is written to check only a single specific time: 6:00 AM. That's what you asked for in your original post. There are better ways to do this. See your original post. Biff "NWO" wrote in message ... I have the following array formula: {=SUM((B$1:B$100=DATEVALUE("3/10/2007"))*((C$1:C$100)=TIMEVALUE("6:00 AM")))}. Works fine for AM time values, but when formula runs across a PM time value in the range, its ignored - any clues as to why this is happending? Thank you. Mark :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
template or formula for start time -finish time -total hours ple | New Users to Excel | |||
Help with time formula so the time will not change. | Excel Discussion (Misc queries) | |||
How do I set up a formula on a time sheet to calculate time in 1/. | Excel Discussion (Misc queries) | |||
Formula for time | Excel Discussion (Misc queries) | |||
Time / Formula to look at time difference | Excel Worksheet Functions |