Date formatting within a calculation field

[arbrctb]

Hi there,

I could really use some help with an issue I'm having... I'm might be overcomplicating things as I'm an FM newbie.

I'm trying to get "Date of Death: DD.MM.YYYY" to display within a block of text as a merge field if a date of death has been entered into the relevant field. My issue is, how can I get FM to remove "Date of Death: " from my block of text if the field is empty? I've tried using conditional formatting but my merge fields are within a large text block and I can't find a way to just conditionally format the words "Date of Death: " to disappear - I have to select the entire text block, resulting in the entire text block disappearing.

I've tried making a separate text block containing a merge field, dragging it where I'd like it to be placed and applying conditional formatting, but this then means that it's not always placed exactly where I want it and doesn't move and flow with the other text within my field.

I've tried getting around this by using a Calculation field to prefix the date with "Date of Death: " if data has been entered and using this new calculated field as a merge field. Problem is, the date is then formatted as DD/MM/YYYY with slashes instead of periods, which is not what I want. Date format options within the layout don't seem to have any effect, I presume because the field has already been calculated as text.

Does anyone have any suggestions?

Cheers

Adrian

Edited September 3, 2009 by Guest

[comment]

You need to use a calculation field like =

Case ( DateOfDeath ; "Date of death: " & Substitute ( DateOfDeath ; "/" ; "." ) )

or (preferably) =

Case ( DateOfDeath ; "Date of death: " & 

SerialIncrement ( "00" ; Day ( DateOfDeath ) ) & "." & 

SerialIncrement ( "00" ; Month ( DateOfDeath ) )  & "." & 

SerialIncrement ( "00" ; Year ( DateOfDeath ) )

)

[arbrctb]

Thanks for that, your help is appreciated. How would I go about removing the leading zeros from the date? i.e. to get 1.2.87 instead of 01.02.87?

[comment]

You said you wanted DD.MM.YYYY. If you want D.M.YYYY, don't use the SerialIncrement() function:

Case ( DateOfDeath ; "Date of death: " & 

Day ( DateOfDeath ) & "." & 

Month ( DateOfDeath ) & "." & 

Year ( DateOfDeath )

)

If you really want YY instead of YYYY, change the year part to:

Right ( Year ( DateOfDeath ) ; 2 )