Date formula

[Dukester0122]

I have a formula:

if {USERDATE01} = Date(1900, 1, 1) then "Date Not Entered"
else Totext({USERDATE01}, "mm/dd/yyyy")

but is returning 00/10/2005 instead of 09/10/2005

 

[lbass]

The case matters, as "mm" refers to minutes. Try:

"MM/dd/yyyy"

-LB

 

[Dukester0122]

That worked,thanks.

Now I need to add this to another formula, here's what I did:

if {?Performance between} = "Order to Invoice Date" then {INVDATE}-{ORDERDATE}
else if {?Performance between} = "Order to Actual Ship Date" then {USERDATE01} - {ORDERDATE}

or if {USERDATE01} = Date(1900, 1, 1) then "Date Not Entered" else Totext({USERDATE01}, "mm/dd/yyyy")

If USERDATE01 is 01/01/1900, the formula still calculates the difference. What I want to return is "Date Not Entered".

 

[lbass]

All results of a formla have to be of the same datatype. Also, if you want the userdate to be evaluated first, then it needs to be the first clause of the formula. Try:

if {USERDATE01} = Date(1900, 1, 1) then
"Date Not Entered" else
If {?Performance between} = "Order to Invoice Date" then totext({INVDATE}-{ORDERDATE},0,"") else
if {?Performance between} = "Order to Actual Ship Date" then totext({USERDATE01} - {ORDERDATE},0,"")
else Totext({USERDATE01}, "MM/dd/yyyy")

-LB

 

[Dukester0122]

When I chose "Order to Invoice Date" some are working others are showing 'Date not Entered'. But both {INVDATE} and {ORDERDATE} fields have dates on them and I thought the "Date not Entered" only applies to the field {USERDATE01}

 

[lbass]

Can you explain what {userdate01} is? A parameter? A database field? What does it refer to?

-LB

 

[Dukester0122]

A datetime field in a sql database. Its a user-defined field where users can enter a date but sometimes they forget and this is when the system assigns 01/01/1900.

 

[lbass]

Try something like:

If {?Performance between} = "Order to Invoice Date" then totext({INVDATE}-{ORDERDATE},0,"") else
if {?Performance between} = "Order to Actual Ship Date" then
(
if {USERDATE01} = Date(1900, 1, 1) then
"Date Not Entered" else
totext({USERDATE01} - {ORDERDATE},0,"")
)
else Totext({USERDATE01}, "MM/dd/yyyy")//or maybe the else here should be 'else ""'--not sure

-LB

 

[Dukester0122]

Added more to the formula:

if {?Performance between} = "Est. Ship Date vs Actual Ship Date" then
(
if {USERDATE01} = Date(1900, 1, 1) then "Date Not Entered"
else totext({AVSOPHDR.ACTLSHIP}-{USERDATE01},0,"")
)

Works when both {USERDATE01} and {AVSOPHDR.ACTLSHIP} have dates or no dates. Whne one has a date and the other shows "Date not Entered" the result of {?Performance between} is weird. Some shows -38644, -38606, -38577.

 

[lbass]

Please paste in the entire formula.

-LB

 

[Dukester0122]

I've got it.

if {?Performance between} = "Est. Ship Date vs Actual Ship Date" then
(
if {USERDATE01} = Date(1900, 1, 1) then "Date Not Entered"
else if {AVSOPHDR.ACTLSHIP} = Date(1900, 1, 1) then "Date Not Entered"
else totext({AVSOPHDR.ACTLSHIP}-{USERDATE01},0,"")
)

I've been testing and everything looks good. I tried to put the results in a chart graph as percentage. The ones that resulted in "Date Not entered" is screwing the graph. What's the best way to resolve this?