Good Afternoon All,
I am working on a project that I need to claculate the number of people working during a date range. The time period for an example would be 07/01/2011 thru 07/31/2011. A sample of data is below.
<TABLE style="WIDTH: 149pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=199><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=64>Start</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=71>Term</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64>Unit</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #b8cce4; HEIGHT: 12.75pt; BORDER-TOP: white 0.5pt solid; BORDER-RIGHT: white 0.5pt solid" class=xl65 height=17 align=right>5/3/2006</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: white 0.5pt solid; BORDER-RIGHT: white 0.5pt solid" class=xl65 align=right>12/3/2007</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: white 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl66>002</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: white 0.5pt solid" class=xl67 height=17 align=right>4/20/2005</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #ece9d8; BORDER-RIGHT: white 0.5pt solid" class=xl67 align=right>1/0/1900</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>004</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #b8cce4; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: white 0.5pt solid" class=xl69 height=17 align=right>6/27/2006</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #ece9d8; BORDER-RIGHT: white 0.5pt solid" class=xl69 align=right>1/0/1900</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70>002</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: white 0.5pt solid" class=xl67 height=17 align=right>6/27/2006</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #ece9d8; BORDER-RIGHT: white 0.5pt solid" class=xl67 align=right>11/20/2007</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>002</TD></TR></TBODY></TABLE>
Ok on the term column the date of 1/0/1900 is a null field that I can not get to dump as blank. So technically there should be no date in there. This data is coming from a query into excel. Also I know the Unit column is shown as text and should be a number. Again the database is storing and dumping as a text field.
So I need the return to be that with the time period of 07/01/2011 thru 07/31/2011 I have one person in unti 004 and one in 002.
I hope this make sense i have been running in circles on this one and can not seem to come up with the formula to calculate it right.
Thank you for any help.
I am working on a project that I need to claculate the number of people working during a date range. The time period for an example would be 07/01/2011 thru 07/31/2011. A sample of data is below.
<TABLE style="WIDTH: 149pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=199><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17 width=64>Start</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=71>Term</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64>Unit</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #b8cce4; HEIGHT: 12.75pt; BORDER-TOP: white 0.5pt solid; BORDER-RIGHT: white 0.5pt solid" class=xl65 height=17 align=right>5/3/2006</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: white 0.5pt solid; BORDER-RIGHT: white 0.5pt solid" class=xl65 align=right>12/3/2007</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: white 0.5pt solid; BORDER-RIGHT: #ece9d8" class=xl66>002</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: white 0.5pt solid" class=xl67 height=17 align=right>4/20/2005</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #ece9d8; BORDER-RIGHT: white 0.5pt solid" class=xl67 align=right>1/0/1900</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>004</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #b8cce4; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: white 0.5pt solid" class=xl69 height=17 align=right>6/27/2006</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #ece9d8; BORDER-RIGHT: white 0.5pt solid" class=xl69 align=right>1/0/1900</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #b8cce4; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl70>002</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: white 0.5pt solid" class=xl67 height=17 align=right>6/27/2006</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #ece9d8; BORDER-RIGHT: white 0.5pt solid" class=xl67 align=right>11/20/2007</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #dbe5f1; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>002</TD></TR></TBODY></TABLE>
Ok on the term column the date of 1/0/1900 is a null field that I can not get to dump as blank. So technically there should be no date in there. This data is coming from a query into excel. Also I know the Unit column is shown as text and should be a number. Again the database is storing and dumping as a text field.
So I need the return to be that with the time period of 07/01/2011 thru 07/31/2011 I have one person in unti 004 and one in 002.
I hope this make sense i have been running in circles on this one and can not seem to come up with the formula to calculate it right.
Thank you for any help.