about dates....

General
#1

I’m confused.
I had an excel spread sheet with a date in it. Formatted YYYY/MM/DD.
When exported to CSV the date format got changed to MM/DD/YYYY.

I’m wondering how to properly handle the mirad of ways that people will enter a date as text…

#2

Give them separate fields, or specify the format you expect in a label. (“MM/DD/YYYY”, for example.)

#3

I’m more concerned about how a read the data in from a file that is produced by something like excel.
I not control over what excel will out put as a date but what does the date class do?

dim d1 as new date(“yyyy/mm/dd”)
dim d1 as new date(“mm/dd/yyyy”)
dim d2 as new date(“yyyymmdd”)
dim d3 as new date(“yyyy-mm-dd”)

#4

I’m under the impression that Excel is consistent in how it outputs dates regardless of how they appear on-screen.

#5

That may be Kem… but I still need to deal with it. :slight_smile:

#6

If it’s that format, it’s a SQLDate, so this should work:

dim d as new date
d.SQLDate = xlDateString
#7

Here it looks like MM/DD/YY
I just exported a workbook with one cell - just todays date in it
I got 11/27/13

#8

I got DD/MM/YYYY which is my short date format in Windows. Parsedate should work so long as you aren’t transporting your data between North America & the rest of the world, in which case 9th June could become 6th September.

#9

Odd as I even went so far as to close excel, change my short date format to DD/MM/YYYY and then export again

Now this IS Excel on the Mac not Windows & gawd knows ……

#10

Ya that’s kinda how I feel…

#11

Have you tried exporting the csv in excel with the date format you wanted then opening the csv in a text editor? I notice that excel will reopen the correctly csv and will apply a new date format in excel.

#12

The safest way that I know is to convert it to a value, and then apply your formatting later (to DD/MM/YYYY, or MM/DD/YYYY, or DD/MMM/YYYY - etc.). Here is a link to the Microsoft Excel DateValue page which explains the value.

Excel DateValue

The saved CSV file always has a chance to be different based on the computers local settings -which is not consistent across users computers.

Sorry, it may not be the answer you are wanting, and it is likely the most consistent.

#13

I had to deal with that (Excel / French) and decided to decode the date into atoms and build the Xojo date from that.

I also had dates like these:
11//28/13
00/00/13
11/ 28/13

and so on.

I decided to insert a ‘decode as correct as possible’ method that start by counting the number of ‘/’, replaceAll spaces (leading, ending, inside), etc.

And in the end, I took the first two digits (Field #1) as Month, second two digits as Day (Field #2) and last two digits as Year (Field #3).

I also declared that all dates that are beyond of 13 / 2013 (starting with year 2014) is in reality 1914, 1915, etc.

I hope I recall all / not forget a test.

HTH.

#14

[quote=49446:@Norman Palardy]Odd as I even went so far as to close excel, change my short date format to DD/MM/YYYY and then export again

Now this IS Excel on the Mac not Windows & gawd knows ……[/quote]

You probably need to change it in Excel as well.

Personally I only export SQL Date/Time from Excel, however most of the people I work with don’t know to do that so getting a csv file from them is a pain.

#15