MrExcel Publishing
Your One Stop for Excel Tips & Solutions

formatting dates in a cell


Posted by sean on March 28, 2001 8:07 AM

hi there,

I data like this: 26/04/00 in a cell. I want to format it so it looks like: 26-Apr. I can go to cell format and hit Number tab and then date and choose from the list but the cell still keeps the year 00 on it. I went to Custom Number and clicked on dd/mm but the data still remained 26/04/00. how can I correct this?

thanks


Posted by Mark W. on March 28, 2001 8:21 AM

By chance is this a text value rather than a datevalue?

Posted by sean on March 28, 2001 8:29 AM

I'm not sure....they are data points downloaded from a thermograph...temperature is recorded every 45 minutes for 252 days. Its all downloaded into a .bin file and converted to ASCII and then inputted into Excel. How can you tell if its text or number? Does ceonverting it to ASCII do this/??

thanks

Posted by Mark W. on March 28, 2001 9:11 AM

Using an empty cell, enter the formula, =ISTEXT(ref), where "ref" is a cell containing one of your "dates". If thIS formula returns TRUE you're dealing with a text value rather than a date. If so, you'll need to convert it prior to applying your desired date value.

Posted by sean on March 28, 2001 9:44 AM

thanks...I tried it and it's text...I figured out how to convert them and now they are numbers.

Another question....about MS Word. I have a document that when I close Word completely, it backgound saves the Normal.dot file. No virus scanner pick up anything but the file crashes when I save it after any modification...any suggestion? I know this isn't a WORD forum but I figured I'd ask anyway.

thanks Using an empty cell, enter the formula, =ISTEXT(ref), where "ref" is a cell containing one of your "dates". If thIS formula returns TRUE you're dealing with a text value rather than a date. If so, you'll need to convert it prior to applying your desired date value. : I'm not sure....they are data points downloaded from a thermograph...temperature is recorded every 45 minutes for 252 days. Its all downloaded into a .bin file and converted to ASCII and then inputted into Excel. How can you tell if its text or number? Does ceonverting it to ASCII do this/?? :

Posted by Mark W. on March 28, 2001 11:11 AM

Sean, I don't use Word or that much. You might try
this forum:

http://bbs.elementkjournals.com/bbs/wwwthreads.pl?action=list&Board=o2k

Posted by sean on March 28, 2001 11:43 AM

Thanks Mark....you've been very helpful. MS Office has a lot a quarks and I find discussion groups the best way to sort things out.

thanks again.

sean