Jump to content

Welcome to MSFN Forum
Register now to gain access to all of our features. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more. This message will be removed once you have signed in.
Login to Account Create an Account



Photo

Excel 2007: Editing a date causes a format change

- - - - -

  • Please log in to reply
5 replies to this topic

#1
novicee

novicee

    Newbie

  • Member
  • 16 posts
  • Joined 05-May 10
  • OS:Windows 7 x86
  • Country: Country Flag
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
"Everything should be made as simple as possible but not simpler" - Einstein


How to remove advertisement from MSFN

#2
Geej

Geej

    Senior Member

  • Member
  • PipPipPipPip
  • 635 posts
  • Joined 01-January 08
  • OS:XP Pro x86
  • Country: Country Flag

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?

#3
novicee

novicee

    Newbie

  • Member
  • 16 posts
  • Joined 05-May 10
  • OS:Windows 7 x86
  • Country: Country Flag
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
"Everything should be made as simple as possible but not simpler" - Einstein

#4
Geej

Geej

    Senior Member

  • Member
  • PipPipPipPip
  • 635 posts
  • Joined 01-January 08
  • OS:XP Pro x86
  • Country: Country Flag
I couldn't get the below date format in Excel 2007

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.

#5
novicee

novicee

    Newbie

  • Member
  • 16 posts
  • Joined 05-May 10
  • OS:Windows 7 x86
  • Country: Country Flag
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

Edited by novicee, 18 October 2011 - 01:48 PM.

"Everything should be made as simple as possible but not simpler" - Einstein

#6
Geej

Geej

    Senior Member

  • Member
  • PipPipPipPip
  • 635 posts
  • Joined 01-January 08
  • OS:XP Pro x86
  • Country: Country Flag
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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users