Convert standard date into numerical form

[oasuke]

Hello,

I have a date format written as "DD-MM-YYYY (I.e, 24/11/2012 or November 24th, 2012)
DD = two-digit day of month (01 through 31)
MM = two-digit month (01=January, etc.)
YYYY = four-digit year

I got this format from using the file object's modification date expression. However, I want to convert this form into a single number so that I can arrange my dates in proper ascending/descending order. All the dates are stored in an Internal list object.Is there a formula or method for going about this?

[AyreGuitar]

To convert into a single number, try the calculations listed here for Julian Day: http://en.wikipedia.org/wiki/Julian_day#Calculation

[danworth]

Why not change the date to YYYYMMDD if its a string stored exactly as "DD-MM-YYYY". To rearrange the date string the following expression could be used : right$("date", 4)+left$(right$("date", 7), 2)+left$("date", 2).

[oasuke]

Why not change the date to YYYYMMDD if its a string stored exactly as "DD-MM-YYYY". To rearrange the date string the following expression could be used : right$("date", 4)+left$(right$("date", 7), 2)+left$("date", 2).

The problem would still be the same though, which is that it couldn't be arranged in most recent date to lower. For example, "24/11/2012 " would then become 2012/11/24. Using the internal list object's forward alphanumeric sort, everything would be sorted based on the highest number(which is what I want), but in this case it'd be arranged from highest year, then to months. (YYYYMMDD) However, since January starts at 1, December dates would always come first since it's 12. This is why I needed it converted into a number which factors in roll overs. However that expression is something I'll keep in mind.

Following AyreGuitar's, I have to do it like this:


Since the date is a string, I had to first extract the proper date strings, and put them inside alterable strings called y,a and m.

(Ie, a string reads 27/08/12)
a=Left$(Date( "Display" ), 2)
y="20"+Right$(Date( "Display" ), 2)
m=Left$(Right$(Date( "Display" ), 5), 2)

Those strings are now a=27, y=2012 and m=08.(I realize I had to add 20 to make it 2012, which will be a problem for dates under 20..but that's another issue for now) Since these are strings, I needed to convert them into numbers. I made some more alt.values and named them the same as the alt.strings.(y,a,m). This lets me compute the first part of the equation:

Display=The object the values are stored in
a=(14-Val(a( "Display" )))/12
y=Val(y( "Display" ))+4800-a( "Display" )
m=Val(m( "Display" ))+12*(a( "Display" ))-3

Those values are now a=0, y=6812 and m=5. Finally I can plug those into the JDN equation:

JDN=Round(0+(153(5)+2)/5+365(6812)+6812/4-6812/100+6812/400-32045)
JDN=2456140

Testing it in MMF, it appears to work, showing the most recent date equating to the highest number. However I haven't done thorough testing, but thanks for the tips everyone.

[AyreGuitar]

oasuke - danworth's solution is much simpler and should work as long as you have 2 digits for your month and date like you specified, so January should be 01 rather than 1. My solution was something I'd already come across when I needed to count how many days since a particular date.

[oasuke]

Yeah I don't know why I was thinking the month was 1-digit. Wish I had caught that earlier. So Instead of messing with that whole formula, I changed it to:

"20"+Right$(ChangeDate$( "File", getCurrentBrowsedPath$( "File-Folder object" )), 2)+Left$(Right$(ChangeDate$( "File", getCurrentBrowsedPath$( "File-Folder object" )), 5), 2)+Left$(ChangeDate$( "File", getCurrentBrowsedPath$( "File-Folder object" )), 2)+"|"+FileName$( "File", getCurrentBrowsedPath$( "File-Folder object" ))

Which gives me a string like 20120827|Filename, exactly what I needed for arranging by date modified.

Many thanks guys