Blank Date fields

[LOUBOYBEAR]

Hi all,
i have the following formula in a cell:
=IF(W2="","",IF(W2="vacant","",VLOOKUP(A2,Emp_in_Pos,8,0)))

if a date is present in "Emp_in_Pos" if puts it in the cell.

My issue is that I want to use this data in a pivot table, but the cells are not recognised as dates as I cant filter it.

Any ideas on how to make it work??
thanks, Sam

 

[Sal Paradise]

Convert your values to dates.

Without knowing more, there's no way to help you do that.

 

[Andrew Poulsom]

Try using this formula:

=IF(W2="",0,IF(W2="vacant",0,VLOOKUP(A2,Emp_in_Pos,8,0)))

and format the cells as eg dd/mm/yyyy;;; to suppress the zeroes.

 

[markmzz]

Try this:

Note: like the Andrew's suggestion with another formula way.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Date</td><td style="text-align: center;;">Value</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">5</td><td style="text-align: center;;">4</td><td style="text-align: center;;">2/1/2011</td><td style="text-align: center;;">19</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">5</td><td style="text-align: center;;">vacant</td><td style="text-align: center;;"></td><td style="text-align: center;;">55</td><td style="text-align: center;;"></td><td style="background-color: #C5D9F1;;">Sum of Value</td><td style="background-color: #C5D9F1;;">Date</td><td style="text-align: right;background-color: #C5D9F1;;"></td><td style="text-align: right;background-color: #C5D9F1;;"></td><td style="text-align: right;background-color: #C5D9F1;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">1</td><td style="text-align: center;;">6</td><td style="text-align: center;;">3/1/2011</td><td style="text-align: center;;">76</td><td style="text-align: center;;"></td><td style="text-align: right;background-color: #C5D9F1;;"></td><td style="background-color: #C5D9F1;;">Jan</td><td style="background-color: #C5D9F1;;">Feb</td><td style="background-color: #C5D9F1;;">Mar</td><td style="background-color: #C5D9F1;;">Total  </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">1</td><td style="text-align: center;;">vacant</td><td style="text-align: center;;"></td><td style="text-align: center;;">52</td><td style="text-align: center;;"></td><td style="background-color: #C5D9F1;;">Total</td><td style="text-align: right;background-color: #C5D9F1;;">334</td><td style="text-align: right;background-color: #C5D9F1;;">35</td><td style="text-align: right;background-color: #C5D9F1;;">184</td><td style="text-align: right;background-color: #C5D9F1;;">553</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2/1/2011</td><td style="text-align: center;;">16</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">1</td><td style="text-align: center;;">vacant</td><td style="text-align: center;;"></td><td style="text-align: center;;">37</td><td style="text-align: center;;"></td><td style="font-weight: bold;background-color: #C5D9F1;;">PivotTable</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1/1/2011</td><td style="text-align: center;;">44</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">38</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">1</td><td style="text-align: center;;">vacant</td><td style="text-align: center;;"></td><td style="text-align: center;;">13</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3/1/2011</td><td style="text-align: center;;">52</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td><td style="text-align: center;;">3/1/2011</td><td style="text-align: center;;">56</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">61</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">11</td><td style="text-align: center;;"></td><td style="font-weight: bold;background-color: #C5D9F1;;">Column Date format</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">1</td><td style="text-align: center;;">vacant</td><td style="text-align: center;;"></td><td style="text-align: center;;">23</td><td style="text-align: center;;"></td><td style="font-weight: bold;background-color: #C5D9F1;;">m/d/yyyy;;</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">B2={"","vacant"}</font>),0,VLOOKUP(<font color="Red">A2,myname,8,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">myname</th><td style="text-align:left">=Sheet1!$M$1:$T$6</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[Andrew Poulsom]

You will need to group by month and year, otherwise Jan will include the zeroes.

 

[markmzz]

You will need to group by month and year, otherwise Jan will include the zeroes.

Andrew,

