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?

Thanks

Lars
Hello,
do you need the year really dynamically? Can't you determine it once in Excel and import it as a static attribute for your dates?
...since there seems to be no In-Palo-Solution so far I did it via Excel...

However, on the wishlist I would put on top Date Calculation rules like year, month, day a.s.o like in Excel but as number, not as text....

greetz from an annoyed Palo-user
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.
laloune

Post hoc, non est propter hoc