I’m trying to enter a date into an excel spreadsheet but it is being displayed as a julian day number (number of days since 1/1/1900). I’ve tried using the format->cells->number->category->Date->type*3/14/2001 option but that doesn’t change anything.
I can enter date in other worksheets in this same workbook and they display just fine. However, no matter what cell I enter a date on this particular spreadsheet it is changed to JDN format.
Anyone know how to change this???
Can you use the format painter to copy the format from one page to another?
No expert here, but the only way I’ve found to fix these types of weird issues is to clear the cell and reformat/re-enter data.
Edit | Clear | All to clear the cell. Then set your date display format, and re-enter the date.
Good luck. This kind of quirk is such a pita.
I’ve had luck with creating new columns with =value(A2) (or whatever original cell is), formatting it the way I want it, then copying and pasting special (format and value), back, onto the original cells.
Then go delete the new columns.
A tip I believe I picked up here.
Where do I find format painter? I’ve not heard of that.
redtail23I tried to clear all but the format remained the same. As I said, everywhere in the worksheet it is formatting dates this way.
jonesj2205Unfortunately the entire spreadsheet is behaving this way, although I could probably copy it to another spreadsheet within the workbook.
I did figure out that if I change the cell format to text and then reenter my date all is good. The only issue is that I have hundreds of rows and when you change the cells to text it changes the cell content to the number of days since 1/1/1900 and I lose the date in m/d/yy format. I could work around that but again, kind of a pita, as redtail23 put it.
There’s an icon in the taskbar that looks like a paintbrush. Click a cell that’s formatted correctly, click the paintbrush, now click the cell you want to reformat.