Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
count between two dates
I have the following formula.
=IF($A6="","",COUNTIF(MAIN!$B$10:$B$4999,"" &$A6 & "")) How do I count between two dates in cells: P3 and R3? -- capt |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
count between two dates
Try something like this:
=IF(COUNT(P3,R3)<2,"",SUMPRODUCT((MAIN!$B$10:$B$49 99=P3)*(MAIN!$B$10:$B$4999<=R3))) where P3, R3 houses the start and end dates -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "capt" wrote: I have the following formula. =IF($A6="","",COUNTIF(MAIN!$B$10:$B$4999,"" &$A6 & "")) How do I count between two dates in cells: P3 and R3? -- capt |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
count between two dates
From your post below - I wasn't really sure what you were looking for.... If you're looking for the # of days between 2 dates, you can just subtract Date 1 from Date 2. If you're looking for the number of working days, enable the Analysis Toolpack Addin (tools - Addins - Anaysis Toolpack) and then use the networkdays() function to calculate the number of days, excluding weekends, between the 2 dates. If you are looking for the number of rows of data between 2 dates on your spreadsheet, the following will work: Assume you have dates in Cells A1: A6 as follows: 1/1/2007 2/1/2007 2/15/2007 3/1/2007 4/9/2007 12/31/2007 In cells B1 and B2 I put the 2 dates you want to search between 2/1/2007 3/1/2007 This formula counts the # rows between the 2 dates entered: =COUNTIF($A$1:$A$6,"="&B1)-COUNTIF($A$1:$A$6,""&B2) On Dec 10, 9:55 am, capt wrote: I have the following formula. =IF($A6="","",COUNTIF(MAIN!$B$10:$B$4999,"" &$A6 & "")) How do I count between two dates in cells: P3 and R3? -- capt |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
count between two dates
The formula you show counts how many times the B range in the Main worksheet
match the value in A6 of the sheet where the formula resides. If A6 is empty you see a blank cell. Now tell us what you mean by: How do I count between two dates in cells: P3 and R3? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "capt" wrote in message ... I have the following formula. =IF($A6="","",COUNTIF(MAIN!$B$10:$B$4999,"" &$A6 & "")) How do I count between two dates in cells: P3 and R3? -- capt |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
count between two dates
In cells P3 and R3 I insert a date From and To. The B range is where I find
the Names of clients. cell A6 will carry one of the names of the client. I after to count how many entries there are of a client, in col B, between two dates. Thank you -- capt "Bernard Liengme" wrote: The formula you show counts how many times the B range in the Main worksheet match the value in A6 of the sheet where the formula resides. If A6 is empty you see a blank cell. Now tell us what you mean by: How do I count between two dates in cells: P3 and R3? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "capt" wrote in message ... I have the following formula. =IF($A6="","",COUNTIF(MAIN!$B$10:$B$4999,"" &$A6 & "")) How do I count between two dates in cells: P3 and R3? -- capt |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
count between two dates
And where are the dates relative to the B range? We need to know what you
data looks like. -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "capt" wrote in message ... In cells P3 and R3 I insert a date From and To. The B range is where I find the Names of clients. cell A6 will carry one of the names of the client. I after to count how many entries there are of a client, in col B, between two dates. Thank you -- capt "Bernard Liengme" wrote: The formula you show counts how many times the B range in the Main worksheet match the value in A6 of the sheet where the formula resides. If A6 is empty you see a blank cell. Now tell us what you mean by: How do I count between two dates in cells: P3 and R3? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "capt" wrote in message ... I have the following formula. =IF($A6="","",COUNTIF(MAIN!$B$10:$B$4999,"" &$A6 & "")) How do I count between two dates in cells: P3 and R3? -- capt |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
count between two dates
Below is a small part of the table. I need to count each client (col B)
relative to the dates. ie between 5-sep-07 and 10-sep-07 client A = 4 client B = 3 A B C D Date Client Hours Total 5-Sep-07 A 5:20 5:20 7-Sep-07 B 5:15 10:35 8-Sep-07 A 10:05 20:40 9-Sep-07 B 0:35 21:15 9-Sep-07 B 7:40 28:55 9-Sep-07 A 0:15 29:10 10-Sep-07 A 0:15 29:25 and so on..... -- capt "Bernard Liengme" wrote: And where are the dates relative to the B range? We need to know what you data looks like. -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "capt" wrote in message ... In cells P3 and R3 I insert a date From and To. The B range is where I find the Names of clients. cell A6 will carry one of the names of the client. I after to count how many entries there are of a client, in col B, between two dates. Thank you -- capt "Bernard Liengme" wrote: The formula you show counts how many times the B range in the Main worksheet match the value in A6 of the sheet where the formula resides. If A6 is empty you see a blank cell. Now tell us what you mean by: How do I count between two dates in cells: P3 and R3? best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "capt" wrote in message ... I have the following formula. =IF($A6="","",COUNTIF(MAIN!$B$10:$B$4999,"" &$A6 & "")) How do I count between two dates in cells: P3 and R3? -- capt |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
count between two dates
Something like this should work for you:
In say, T3: =IF(COUNTA(P3,R3,S3)<3,"",SUMPRODUCT((MAIN!$A$10:$ A$4999=P3)*(MAIN!$A$10:$A$4999<=R3)*(MAIN!$B$10:$ B$4999=S3)) where P3, R3 houses the start and end dates, S3 contains the client, eg: A Adapt the ranges to suit -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "capt" wrote: Below is a small part of the table. I need to count each client (col B) relative to the dates. ie between 5-sep-07 and 10-sep-07 client A = 4 client B = 3 A B C D Date Client Hours Total 5-Sep-07 A 5:20 5:20 7-Sep-07 B 5:15 10:35 8-Sep-07 A 10:05 20:40 9-Sep-07 B 0:35 21:15 9-Sep-07 B 7:40 28:55 9-Sep-07 A 0:15 29:10 10-Sep-07 A 0:15 29:25 and so on..... |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
count between two dates
Brilliant Max it works fine.
Thank you -- capt "Max" wrote: Something like this should work for you: In say, T3: =IF(COUNTA(P3,R3,S3)<3,"",SUMPRODUCT((MAIN!$A$10:$ A$4999=P3)*(MAIN!$A$10:$A$4999<=R3)*(MAIN!$B$10:$ B$4999=S3)) where P3, R3 houses the start and end dates, S3 contains the client, eg: A Adapt the ranges to suit -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "capt" wrote: Below is a small part of the table. I need to count each client (col B) relative to the dates. ie between 5-sep-07 and 10-sep-07 client A = 4 client B = 3 A B C D Date Client Hours Total 5-Sep-07 A 5:20 5:20 7-Sep-07 B 5:15 10:35 8-Sep-07 A 10:05 20:40 9-Sep-07 B 0:35 21:15 9-Sep-07 B 7:40 28:55 9-Sep-07 A 0:15 29:10 10-Sep-07 A 0:15 29:25 and so on..... |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
count between two dates
welcome, glad it helped.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "capt" wrote in message ... Brilliant Max it works fine. Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count dates | Excel Worksheet Functions | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
count dates | New Users to Excel | |||
How do a count dates? | Excel Discussion (Misc queries) | |||
How do a count dates? | Excel Discussion (Misc queries) |