Using 1 Formula for Multiple Functions CR11

[Cobby1812]

Hi there,
I want to convert this 1,100,325,00 into a date. The date in this case being 25/03/10. I want to try and do this in one formula rather than loads (which is what I have resorted to........I remember doing this ages ago but the grey cells have now gone...any help would be greatly appreciated.

 

[IanWaterman]

What is the logic for converting 1,100,325,00 to 25/03/10

Commas do not appear to make any sense

Ian

 

[Cobby1812]

Hi Ian,
the date is from a connection to my AS400 and this is how the dates are shown on the AS400 Table.

 

[IanWaterman]

You still need to explain logic for converting to date. It does not look like epoch time (seconds since 01/01/1970 GMT).

Ian

 

[Cobby1812]

Because I want to use this field as a date field.....in this case this is the postdate of a transaction so I want to convert this into a date field to then use in the report. Not really sure what you are driving at Ian (although it's probably me being thick)

 

[IanWaterman]

I just need to know what elements you take from 1,100,325,00 to derive a date of 25/03/10

What does 31/12/11 look like in your date format

 

[Cobby1812]

Ahhhhh I see. OK

123456789012
1,100,325,00

1 to 2 - Not Used
3 - 4 - Year
5 - 7 - Month (remove comma)
8 - 9 - Day
0 - 2 - Remove

What I did was to convert to text then remove commas leaving

123456789
110032500

So in real terms 2 - 3 = Year
4 - 5 Month
6 - 7 Day

 

[IanWaterman]

@date

global numbervar year;
global numbervar month;
global numbervar day;

year:= tonumber(mid(replace({datefield},",","")2,2));
month:= tonumber(mid(replace({datefield},",","")4,2));
day:= tonumber(mid(replace({datefield},",","")6,2));

date(2000+year, month, day);

Ian