Cell format changes automatically

[vestax22]

Hi, I have a cell with a standard format inside it. I have a long formula inside it that seems to change the format of the cell to a date format.

Is it possible for a formula to change the format of a cell?

Any help would be appreciated

 

[SkipVought]

Hi,

You have a cell with a General format? (nothing called a Standard Format)

What value does your formula return when the format is changed to date?


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[vestax22]

It returns the time value of the numeric value.



Example if the value returned is 2 the cell will show :

00-01-02 00:00

if I press f9 and f2 I see the vaule 2


Any suggestions?

 

[xlbo]

do you have anything in the formula like :
Text(A1,"dd/mm/yy&quot ???
Formulae cannot change the format of a cell

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[SkipVought]

Actually its displaying a date and time:

jan 2 1900 12:00 AM

Geoff is right -- something in your formula is making Excel believe that you are calculating a date.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

or the format has been set to custom or something. Are you SURE that it isn't in date format ??
If you select the ell then go Format>Cells and choose the number tab...what do you see ??

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[vestax22]

Here is the formula :

=(IF(OR(OR(AND(ISTEXT(F56);D56=""D56="ACT"D56="EST"1;IF(AND(ISTEXT(F56);(D56)="REV"-1;0)))+H55-IF(ISTEXT(A55);0;IF(MONTH(A56)>MONTH(A55);J55;0))A55);J55;0))


Well there it is.

If there are errors inside the formula its because I translated from french to english. My Excel is french.

Any suggestions?

BTW Geof the format has been set to custom but if I set it to general it will automatically change on refresh.

 

[SkipVought]

What is the Custom Formatthat is set????

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[vestax22]

yy-mm-dd hh:mm

 

[vestax22]

It's ok now. I changed the format to numeric and it doesn't change anymore. Thx for the help guys!

 

[xlbo]

so it was the format of the cell all along

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244