[novicee]

17 Oct 11

Excel 2007: Editing a date causes a format change

When I edit a date (e.g. changing the month) the format of the cell changes from "date" to "general" and the edited date has a ^ symbol before it in the formula bar indicating it has become text. This stops me from using it in formulas (e.g. I cannot subtract it from another date to find the interval between them).

I have searched Word 2007 help without being able to find a way to stop this format change. Any help would be welcome.

- Novice

[Geej]

novicee, on 17 October 2011 - 02:42 AM, said:

17 Oct 11

Excel 2007: Editing a date causes a format change

When I edit a date (e.g. changing the month) the format of the cell changes from "date" to "general" and the edited date has a ^ symbol before it in the formula bar indicating it has become text. This stops me from using it in formulas (e.g. I cannot subtract it from another date to find the interval between them).

I have searched Word 2007 help without being able to find a way to stop this format change. Any help would be welcome.

- Novice

Need to ensure the cell is Date type. (Right-click the cell->Format cells... --->
In the "Number" tab, under Category: Date should be selected -> Followed by your choosen Type: (eg. 14-Mar-01)

When you edit the date cell manually, ensure you stick to the required format to prevent from changing to text/ other type.

BTW, What has Word 2007 help got to do with Excel?

[novicee]

18 Oct 11

Excel 2007: Editing a date causes a format change

Geej,

Thanks for the suggestion but I had already done what you advise. This is what I did:

1. format cells > number > date > 14.03.01

2. In the cell the date displays as 30.10.95 and in the formula bar as 1995/10/30. No problems at this stage and I can subtract the date from another date.

3. In the formula bar, I then deleted the 0 in 30 and replaced it with 1. When I hit enter, the date as displayed in the cell changed from 30.10.95 to 1995/10/31. In the formula bar, the date displayed changed from 1995/10/30 to ^1995/10/31. The ^ indicating it had become text.

When I edit a number it does not change to text so I do not understand why a date should be any different. I did not have this problem with dates in previous versions of Excel.

BTW, I meant, of course, "Excel 2007 help" in my last post.





- Novice

[Geej]

I couldn't get the below date format in Excel 2007

novicee, on 17 October 2011 - 10:39 PM, said:

1. format cells > number > date > 14.03.01

2. In the cell the date displays as 30.10.95 and in the formula bar as 1995/10/30. No problems at this stage and I can subtract the date from another date.

Nevertheless, try using other date format. Some guide like this or this might help.

[novicee]

19 Oct 11

Excel 2007: Editing a date causes a format change - Solution

Geej,

Available date formats

The available date formats depend on the location the user specifies on the page:

format cells > number > date.

I could choose 14.03.01 because I had my location as English (New Zealand). This format is not offered if the location is English (United States).

Extension of the problem

I changed my location to English (United States), chose different date formats and tried editing but got the same result as before.

I also noticed that I don't even have to edit the date for the problem to occur. Just double clicking the cell is enough to change the date to text.

Solution of the problem

I had customized my Win 7 date format using:

control panel > region and language > formats > additional settings > date

I went to the same page and chose reset and that has fixed the problem in Excel 2007.

Conclusion

Customizing the date format in Win 7 via the control panel page above, feeds through into Excel 2007 and can cause problems with dates in worksheets.


- Novice

This post has been edited by novicee: 18 October 2011 - 01:48 PM

[Geej]

Thanks for sharing your solution to your own problem with us. Glad to hear you solved the problem yourself.
Your solution may help someone who face similar situation.

Cheers