Calculation with dates and years

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • Calculation with dates and years

    Hello Community,

    I am struggeling with date calculation and need some help:

    I have a list of staff with birthdate, employment start and -end date input in Excel format and need to calculate who and when they may go into pension or have a anniversery of service (10 + 25 yrs.). All this staff data are attributes and the following calculation will be in attribute cube.

    Converting the Excel date into Palo date is no problem. [ (Exceldate - 25569) * 86400 = Palodate]

    The problem I have is cutting off the years lower than 1970 and how than to calculate with the years.
    When I get the year from the palodate I use DATEFORMAT and get only text and no years lower than 1970.

    Are there any ideas and tips or workaround for doing this?


  • Hi,

    this is possible using a rule. I set up an element name "date" (S) and "date timestamp" (N)

    using the following rule, this is possible to convert the date into a timestamp:
    ['date timestamp'] = N: DATEVALUE(CONCATENATE(CONCATENATE(CONCATENATE(MID(['date'],4,2),"/"),CONCATENATE(MID(['date'],1,2),"/")),CONCATENATE(MID(['date'],7,4),"/")))

    in my case it was a little bit more complex, as I used french standard format "dd/mm/yyyy". DATEVALUE indeed requires "mm/dd/yyyy" format as parameter format, so I had to switch days and month.

    I hope it helps and I hope I understood your problem well.

    Post hoc, non est propter hoc