Display negative dates as blank

[davystuff]

Good Motning all
I have a materials procurement spreadsheet in which are a lot of date calcs based on "Today" date, as I have copied all the formulae and conditional formatting to the rest of the worksheet without entering the required dates, as I don't know what they will be until the time arrives, I am getting three columns of #######, can this ##### be displayed as a blank cell??
Please put me out of my misery so that I can sleep tonight.
Many Thanks
davystuff

 

[Richard Schollar]

Hi Davy

Highlight your column and go Format>Cells and on the Number tab under Category select Custom. Then in the Type box type the following:

[<0]"";dd mm yyyy

and press OK. You may have to change the "dd mm yyyy" bit, depending on how you want the date to be displayed if it is valid.

Hope this helps!

Bets regards

Richard

 

[davystuff]

Hi Richard,
Superb, worked a treat for the neg dates and also other numbers, now I have another problem:
On a sumif calc how can I get it to return a value for between a set of numbers i.e. IF E2 is between 0 and 365 then return 0 as the value.
I have tried all sorts but with no luck, is this possible without putting in 365 separate +IF's.
Again Many Thanks
Regards
Davy

 

[Richard Schollar]

Davy

You'll have to explain exactly what you are trying to do here - post a few starting values and hoped for result. Why would you want to return a value of 0 to a SUMIF? The SUMIF would then sum to zero, surely?

Richard

 

[davystuff]

Soz Richard,
I just got it to work using +IF(E2<0>365,3)
It seems to work OK so far, if I get into more diffs I will get back.
It would be too difficult to explain ,I would have to upload the sheet or an example, which I may need to do.
Again Many Thanks
regards
Davy

 

[davystuff]

Hi Richard,
I am sitting on the two brain cell left, my last reply was rubbish, I have got it sorted now. I wanted a result of zero because of the date calcs and conditional formatting where a certain cell will cgange colour when a date is approached, equalled and passed. There is most likely a shorter way but antways it works now.
Cheers all
Davy

 

[davystuff]

Hi Richard,
I am sitting on the two brain cells left, my last reply was rubbish, I have got it sorted now. I wanted a result of zero because of the date calcs and conditional formatting where certain cells will change colour when certain dates are approached, equalled and passed. There is most likely a shorter way but anyways it works now.
Cheers all
Davy