Bad Date Format

[scott1971]

I am converting an AS400 "date" on CR 11 (not a true date, format of 20,041,001.00 etc etc) and all works well for a 'start date' by changing the "date" totext then splitting out the year, month and day then concatenating it back together with the following formula: "cdate({@The Year 3}&","&{@The Month 3}&","&{@The Day 3})", however for the end date this is returning a bad date format which I cannot understand. Start Date is ALWAYS filled but often the end date is not so I'm assuming that is the problem but I have no idea how to get around this, can someone please help!!!

 

[lbass]

Start your formula with:

if isnull({table.yourdate}) then date(0,0,0) else
cdate({@The Year 3}&","&{@The Month 3}&","&{@The Day 3})

This assumes that the second part of this formula works for you.

-LB

 

[scott1971]

lbass, I've tried this and it doesn't seem to work, it's still telling me it's a bad date format but it works perfectly for the other date which is identical other than it won't always have a date.

I take the end date 20,041,001.00 and do the following formulae:

Formula: totext ({HELIGL.EENDT},0,"")
Formula: mid ({@Date 3},7,2)
Formula: mid ({@Date 3},5,2)
Formula: left({@Date 3},4)
Formula: cdate({@The Year 3}&","&{@The Month 3}&","&{@The Day 3})

Identical as I say for the start date which works fine.

 

[lbass]

Maybe try it without all the nested formulas and the ampersands, as in:

if isnull({HELIGL.EENDT}) then
date(0,0,0) else
cdate(val(left(totext({HELIGL.EENDT},0,""),4)),val(mid(totext({HELIGL.EENDT},0,""),5,2)),val(mid(totext({HELIGL.EENDT},0,""),7,2)))

-LB

 

[IanWaterman]

LB's solution will only work if the field is null.
Place the datefield in your report and make sure nothing is being entered.

Also check your Options settings and report options settings in File and make sure that you have not checked the Convert Nulls to default.

You can then modify LBs formula to test for whatever is your default in that field.

Ian

 

[scott1971]

Many Thanks guys, this will help me a great deal and saves a colleague from walking out after two days staring and playing with the same formula!