Solved

Excel 2016 cell format issue

Posted on 2016-07-15
9
115 Views
Last Modified: 2016-08-11
"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
Comment
Question by:agwalsh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 50
ID: 41711841
Hello,

are you quoting somebody else? Can you supply a sample file that shows the problem?
0
 

Author Comment

by:agwalsh
ID: 41711879
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
 
LVL 70

Expert Comment

by:Qlemo
ID: 41711890
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 11

Expert Comment

by:Wilder1626
ID: 41712611
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
 

Author Comment

by:agwalsh
ID: 41712641
@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
 
LVL 11

Accepted Solution

by:
Wilder1626 earned 500 total points
ID: 41712682
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
 

Author Comment

by:agwalsh
ID: 41720293
This person is an experienced Excel user and she's only experienced this since installing Excel 2016. Again, any ideas?
0
 
LVL 11

Assisted Solution

by:Wilder1626
Wilder1626 earned 500 total points
ID: 41720897
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
 

Author Closing Comment

by:agwalsh
ID: 41751656
The problem got solved or at least semi-resolved in another way..but thank you
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question