Formula with Dates and Blank Cells

[Charlie C]

Hello, I am in the process of creating a spreadsheet that includes detailed information re: "incidents" that occur on a specific date. The date format that I have chosen to use is 14-Feb-11. I was able to create hidden cells that extract the Day, Month, Year and Weekday of the occurence, as shown below:


<TABLE style="WIDTH: 369pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=492 border=0 x:str><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" width=98><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" span=3 width=87><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_160075 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 74pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=98 height=17 x:num="40653">20-Apr-11</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=87 x:num x:fmla="=MONTH(A1)">4</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=87 x:num x:fmla="=DAY(A1)">20</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=87 x:num x:fmla="=YEAR(A1)">2011</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 100pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=133 x:num x:fmla="=WEEKDAY(A1)">4</TD></TR></TBODY></TABLE>

My problem is when the date cell is blank (which it will always be until the user enters the occurence date) the hidden cells display the following:

<TABLE style="WIDTH: 295pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=394 border=0 x:str><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" span=3 width=87><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4864" width=133><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=87 height=17 x:num>1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=87 x:num>0</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=87 x:num>1900</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 100pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=133 x:num>7</TD></TR></TBODY></TABLE>

This impacts the stats and graphs I am trying to pull from the spreadsheet.

I hope this makes sense and would appreciate any help re: what formula can I use to have the hidden cells display blanks when there is no date. so that my statistics are not impacted.

 

[llywelyn]

You could try doing an if statement along the lines of =if(a1=0,"#n/a",a1).

This will then put #n/a in the cell wich your graph won't show. I have used that a few time where I am tring to show actuals againt budget. It stops you having the sales line drop to zero on your graph.

 

[texasalynn]

welcome to MrExcel board...

...too slow I had the same answer as llywelyn

 

[llywelyn]

Great minds......

 

[Charlie C]

Thank you for the quick reply. Sometimes something so simple can cause a lot of grief.

Your suggesiton worked. I look forward to using this forum again in the future.

Cheers,
Charlie