Custom Cell Formatting

T

Tom

I set up a custom cell format to bring forward (1.) the sum a cell, which is
a DATE and (2.) the sum of a different cell, which is a %. However, the
program is taking the luxury of converting the -% data to a positive %
number. Why is this, and how can I correct it?

The Custom cell format is as follows: m/dd/yy;0.00%

Any help would be greatly appreciated.
 
R

RagDyeR

I still wondering how you're getting *any other* display besides a date
format!

I can't duplicate your results with the custom format you posted.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

I set up a custom cell format to bring forward (1.) the sum a cell, which is
a DATE and (2.) the sum of a different cell, which is a %. However, the
program is taking the luxury of converting the -% data to a positive %
number. Why is this, and how can I correct it?

The Custom cell format is as follows: m/dd/yy;0.00%

Any help would be greatly appreciated.
 
T

Tom

The information that is being brought forward is coming from one cell with a
"LOOKUP" formula in it (the DATE field) and the other cell has an "OFFSET"
formula (the % field) in it.
I did make a mistake in my displayed cell format, it should read m/d/yy not
m/dd/yy.

I appreciate your attempt to assist in this matter.

Tom
 
F

Fred Smith

RagDyeR:

When formatting a cell, it doesn't matter where the data came from (Vlookup,
Offset, or any other formula). It only matters what the resulting data is.

I'm also having a hard time understanding why you would want a date *and* a
percentage in the same cell, but regardless, here are some pointers.

Excel is displaying your negative percentages without a sign because you told it
to. The format m/dd/yy;0.00% means "if the number in the cell is positive, use a
format of m/dd/yy; it it's negative, use 0.00%". The semicolon is what separates
the format for positive numbers from negative numbers. It's most commonly used
to display negative numbers in red.

In your case, you want something different. If the number is a percentage, you
want the 0.00% format. If it's a date, you want the date format. You now have to
tell Excel how to tell the difference. Fortunately, percentages are very low
numbers, and dates are much higher numbers. Depending on the highest percentage
you could ever have, you could, for example, tell Excel to format everything
less than 10 (ie, 1000%) as a percentage, and everything else as a date, with
the following:

[<10]0.00%;m/dd/yy
 
T

Tom

BINGO - That worked pefectly.

Thank you, so very much for your help!!!!

Tom

Fred Smith said:
RagDyeR:

When formatting a cell, it doesn't matter where the data came from (Vlookup,
Offset, or any other formula). It only matters what the resulting data is.

I'm also having a hard time understanding why you would want a date *and* a
percentage in the same cell, but regardless, here are some pointers.

Excel is displaying your negative percentages without a sign because you told it
to. The format m/dd/yy;0.00% means "if the number in the cell is positive, use a
format of m/dd/yy; it it's negative, use 0.00%". The semicolon is what separates
the format for positive numbers from negative numbers. It's most commonly used
to display negative numbers in red.

In your case, you want something different. If the number is a percentage, you
want the 0.00% format. If it's a date, you want the date format. You now have to
tell Excel how to tell the difference. Fortunately, percentages are very low
numbers, and dates are much higher numbers. Depending on the highest percentage
you could ever have, you could, for example, tell Excel to format everything
less than 10 (ie, 1000%) as a percentage, and everything else as a date, with
the following:

[<10]0.00%;m/dd/yy

--
Regards,
Fred


Tom said:
The information that is being brought forward is coming from one cell with a
"LOOKUP" formula in it (the DATE field) and the other cell has an "OFFSET"
formula (the % field) in it.
I did make a mistake in my displayed cell format, it should read m/d/yy not
m/dd/yy.

I appreciate your attempt to assist in this matter.

Tom
Click to expand...
Click to expand...
 
Top