Date Formatting in Calculations

[marcusa]

Hello,

Would appreciate anyone's thoughts on the following issue please...

I am creating a simple field whereby a user inputs a date into the 'Membership Start' field and then selects either Annual / Monthly / Daily from the 'Membership Type' field. There is then a case function which adds a year, month or day to the 'membership start' date and puts the result into the 'Membership Expiry' field.

The calculation is as follows:

Case (

Membership Type = "Annual" ; Date(Day(Membership Start) ; Month(Membership Start) ; Year(Membership Start) +1);

Membership Type = "Monthly" ; Date(Day(Membership Start) ; Month(Membership Start) +1 ; Year(Membership Start));

Membership Type = "Daily" ; Date(Day(Membership Start) +1 ; Month(Membership Start) ; Year(Membership Start));

)

The maths seems to work fine but the problem is that whilst the 'Membership Start' is filed out in dd/mm/yyyy format, the returned values in 'Membership Expiry' appear as mm/dd/yyyy. Selecting a preferred display format from the layout view does not appear to have any affect on the calculated field. The question is how to force a preferred date display in the calculated field?

I have attached a screen shot showing the results...

Your help in advanced is greatly appreciated.

Many thanks,

Marcus

[comment]

See if this helps:

http://fmforums.com/forum/topic/79243-question-mark-in-date-field/page__p__369632#entry369632

[marcusa]

Many thanks for the link..

I have actually just seemed to have solved the problem by changing the order in which the case function was written:

Formatting it in the mm/dd/yyyy order rather than dd/mm/yyyy order fixed the issue. So basically it was changed from:

Case (

Membership Type = "Annual" ; Date(Day(Membership Start) ; Month(Membership Start) ; Year(Membership Start) +1);

Membership Type = "Monthly" ; Date(Day(Membership Start) ; Month(Membership Start) +1 ; Year(Membership Start));

Membership Type = "Daily" ; Date(Day(Membership Start) +1 ; Month(Membership Start) ; Year(Membership Start));

)

 

to...

 

Case (

Membership Type = "Annual" ; Date(Month(Membership Start) ; Day(Membership Start) ; Year(Membership Start) +1);

Membership Type = "Monthly" ; Date(Month(Membership Start) +1 ; Day(Membership Start) ; Year(Membership Start));

Membership Type = "Daily" ; Date(Month(Membership Start) ; Day(Membership Start) +1 ; Year(Membership Start));

)

Problem solved for now!

[comment]

changing the order in which the case function was written:

It's actually the order of the Date() function. Regardless of the date format in use or the way the date is formatted for display, the Date() function parameters are month; day; year - in that order. I guess I should have seen that in your original post, but I was misled by your description.