Date format in Excel97

Topic

New Reply

I have a simple spreadsheet for my personal cashbook, including some fairly simple formulas. The first column (A ) is formatted as a date all the way down and not used in any formula. However the only way I can enter the date I want is as an =date(yy,mm,dd) function. In other spreadsheets I can format the column as a date, enter the date as dd/mm/yy and bingo, I get what I want ie in the format I have set for the column.
Can anyone suggest what I have done in my cashbook spreadsheet to force me to enter the full formula each time? Please…

Reply | Quote

Replies

Have you tried Format/Cells/Custom and entering
yy/mm/dd
or
dd/mm/yy
depending on your preference, instead of using the Date format?
With either of the above, I find I can simply enter (for todays date) 9-1 or 09-01 and get 01/01/09 or 09/01/01
(Local here is UK – dd/mm/yy)

Reply | Quote

Thanks, but no go. What you suggest and the “normal” method I tried (use Format cells as date dd-mmm-yy and set Control Panel global date format as dd/mm/yy and then just type 12/01/01 expecting the result 12-Jan-01) works happily in other spreadsheets on my machine but not this one! In this sheet it gives me 12-Jan-00! I cannot figure out why! I can only assume I have some underlying format on this column which I cannot overwrite. Cheers, Ken

Reply | Quote

Are you saying the year is wrong?
Is the date set correctly on your PC – (no offense meant )
Otherwise, do you want to attach a (blank) copy to analyse?
And can you just confirm:
You want to literally enter 11/01/01 and have the result 11-Jan-01

Reply | Quote

No. In the example I quoted it appeared that way. However I just tried a few other examples and it is all over the place – often giving me 00-Jan-00 which I think indicates that it is trying to read my entered data as a numeric string and then convert it to a date. My real puzzle is that in a new spread sheet if I format the cells as Date (dd-mmm-yy) and enter dd/mm/yy in the cell I get what I want! But not in my cashbook sheet!! Weird.
Yes the date is correct – no offence taken – it wouldn’t be my first stupid mistake if it had been!
I’d prefer not to put our personal finances on the airwaves, so I’m sorry but I haven’t attached a copy.
And yes, I want to enter 12/01/01 and get the result 12-Jan-01. AND it works in a new (clean) s/sheet (in cell A1)! I suppose I could create a new spreadsheet, set it up the same as this one, taking care with what I do with column A and enter some dummy data to try. I just feel there is some underlying formatting which is getting in my way.
Cheers, Ken

Reply | Quote

How are you entering the data – typing directly into the cell?
Have you tried selecting column A from a good worksheet and pasting it into the problem one?

Reply | Quote

Yes, Leif, typing directly into the cell. I’ll try your cut and paste suggestion tomorrow (and, if I have time, my proposed reconstruction type solution) and let you know. Thanks for your responses. I’m logging off now – need my beauty sleep – about 60 year’s worth! Speak with you next time. Ciao, Ken

Reply | Quote

Hi,
Just to confirm – Legare’s suggestion about Transition formulae (on the Transitions tab in Options, which I think is workbook specific) would produce exactly the behaviour you describe, so it’s definitely worth checking before you copy everything to a new workbook.
Hope that helps.

Reply | Quote

Try checking the regional settings on your Control Panel.

Reply | Quote

Thanks. See my response to Lief. Cheers, Ken

Reply | Quote

Ken,

An obvious point, but just to make sure we’ve covered all bases.

I assume you highlighted the cells when you applied formatting.

If so, and you have problems, you may be able to attacj the spreadsheet to find out what’snot working for you.

Reply | Quote

Thanks. As I said to Leif the data is a bit too personal to publish abroad. I’ll see if we get any other answers and also try my other solution (outlined in the reply to Leif). If it goes pear shaped as well I can publish it because the data will all be imaginery. Ciao, Ken

Reply | Quote

Sorry, I didn’t answer your question. Yes I did highlight the appropriate cells. Ken

Reply | Quote

I see that you have gotten numerous attempts to solve this problem from others. We could probably go on like this and eventually find out exactly what is causing the problem. It would probably be something simple like having “Transition Formulas” selected in “Options.” However, it could take a long time to narrow it down this way.

I will suggest something that might get you working more quickly. Why not create a new workbook and copy the worksheet to the new workbook (everything but the column causing the problem). If the date entry then works in the new workbook, you can copy any other worksheets, VBA code, etc. to that workbook and delete the one that does not work.

If you want to sent the offending workbook to use to look at, then make a copy of it and delete all of the data. See if the problem still happens, and if it does send us the workbook (not directly to me as I no longer have Excel 97 available).

Reply | Quote

Thank you all for your help. Yes it was having Transition formulae checked in this workbook. It was my first serious Excel workbook after years of Lotus123 and although I don’t recall it now I must have found and decided to use the Transition formulae setting. I have unchecked it and all is now roses. Thanks again. Ken

Reply | Quote