Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

Excel 2016 cell format issue

"I now use Excel 2016) whereby when I open a file I have already worked on (even same day) the format in cell is changing from ‘general’ to date. " 
What is the cause of this problem and how can it be rectified?
0
agwalsh
Asked:
agwalsh
2 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

are you quoting somebody else? Can you supply a sample file that shows the problem?
0
 
agwalshAuthor Commented:
Yes, we did it over a screen share, I could see that sequence happening on her screen but when I did exactly the same thing, it incremented by days...any ideas?
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Background: OP wants to copy down dates, incrementing in years instead of days: https://www.experts-exchange.com/questions/28957642/Copy-down-dates-in-Excel-incrementing-by-years-not-days.html
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Wilder1626Commented:
agwalsh

have you tried to put the date using Genaral cell format but also adding a single quote in front of date value?
Ex:
'2016/1/1
'2016/1/2
'2016/1/3

Normally, that should keep the cell format to general.
0
 
agwalshAuthor Commented:
@Wilder1626 - the person who asked me doesn't see (and to be honest neither do I) why if you enter data in general format it should decide to just change to date...any ideas?
0
 
Wilder1626Commented:
It normally change format automatically if a value inside a cell match a certain Excel format logic.

Date is an example. even if you enter in a general format cell a "date", if the date value represent an official date format in excel, it will force the cell to the real format unless you enter a single quote at the beginning, as an example, to break the official date format.

To really force Excel to not convert your cell to date format would be to put the cell format to TEXT or add the single quote at the beginning of the value.

Excel realities...

Other thing to know about:
Excel likes to format thing also when opening. You can see this just by trying to open a csv file format into excel. If for example you have numbers with leading zero's (ex: 00123456) if open in excel, it format the number as general without the leading zero's (123456). It does not keep the same value format.

Wiki: CSV is a simple file format used to store tabular data, such as a spreadsheet or database. Files in the CSV format can be imported to and exported from programs that store data in tables, such as Microsoft Excel or OpenOffice Calc. CSV stands for "comma-separated values".
0
 
agwalshAuthor Commented:
This person is an experienced Excel user and she's only experienced this since installing Excel 2016. Again, any ideas?
0
 
Wilder1626Commented:
What you can try  is below option.

If all cells are General in your excel file, select a couple of cells where you have the dates in it and force the general formatting just by following these few steps:

Click on HOME tab and select CELL STYLES
 cell_styles
Then, Right click on NORMAL and select MODIFY

Then, click on FORMAT button

Finally in the NUMBER tab, re select the GENERAL format and click OK

Then, close the excel file and try to open it again and tell me if it did fixed the problem.
0
 
agwalshAuthor Commented:
The problem got solved or at least semi-resolved in another way..but thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now