You are not logged in.

Dear visitor, welcome to Palo Community Forum. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

lawa

Master

  • "lawa" is male
  • "lawa" started this thread

Posts: 41

Date of registration: Jan 28th 2009

Location: Dresden

  • Send private message

1

Friday, December 2nd 2011, 12:00pm

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

kratzer

Master

  • "kratzer" is male

Posts: 87

Date of registration: Feb 23rd 2006

Location: Karlsruhe

Occupation: IT

  • Send private message

2

Saturday, December 3rd 2011, 3:42pm

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?

lawa

Master

  • "lawa" is male
  • "lawa" started this thread

Posts: 41

Date of registration: Jan 28th 2009

Location: Dresden

  • Send private message

3

Thursday, December 8th 2011, 10:13am

...since there seems to be no In-Palo-Solution so far I did it via Excel... :thumbdown:

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

Posts: 318

Date of registration: Dec 2nd 2009

Hobbies: I'll swallow your soul! I'll swallow your soul!I 'll swallow your soul!

  • Send private message

4

Wednesday, January 4th 2012, 5:10pm

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

"A problem worthy of attack proves its worth by fighting back." Piet Hein

Rate this thread