+ Reply to Thread
Results 1 to 6 of 6

Reading ages problem in Excel

  1. #1
    Forum Contributor
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    226

    Reading ages problem in Excel

    Hi,

    I initially had a problem with reading ages which had been solved but now I have another problem with the same type of issue when I save the spreadsheet as a csv file.

    I have a spreadsheet showing reading ages in the format 10:05 for 10 years and 05 months which I wanted to show as 10/05 for importing into a MIS.

    Either one of the following formulae, written by experts in this forum, convert the reading age and show it in the format I require (10/05). However, when I save the file as a csv and then check the data, the reading ages show as a date format.

    Can anyone suggest a way around this problem?

    I have attached a saved csv file showing the problem.

    Thanks and regards
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Reading ages problem in Excel

    You should use Notepad or some other text editor to open the csv file and check what is inside it, rather than try to open it directly in Excel. Excel will always try to convert values, and to avoid this you can use the Data | From Text options to open the file, rather than double-click it, and this approach will open the Data Import wizard, which gives you more options on how to treat the imported records (you should set the option for that field as TEXT).

    Hope this helps.

    Pete

  3. #3
    Forum Contributor
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    226

    Re: Reading ages problem in Excel

    Hi Pete,

    Many thanks for your reply. I opened the csv file in notepad and the data is shown as I want it, so thanks you for your input.

    I'm not sure what you mean by 'to avoid this you can use the Data | From Text options to open the file'. Can you elaborate, please?

    Regards

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Reading ages problem in Excel

    Open Excel, then click on the Data tab in the ribbon, and then on the From Text icon (3rd from left).

    This will then invite you to specify the file that you want to import, so navigate to it and double click it.

    The Text Import Wizard will automatically open, which takes you through 3 stages, as follows:

    1. you can specify whether the data is fixed width or delimited, and you can also start the import from a row other than 1 - an area at the bottom shows the first few rows of the data. Click Next;

    2. if you specified Delimited on panel 1 this allows you to specify which character is used as the delimiter, and again the area at the bottom will show how the data will be split into columns. Click Next;

    3. the third panel enables you to specify how each column should be treated, so for those columns that you don't want to change you should highlight them and click on TEXT. You can also specify Date and which order the day/month/year appears in.

    When you click Finish you can also specify where the data should be placed, and then it will be arranged in columns from that point.

    If you are opening a csv file you can accept most of the defaults that Excel presents you with, so you will just be clicking Next | Next to get to the third panel, but it is important on this one to specify TEXT for those columns that you don't want Excel to try to interpret.

    Hope this helps.

    Pete

  5. #5
    Forum Contributor
    Join Date
    10-19-2016
    Location
    London, England
    MS-Off Ver
    Office 365
    Posts
    226

    Re: Reading ages problem in Excel

    Thanks, Pete.

    Perfect explanation!

    Regards

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Reading ages problem in Excel

    You're welcome - thanks for the rep.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Reading ages problem in Excel
    By cosmica67 in forum Excel General
    Replies: 9
    Last Post: 10-19-2016, 07:12 AM
  2. Problem with prompts while saving and reading from an excel file
    By flat_pro in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-28-2013, 02:07 PM
  3. Problem in Reading Excel file for looking at threshold values.
    By magantiamala in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-13-2012, 04:17 AM
  4. Problem with ages
    By Thatlibrarymiss in forum Excel General
    Replies: 2
    Last Post: 10-09-2012, 04:26 AM
  5. Criteria Between two ages problem
    By kev030981 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-07-2006, 01:08 AM
  6. Loading Excel - please help - takes ages to load
    By Smilersal in forum Excel General
    Replies: 0
    Last Post: 07-12-2006, 09:15 AM
  7. loading excel takes ages
    By Smilersal in forum Excel General
    Replies: 3
    Last Post: 07-11-2006, 01:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1