Thanks. I forgot that.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Date</td><td style="text-align: center;;">Value</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">5</td><td style="text-align: center;;">4</td><td style="text-align: center;;">2/1/2011</td><td style="text-align: center;;">19</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">5</td><td style="text-align: center;;">vacant</td><td style="text-align: center;;"></td><td style="text-align: center;;">55</td><td style="text-align: center;;"></td><td style="background-color: #C5D9F1;;">Sum of Value</td><td style="background-color: #C5D9F1;;">Anos</td><td style="background-color: #C5D9F1;;">Date</td><td style="text-align: right;background-color: #C5D9F1;;"></td><td style="text-align: right;background-color: #C5D9F1;;"></td><td style="text-align: right;background-color: #C5D9F1;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">1</td><td style="text-align: center;;">6</td><td style="text-align: center;;">3/1/2011</td><td style="text-align: center;;">76</td><td style="text-align: center;;"></td><td style="text-align: right;background-color: #C5D9F1;;"></td><td style="text-align: right;background-color: #C5D9F1;;">1900</td><td style="text-align: right;background-color: #C5D9F1;;">2011</td><td style="text-align: right;background-color: #C5D9F1;;"></td><td style="text-align: right;background-color: #C5D9F1;;"></td><td style="background-color: #C5D9F1;;">Total  </td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">1</td><td style="text-align: center;;">vacant</td><td style="text-align: center;;"></td><td style="text-align: center;;">52</td><td style="text-align: center;;"></td><td style="text-align: right;background-color: #C5D9F1;;"></td><td style="background-color: #C5D9F1;;">Jan</td><td style="background-color: #C5D9F1;;">Jan</td><td style="background-color: #C5D9F1;;">Feb</td><td style="background-color: #C5D9F1;;">Mar</td><td style="text-align: right;background-color: #C5D9F1;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2/1/2011</td><td style="text-align: center;;">16</td><td style="text-align: center;;"></td><td style="background-color: #C5D9F1;;">Total</td><td style="text-align: right;background-color: #C5D9F1;;">290</td><td style="text-align: right;background-color: #C5D9F1;;">44</td><td style="text-align: right;background-color: #C5D9F1;;">35</td><td style="text-align: right;background-color: #C5D9F1;;">184</td><td style="text-align: right;background-color: #C5D9F1;;">553</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">1</td><td style="text-align: center;;">vacant</td><td style="text-align: center;;"></td><td style="text-align: center;;">37</td><td style="text-align: center;;"></td><td style="font-weight: bold;background-color: #C5D9F1;;">PivotTable</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1/1/2011</td><td style="text-align: center;;">44</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">38</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">1</td><td style="text-align: center;;">vacant</td><td style="text-align: center;;"></td><td style="text-align: center;;">13</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3/1/2011</td><td style="text-align: center;;">52</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td><td style="text-align: center;;">3/1/2011</td><td style="text-align: center;;">56</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">61</td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">11</td><td style="text-align: center;;"></td><td style="font-weight: bold;background-color: #C5D9F1;;">Column Date format</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">1</td><td style="text-align: center;;">vacant</td><td style="text-align: center;;"></td><td style="text-align: center;;">23</td><td style="text-align: center;;"></td><td style="font-weight: bold;background-color: #C5D9F1;;">m/d/yyyy;;</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td><td style="text-align: center;;">*****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">B2={"","vacant"}</font>),0,VLOOKUP(<font color="Red">A2,myname,8,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">myname</th><td style="text-align:left">=Sheet1!$M$1:$T$6</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[LOUBOYBEAR]

Try using this formula:

=IF(W2="",0,IF(W2="vacant",0,VLOOKUP(A2,Emp_in_Pos,8,0)))

and format the cells as eg dd/mm/yyyy;;; to suppress the zeroes.

hi Andrew, thanks for your response, how do I format the cells as dd/mm/yyyy;;;;? I dont have that format in the date or custom categories of the format cells menu.

what does the ;;;; do?

 

[markmzz]

I'm not Andrew, but I will try to help.

1. Select the cells you want to format.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
2. <o></o>
for 2003 - on the Format menu, click cells, and then click the number tab. <o></o>
for 2007-2010 – on the Home tab, click in the arrow of Number format, choose More number format.
<o></o>
3. in the categories list, click a category that is similar to the one you want, and then set its built-in formats so that they are similar to those you want. (You will not alter the built-in format; you'll be creating a copy to customization.)
<o></o>
4. in the categories list, click Custom.
<o></o>
5. in the type box, edit the number format codes to create the desired format.

You can specify up to four sections of format codes. The formatting codes, separated by semicolons, define the formats for positive numbers, negative numbers, zero values, and text, in that order. If you specify only two sections, the first is used for positive numbers and zeros, and the second is used for negative numbers. If you specify only one section, it will be used for all numbers. If you skip a section, include the ending semicolon for that section.<o></o>

Markmzz