News, tips, advice, support for Windows, Office, PCs & more
Home icon Home icon Home icon Email icon RSS icon

We're community supported and proud of it!

  • How to determine data type?

    Tagged: , , ,

    Viewing 21 reply threads
    • Author
      Posts
      • #2352702
        Ron M
        AskWoody Plus

        I have started to download monthly files from my bank as .csv files and then save/convert  them as .xlsx or .xlsm files.  One of the columns is a date that looks like MM/DD/YYYY, e.g., 01/04/2021.  I tried to reformat it as a “Date” — YYYY-MM-DD, but it did not work – nothing happened.  I tried to use the DATEVALUE function and all I got was #VALUE! .  Is there anyway to find out what type of data this “Date” is – numeric, text, etc., as all the other values – Deposits, Withdrawals, etc., convert as numbers, which is what they should do.  I tried using the MONTH function to extract the month, but that produced another #VALUE! error.  I was able to extract the month as a numeric value using the LEFT( X2,2) function and that gave me the month as a number, but I would really like to produce a “date” value that I can format and treat as a real date from whatever the date is that comes through during the .csv translation.

        I am running Windows 10-20H2 and Excel as part of Office 365.

        All of the dates that are produced by the .csv conversion are exactly the same, so maybe I have to extract each component separately and then recombine them into a date somehow (any ideas).

        Any insight that anyone can provide will be greatly appreciated.  TIA.

        Ron M

      • #2352709
        doriel
        AskWoody Lounger

        The problem may be, that excel stores the date as number.. So if your CSV comes with text 01.03.2021 as a date, there may be problem.
        My question is, if the date comes like 24.3.2021 or 24.03.2021. That makes difference in my Office 365.

        conversion

        I can change the format to YYYY-MM-DD, if I rightclick the cell and select the format.

        different

        I tried using the MONTH function to extract the month, but that produced another #VALUE! error. I was able to extract the month as a numeric value using the LEFT( X2,2) function and that gave me the month as a number

        Your date is stored as string, thus you can extract substring. You must convert to date, but with leading zeroes (01.01.2021 eg)

        Dell Latitude E6530, Intel Core i5 @ 2.6 GHz, 4GB RAM, W10 1809 Enterprise

        HAL3000, AMD Athlon 200GE @ 3,4 GHz, 8GB RAM, Fedora 29

        Attachments:
      • #2352721
        CraigS26
        AskWoody Plus

        https://eileenslounge.com/viewforum.php?f=27&sid=7aae622a22efebc9ff97d61e02cc6bd3

        IF you don’t find an answer here there are guys at Eileen’s Lounge Excel Forum who seem to know so much Excel I’ve never thought they were from Earth.

        W10 Pro 20H2 / Hm-Stdnt Ofce '16 C2R / HP Envy Desktop-Ethernet/ 12 GB / 256G SSD + 1 TB HDD / i5-8400 CoffeeLake-S / GP=2 + FtrU=Semi-Annual + Feature Defer = 1 + QU=0

        1 user thanked author for this post.
      • #2352736
        Ron M
        AskWoody Plus

        doriel, thanks for the explanation.  The more I think about it, the more I realize that the real question to be answered is what does the rendering of data off a web site in the .csv format do to fields that are presented as “dates” on the website.

        I get the funny feeling that when dates on a website are rendered into a .csv file together with all the other data that are there, the dates come across as a text string.  My problem is trying to find this out, if, indeed, that is the case.

        Ron M

        1 user thanked author for this post.
        • #2352740
          doriel
          AskWoody Lounger

          You are wellcome! In the .csv (coma separeted value) all is considered as pure text. It does not contain any data formats.
          I think you should do some formatting first, before you can set the YYYY-MM-DD. Import your .csv into Excel, then save it as .xlsx, then play with cell formatting.
          If it was not bank account data, I would ask you to post your .csv here, but that is not acceptable in this case. Fell free to ask more questions, @Ron-m.

          Dell Latitude E6530, Intel Core i5 @ 2.6 GHz, 4GB RAM, W10 1809 Enterprise

          HAL3000, AMD Athlon 200GE @ 3,4 GHz, 8GB RAM, Fedora 29

      • #2352737
        Ron M
        AskWoody Plus

        CraigS26, thanks for the reference.  I will go and see what it has to suggest.

        Ron M

      • #2352779
        Ron M
        AskWoody Plus

        doriel, if .csv files are formatted as text files, then why is it that the numbers part of them work properly.  I can format the numbers, e.g., add the thousands comma, set the number of decimal places, etc., but the dates don’t perform the same way.  That’s the bind, I guess.

        I am trying to figure out if there is some way to take the date string that comes in the .csv file, take it apart into its components, month, day, and year and then concatenate it into a string that can be handled as a date.  I haven’t explored this fully yet, but I do know that I can extract the month using the LEFT operator.  I can probably extract the year using the the RIGHT operator.  I just have to figure out how to pull the day of the month from the middle of the text string.

        The other thing I am wondering and have yet to test, is whether or not it is possible to format the Date field within the .csv file before saving it as a .xlsx file – still thinking about this one.

        Ron M

        • #2352796
          anonymous
          Guest

          Try the MID function 🙂

          doriel

      • #2352830
        RetiredGeek
        AskWoody MVP

        Ron,

        You need to use the Import Wizard to get what you want.

        File->Open

        Select Your file (you need to change the type selector box to .csv)

        The wizard should popup.

        TextImportWizard
        On this screen you need to tell the wizard if your file has headers or not. (Default = No Headers)

        Click Next

        DelimiterSelection
        Now you need to select the data delimiter, usually a comma. (Default = Tab)
        Note: I should have unselected Tab but it didn’t make a difference in this case!

        Click Next

        SpecifyDateFormat
        On this screen click on your Date field at the bottom.

        Click the Date radio button and select the format the dates are in.

        Repeat for any other date fields.

        Click Finish

        ImportedDates
        You can see it imported the dates and it knows they are dates. Just select them and right click and select Format Cells and you’ll see they come up as dates!

        FormatDialog
        Here you can change the format to other locals by selecting the Locale (location): dropdown.

        DatesReformatted
        HTH 😎

        May the Forces of good computing be with you!

        RG

        PowerShell & VBA Rule!
        Computer Specs

        • This reply was modified 1 month, 2 weeks ago by RetiredGeek.
        • This reply was modified 1 month, 2 weeks ago by RetiredGeek.
        • This reply was modified 1 month, 2 weeks ago by RetiredGeek.
        Attachments:
        2 users thanked author for this post.
      • #2353964
        Ron M
        AskWoody Plus

        RetiredGeek, thanks for the very detailed explanation, it is greatly appreciated.  It has taken me a while to return to this as I have to deal with some things in my personal life.

        I hope you don’t mind, I printed your reply out as a Word document so that I could have it for future reference.

        I am using Excel 365 which, unfortunately does not have the Import Wizard.  I searched around and found that it can be invoked as a legacy application.  Once I did that, then everything else was straight forward.  All worked, as it was a simple matter of importing as a Table in Excel.  The Table will work as I would have had to convert it to that anyway as most of my ongoing analysis is with Pivot Tables which are better implemented from a data table within Excel.

      • #2354293
        RetiredGeek
        AskWoody MVP

        Ron, 365 does have the wizard as that is the version I used to make the example. It comes up automatically when you use File->Open in Excel and select a .csv file.

        May the Forces of good computing be with you!

        RG

        PowerShell & VBA Rule!
        Computer Specs

        • This reply was modified 1 month ago by RetiredGeek. Reason: Fix spelling from .cab to .cvs
        • This reply was modified 1 month ago by RetiredGeek. Reason: I'll get it right yet!
      • #2354661
        Ron M
        AskWoody Plus

        RetiredGeek, thanks for the insight.  When I went and checked the Excel HELP for the “Import Wizard” it pointed me in the direction of what it referred to as the “Text Import Wizard”.

        My situation was that I was exporting .csv files from the banking application, so they did not show as .cab files.  Nevertheless, everything worked out AS REQUIRED.

        Again, thanks for your help.

        Ron M

      • #2354724
        Ron M
        AskWoody Plus

        Retired Geek, I am having trouble with your instructions.  Things are not working, for some reason.

        Your instructions:

        ———————————————————————

        You need to use the Import Wizard to get what you want.

        File->Open

        Select Your file (you need to change the type selector box to .csv)

        The wizard should popup.

        <hr />

        The only problem is that the Wizard does not pop up.

        Here is what I do:

        1.  Open the Excel 365 Application
        2.  I go to  <Open>  –>   <Browse>
        3.   Set file type to – Text Files (*.prn, *.txt, *.csv)
        4.   Open the required .csv file

        No Wizard pops up, so this is where I get lost in what I am supposed to do.

        My question then, is how do I proceed when the Wizard does not pop up?

        The frustrating part is that I had something working yesterday and today, I cannot remember, or figure out, what it was.  Nevertheless, I will continue to work at it until I hear back from you, or I figure it out.

        Ron M

      • #2354734
        RetiredGeek
        AskWoody MVP

        Ok,

        Let’s try a different tack and force the use of the Wizards.

        From a blank workbook select: File->Options->Data

        Under the Show legacy data import wizards check the From Text (Legacy)

        Click OK

        Now go to the Data Tab.

        In the far upper left corner Click on the Get Data dropdown.

        Click: Legacy Wizards

        Click: From Text (Legacy)
        Get-Data

        Select your file and click Import

        The wizard should now popup and you can proceed with the previous instructions.

        HTH 😎

         

        May the Forces of good computing be with you!

        RG

        PowerShell & VBA Rule!
        Computer Specs

        Attachments:
      • #2354837
        Ron M
        AskWoody Plus

        Retired Geek, thanks for additional explanation.  This now works according to expectations and I thank you very much for your endurance.

        Ron M

      • #2356362
        Ron M
        AskWoody Plus

        Retired Geek, when I tried to paste cells from the Excel spreadsheet into this, everything went all screwy, so I guess I will have to find another way

        Sorry about that as I couldn’t find a way to delete the post???

        Ron

         

         

        • This reply was modified 4 weeks ago by Ron M.
      • #2356414
        Paul T
        AskWoody MVP

        When you tries to paste what into what?
        What went screwy?

        cheers, Paul

      • #2356416
        Ron M
        AskWoody Plus

        Paul, I thought I was simply cutting and pasting examples from Excel into my question here.  Everything looked as I thought it should until I “Submitted” the request.  It ended up with a whole bunch of codes and stuff.  For example, the following originally pasted so that it looked like it looked in the spreadsheet.

        Date 1               Date 2             Date  3

        2021-02-15      2021-02-16    2021-02-18

        2021-02-12      2021-02-13     2021-02-15

        When I submitted it, then it went like this, with all the code for table width, body and each line of the table.  This is what I meant by “screwy”.

        <table width=”670″>
        <tbody>
        <tr>
        <td width=”149″>Account description</td>
        <td width=”87″>Trade date</td>
        <td width=”121″>Settlement date</td>
        <td width=”123″>Processing date</td>
        <td width=”135″>Processing YEAR</td>
        <td width=”55″>Market</td>
        </tr>
        <tr>
        <td>USD Margin</td>
        <td>2021-03-31</td>
        <td>2021-03-31</td>
        <td>2021-04-01</td>
        <td></td>
        <td>CAN</td>
        </tr>
        <tr>
        <td>USD Margin</td>
        <td>2021-03-30</td>
        <td>2021-03-30</td>
        <td>2021-03-30</td>
        <td></td>
        <td>USA</td>
        </tr>
        </tbody>
        </table>

        So the question is – How do I post examples from spreadsheets into the “description” here, so that it looks like it did in the spreadsheet?  It says above that “You can use BBCodes to format your content.”  Unfortunately, I do not know what BBCodes are, although I suspect that the codes enclosed in <> are probably related.

        Ron

        • This reply was modified 4 weeks ago by Ron M.
      • #2356436
        Paul T
        AskWoody MVP

        Take a screenshot.

        Open Snip n Sketch.
        Take a rectangular / window screenshot.
        Save as PNG.
        Attach here by clicking the “Select File” button at the bottom of your reply.

        cheers, Paul

      • #2356647
        Ron M
        AskWoody Plus

        Paul, thanks for the Snip & Sketch reference. I usually use the Snipping Tool, but the end result is the same, I guess.

        Retired Geek, I thought I would keep this in the same thread as I suspect that they are related.

        I imported the CSV file following your previous instructions and converted it to an xlsx file.  I need the MONTH from the “Settlement Date” , so I set out to use the MONTH function, only to be surprised by the results.Screenshot-2021-04-11-084639

        It calculated the first date of Excel – 1900-01-01 as the MONTH.  Obviously, there is something not working right because if I check on the format of the “Settlement Date”, it is in a Date format.  Any insight into how I can extract the Month from the converted date?

        Ron

         

         

        Attachments:
        • #2356827
          doriel
          AskWoody Lounger

          You set “Settlement Month” cell format as date. Thats not going to work.
          Set it as “general” (or number). Then the months will be displayed correctly.

          I used the MONTH function to extract date from a cell on the left.
          You should see the difference in two cells on the right of the picture. Hope this helps.

          date

          On this picture, you can see my formula. Unfortunatelly, I have czech Excel. Your function is called MONTH

          date2

          Dell Latitude E6530, Intel Core i5 @ 2.6 GHz, 4GB RAM, W10 1809 Enterprise

          HAL3000, AMD Athlon 200GE @ 3,4 GHz, 8GB RAM, Fedora 29

          Attachments:
      • #2356836
        Ron M
        AskWoody Plus

        doriel, thank you for the solution.  I must confess to being a bit puzzled though as a Month is part of a Date and, I would think should conform to a Date format.  Regardless, setting as General works.

        Hopefully, I am now finished with the peculiarities of converting a .csv file to an .xlsx file.

        Ron M

        • This reply was modified 3 weeks, 5 days ago by Ron M.
        • #2356843
          doriel
          AskWoody Lounger

          I must confess to being a bit puzzled though as a Month is part of a Date and, I would think should conform to a Date format.

          Yes, I agree with your assumption. That would be logical, that month is a part of date.

          I was looking for month only data type, but there isnt any 🤷‍♂️
          Only DD-MM or MM-DD. No month itself. Maybe someone else can bring another solution.

          date3

          Dell Latitude E6530, Intel Core i5 @ 2.6 GHz, 4GB RAM, W10 1809 Enterprise

          HAL3000, AMD Athlon 200GE @ 3,4 GHz, 8GB RAM, Fedora 29

          • This reply was modified 3 weeks, 5 days ago by doriel. Reason: picture
          Attachments:
          • #2356863
            zeddy
            AskWoody_MVP

            You can just use a custom number format for that cell.

            (that would be the last option in the Category list, in your image that would be Vlastni – I learn something new every day – thank you)

            Use mmm to give Jan, Feb, .., Dec

            Use mm to give 01, 02,.., 12

            Use mmmm to give January, february, .., December etc etc etc

            zeddy

            • This reply was modified 3 weeks, 5 days ago by zeddy.
            • This reply was modified 3 weeks, 5 days ago by zeddy.
            • This reply was modified 3 weeks, 5 days ago by zeddy.
            • This reply was modified 3 weeks, 5 days ago by zeddy.
            1 user thanked author for this post.
            • #2356876
              doriel
              AskWoody Lounger

              Bingo!

              That does the trick 🙂 I learned something new too. Thats great.

              Custom format mmmm transforms the month into January, February, …

              mesic

              Dell Latitude E6530, Intel Core i5 @ 2.6 GHz, 4GB RAM, W10 1809 Enterprise

              HAL3000, AMD Athlon 200GE @ 3,4 GHz, 8GB RAM, Fedora 29

              Attachments:
      • #2356892
        Ron M
        AskWoody Plus

        For some reason, this does not seem to work for me.  When I us the “=MONTH(D2)” formula on the first entry and then replicate it down a couple of entries, Format it using the “Custom – mmm” format,  you can see that all I get is “Jan” when the correct value for the first one would “Mar”.  Likewise, the correct on for the second entry would be “Apr”, and the third one, “Mar”.  The number values for each month are correct as is shown by the examples that follow the three conversion using Custom category.Screenshot-2021-04-12-041719

        Any insight that you folks can provide will be greatly appreciated.

        Ron

        Attachments:
        • #2356898
          doriel
          AskWoody Lounger

          Now I see the catch in it. If we convert full date into MONTH only. It stores a number.
          3 for example.

          If we try to convert 3 into month then, it understands it as 3rd January. Thats why you see Jan everywhere. I will try and let you know later.

          It works with numbers so far. We want names of the month to be displayed.

          Dell Latitude E6530, Intel Core i5 @ 2.6 GHz, 4GB RAM, W10 1809 Enterprise

          HAL3000, AMD Athlon 200GE @ 3,4 GHz, 8GB RAM, Fedora 29

        • #2356899
          doriel
          AskWoody Lounger

          SO, the solution to this is to set the Settlement format to Custom mmmm.
          And set the value directly just by equals

          Dont use the MONTH function in the Settlement cells. It should work then.

          date-final

          Dell Latitude E6530, Intel Core i5 @ 2.6 GHz, 4GB RAM, W10 1809 Enterprise

          HAL3000, AMD Athlon 200GE @ 3,4 GHz, 8GB RAM, Fedora 29

          Attachments:
      • #2356903
        Ron M
        AskWoody Plus

        doriel, my mind must be a little slow this morning (only 5:00am my time), but is your new solution to add another column that formats the number into a month format?  I am not quite clear as to exactly what it is you are doing. (I think I need to go and get some more sleep.)

        Ron

        1 user thanked author for this post.
        • #2356904
          doriel
          AskWoody Lounger

          Sorry for being unclear. My thought is:

          • Duplicate the whole Settlement date into Settlement month column
          • Set the Settlement month column format to Custom: mmmm

          then, just the month of the date is shown. Do we understand each other now? 🙂

          Dell Latitude E6530, Intel Core i5 @ 2.6 GHz, 4GB RAM, W10 1809 Enterprise

          HAL3000, AMD Athlon 200GE @ 3,4 GHz, 8GB RAM, Fedora 29

      • #2357055
        Ron M
        AskWoody Plus

        doriel, we definitely understand each other now. 😊  It works as needed.  Thank you for clarifying this.

        Ron

    Viewing 21 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, no politics or religion.

    Reply To: How to determine data type?

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.