Excel custom cell formatting

macsauce · 03-04-2014 04:16PM #1

If this is not the right forum please move to a more appropriate one.

I am creating a dashboard in excel which is pull information from a couple of different sources in sharepoint. Because one of these is a date i have had to use the date format for the cells in the Dashboard otherwise excell does not pull/display the info correctly. When a cell is blank the dashboard displays the date as 00-Jan-00 which is distracting and confusing for people looking at it. Is there a way that i can format the cell to display the date but when the date is 00-Jan-00 to display the cell as blank?

I would really appreciate any help given on this as i have spent a good bit of time trying to figure it our and my excel skills are not that great.

Thanks

Tokyo · 03-04-2014 06:08PM

I would imagine that rather than using the "Date" formatting, it would be better to use a custom format with rules to eliminate a zero date.

What format is the raw date coming in from sharepoint??

macsauce · 03-04-2014 07:36PM

Thanks for the reply mike_ie. The cell is being pulled from an up formatted cell in another workbook that auto formats the date to dd mmm yy. If there is not entry in the source cell then it shows 00-Jan-00 in the destination cell. Would you by any chance know how to write that custom format to display the cell as empty when that date appears? Thanks for your help.

Halfbaker · 04-04-2014 11:45AM

How about an IF,ELSE function? Something like

=IF(ISBLANK(A1),"",A1)

Excel custom cell formatting

Comments