Date Calculation

[HonkyCat]

Hi,

We currently use the following calculation (see "a") to display a month, day, and year date (and it works great).

Now I want to add an option that will look for a certain customer number and if the result is true, then display only the month and year of a date. If the answer is false, then display the month, DAY, and year.

Example "b" shows my new calculation, but it does not work. I've been racking my brain on how to make this work including different variations...case statements, etc.. Can anyone tell me what I'm doing wrong?

a. If(LabSchedule="Monthly",Date(Month(Sample Date)+1,Day(Sample Date),Year(Sample Date)),

If(LabSchedule="45 Days",Date(Month(Sample Date),Day(Sample Date)+45,Year(Sample Date)),

If(LabSchedule="Bimonthly",Date(Month(Sample Date)+2,Day(Sample Date),Year(Sample Date)),

If(LabSchedule="Quarterly",Date(Month(Sample Date)+3,Day(Sample Date),Year(Sample Date)),

If(LabSchedule="120 Days",Date(Month(Sample Date)+4,Day(Sample Date),Year(Sample Date)),

If(LabSchedule="Semi-Annual",Date(Month(Sample Date)+6,Day(Sample Date),Year(Sample Date)),

If(LabSchedule="Annual",Date(Month(Sample Date)+12,Day(Sample Date),Year(Sample Date)),

If(LabSchedule="Random","",

If(LabSchedule="Other","",""))))))))))))))))))

b. If(LabSchedule="Monthly",Date(Month(Sample Date)+1,Day(Sample Date),Year(Sample Date)),

If(LabSchedule="45 Days",Date(Month(Sample Date),Day(Sample Date)+45,Year(Sample Date)),

If(LabSchedule="Bimonthly",Date(Month(Sample Date)+2,Day(Sample Date),Year(Sample Date)),

If(LabSchedule="Quarterly",Date(Month(Sample Date)+3,Day(Sample Date),Year(Sample Date)),

If(LabSchedule="120 Days",Date(Month(Sample Date)+4,Day(Sample Date),Year(Sample Date)),

If(LabSchedule="Semi-Annual",Date(Month(Sample Date)+6,Day(Sample Date),Year(Sample Date)),

If(LabSchedule="Annual",Date(Month(Sample Date)+12,Day(Sample Date),Year(Sample Date)),

If(Customer# = "145" and LabSchedule="Monthly",Date(Month(Sample Date)+1,Year(Sample Date),

If(Customer# = "145" and LabSchedule="Bimonthly",Date(Month(Sample Date)+2,Year(Sample Date),

If(Customer# = "145" and LabSchedule="Quarterly",Date(Month(Sample Date)+3,Year(Sample Date),

If(Customer# = "145" and LabSchedule="120 Days",Date(Month(Sample Date)+4,Year(Sample Date),

If(Customer# = "145" and LabSchedule="Semi-Annual",Date(Month(Sample Date)+6,Year(Sample Date),

If(Customer# = "145" and LabSchedule="Annual",Date(Month(Sample Date)+12,Year(Sample Date),

If(LabSchedule="Random","",

If(LabSchedule="Other","",""))))))))))))))))))

[comment]

Try something like:

Let ( [

newDate =

Case (

LabSchedule = "Monthly" ;

Date ( Month ( Sample Date ) + 1 ; Day ( Sample Date ) ; Year ( Sample Date ) ) ;

LabSchedule = "45 Days" ;

Date ( Month ( Sample Date ) ; Day ( Sample Date ) + 45 ; Year ( Sample Date ) ) ;

LabSchedule = "Bimonthly" ;

Date ( Month ( Sample Date ) + 2 ; Day ( Sample Date ) ; Year ( Sample Date ) ) ;

LabSchedule = "Quarterly" ;

Date ( Month ( Sample Date ) + 3 ; Day ( Sample Date ) ; Year ( Sample Date ) ) ;

LabSchedule = "120 Days" ;

Date ( Month ( Sample Date ) + 4 ; Day ( Sample Date ) ; Year ( Sample Date ) ) ;

LabSchedule = "Semi-Annual" ;

Date ( Month ( Sample Date ) + 6 ; Day ( Sample Date ) ; Year ( Sample Date ) ) ;

LabSchedule = "Annual" ;

Date ( Month ( Sample Date ) ; Day ( Sample Date ) ; Year ( Sample Date ) + 1 ) ;

)

] ;

Case (

Customer# = "145" and not isEmpty ( newDate ) ; Month ( newDate ) & "/" & Year ( newDate ) ;

GetAsText ( newDate )

)

)

[HonkyCat]

Hi,

Thanks for the response. I know it says I'm working on Dev 7; but for this scenorio it is actually for FileMaker 6. So I'm thinking that solution won't work. -( Sorry about that. Any other suggestions?

[comment]

Yes it will. Define an interim calculation field cNewDate (result is Date ) =

Case (

LabSchedule = "Monthly" ,

Date ( Month ( Sample Date ) + 1 , Day ( Sample Date ) , Year ( Sample Date ) ) ,

LabSchedule = "45 Days" ,

Date ( Month ( Sample Date ) , Day ( Sample Date ) + 45 , Year ( Sample Date ) ) ,

LabSchedule = "Bimonthly" ,

Date ( Month ( Sample Date ) + 2 , Day ( Sample Date ) , Year ( Sample Date ) ) ,

LabSchedule = "Quarterly" ,

Date ( Month ( Sample Date ) + 3 , Day ( Sample Date ) , Year ( Sample Date ) ) ,

LabSchedule = "120 Days" ,

Date ( Month ( Sample Date ) + 4 , Day ( Sample Date ) , Year ( Sample Date ) ) ,

LabSchedule = "Semi-Annual" ,

Date ( Month ( Sample Date ) + 6 , Day ( Sample Date ) , Year ( Sample Date ) ) ,

LabSchedule = "Annual" ,

Date ( Month ( Sample Date ) , Day ( Sample Date ) , Year ( Sample Date ) + 1 ) ,

)

Now make your final calculation field (result is Text) =

Case (

Customer# = "145" and not isEmpty ( cNewDate ) ,

Month ( cNewDate ) & "/" & Year ( cNewDate ) ,

DateToText ( cNewDate )

)

[HonkyCat]

Thank you "Comment"! That worked perfect!!

Kristine