How to determine data type?

Topic

New Reply

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

Reply | Quote

Replies

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.

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

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 3420, Intel Core i7 @ 2.8 GHz, 16GB RAM, W10 22H2 Enterprise

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

PRUSA i3 MK3S+

Reply | Quote

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 22H2 / Hm-Stdnt Ofce '16 C2R / HP Envy Desk-Ethernet - SSD-HDD/ i5(8th Gen) 12GB / GP=2 + FtrU=Semi-Annual + Feature Defer = 1 + QU=0

1 user thanked author for this post.

doriel

Reply | Quote

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.

doriel

Reply | Quote

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 3420, Intel Core i7 @ 2.8 GHz, 16GB RAM, W10 22H2 Enterprise

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

PRUSA i3 MK3S+

Reply | Quote

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

Ron M

Reply | Quote

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

Reply | Quote

Try the MID function 🙂

doriel

Reply | Quote

[RetiredGeek]

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.

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

Click Next

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

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

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!

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

HTH 😎

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

• This reply was modified 2 years, 6 months ago by RetiredGeek.
• This reply was modified 2 years, 6 months ago by RetiredGeek.
• This reply was modified 2 years, 6 months ago by RetiredGeek.

2 users thanked author for this post.

Ron M, doriel

Reply | Quote

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.

Reply | Quote

[RetiredGeek]

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 2 years, 5 months ago by RetiredGeek. Reason: Fix spelling from .cab to .cvs
• This reply was modified 2 years, 5 months ago by RetiredGeek. Reason: I'll get it right yet!

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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)

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

Reply | Quote

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

Ron M

Reply | Quote

[Ron M]

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 2 years, 5 months ago by Ron M.

Reply | Quote

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

cheers, Paul

Reply | Quote

[Ron M]

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 2 years, 5 months ago by Ron M.

Reply | Quote

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

Reply | Quote

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.

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

 

 

Reply | Quote

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.

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

Dell Latitude 3420, Intel Core i7 @ 2.8 GHz, 16GB RAM, W10 22H2 Enterprise

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

PRUSA i3 MK3S+

Reply | Quote

[Ron M]

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 2 years, 5 months ago by Ron M.

Reply | Quote

[doriel]

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.

Dell Latitude 3420, Intel Core i7 @ 2.8 GHz, 16GB RAM, W10 22H2 Enterprise

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

PRUSA i3 MK3S+

• This reply was modified 2 years, 5 months ago by doriel. Reason: picture

Reply | Quote

[zeddy]

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 2 years, 5 months ago by zeddy.
• This reply was modified 2 years, 5 months ago by zeddy.
• This reply was modified 2 years, 5 months ago by zeddy.
• This reply was modified 2 years, 5 months ago by zeddy.

1 user thanked author for this post.

doriel

Reply | Quote

Bingo!

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

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

Dell Latitude 3420, Intel Core i7 @ 2.8 GHz, 16GB RAM, W10 22H2 Enterprise

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

PRUSA i3 MK3S+

Reply | Quote

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.

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

Ron

Reply | Quote

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 3420, Intel Core i7 @ 2.8 GHz, 16GB RAM, W10 22H2 Enterprise

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

PRUSA i3 MK3S+

Reply | Quote

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.

Dell Latitude 3420, Intel Core i7 @ 2.8 GHz, 16GB RAM, W10 22H2 Enterprise

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

PRUSA i3 MK3S+

Reply | Quote

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.

doriel

Reply | Quote

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 3420, Intel Core i7 @ 2.8 GHz, 16GB RAM, W10 22H2 Enterprise

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

PRUSA i3 MK3S+

Reply | Quote

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

Ron

Reply | Quote