How to Calculate 18 years age flag- Scriptless Siebel

This article has been contributed by Praful Desai a reader of Siebel Unleashed.

Requirement:

You need to calculate if a person is underage or not i.e less than 18 years of age or not. You need to return Y if they are less than 18 years (Underage) and N (Not Underage) if they are more than 18 years. It should also take care of people born in leap year.

Solution:

On the first look it typically looks like solution that can be achieved with minor scripting but Praful Desai shows us how we can use ToChar functions in side calculated fields to achieve the solution without any scripting.

  1. Create Calculated Field of todays date in YYYYMMDD Format:
    e.g: Formatted Today Date : ToChar(Today(),’YYYY’) + ToChar(Today(),’MM’) + ToChar(Today(), ‘DD’)
    Field Type: DTYPE_NUMBER
  2. Create another Calculated Field Birth date in YYYYMMDD Format:
    e.g: Formatted Birth Date : ToChar([Birth Date], ‘YYYY’) + ToChar([Birth Date], ‘MM’) + ToChar([Birth Date], ‘DD’)
    Field Type: DTYPE_NUMBER
  3. Set the Calculated Age Under 18 Flag as mentioned below:
    Age Under 18 Flg : IIf(([Formatted Today Date] – [Formatted Birth Date]) < “180000”, “Y”, “N”)
    Field Type: DTYPE_TEXT

 

This should give you an idea how to powerful calculated fields can be.

9 Responses to How to Calculate 18 years age flag- Scriptless Siebel

  1. Brilliant! Some constructive feedback; I would use ‘<=' to let it work on the birthdate itself and I would lose the IIF(), resulting in only ([Formatted Today Date] – [Formatted Birth Date]) < "180000″ and use DTYPE_BOOL instead of DTYPE_TEXT. Also mind the Microsoft Word copy/pasting or disable the curly braces feature.

    • That is a good suggestion. I will update the post with this suggestion also, but I didn’t get the curly braces part? Anything wrong with the way it is appearing??

  2. Neel, “180000″ is not the same ‘text’ as “18000”. More specific: “″ are not ASCII whereas ” is. Copy/pasting from here to Siebel Tools will result in the infamous ‘squares’ or it just doesn’t work.

    Don’t bump your head to the screen looking too closely to the braces 😉

    • I don’t think this is accurate, because in 18 years, there’s at least 4 and sometimes 5 leapyears. Calculations based on intervals will be challenged bu accuracy. IMO the only thing that counts is the birthdays and year notation.

  3. Can you please explain the logic behind ([Formatted Today Date] – [Formatted Birth Date]) < "180000″. 180000 how its related to 18 years of age.

  4. I have a requirement to default the Year in birth date to 1901 instead of how its defaulting it to current year in Contact BC, Birth date field.

    for example if user enters the date and month in the Birth date field, the Year should be defaulted to 1901 instead of 2014.

    Please help!

  5. Create Calculated Field of todays date in YYYYMMDD Format:
    e.g: Formatted Today Date : ToChar(Today(),’YYYY’) + ToChar(Today(),’MM’) + ToChar(Today(), ‘DD’)
    Field Type: DTYPE_NUMBER
    Create another Calculated Field Birth date in YYYYMMDD Format:
    e.g: Formatted Birth Date : ToChar([Birth Date], ‘YYYY’) + ToChar([Birth Date], ‘MM’) + ToChar([Birth Date], ‘DD’)
    Field Type: DTYPE_NUMBER
    Set the Calculated Age Field as mentioned below:
    Age : ([PTS1 Formatted Today Date]-[PTS1 Formatted Birth Date])/10000
    Field Type: DTYPE_INTEGER

    Calculated Age Field.
    Have a nice day 🙂
    Nir

Leave a Reply

Contribute