Skip to Content
0

Calculate difference of 2 dates in SAP BPC 10.0 NW

Dec 21, 2016 at 08:01 AM

119

avatar image
Former Member

Hello Experts

For the purpose of calculating "End of service benefit" in HR Planning, we have a requirement to calculate the difference between joining date and current date.

We have loaded the "Date of Joining" attribute from 0Employee info object in BPC dimension.If the user enters the current date , it should calculate the difference.

for eg:employee xxx has joining date as 01/12/2016 and current date is 31/12/2016.Diff is 30 days.

The challenge above is that the "Date of Joining" is in EPMMemberProperty(, EPMMemberID(B14), "DOJ") and current date is in "Date format" due to which I am getting and "#VALUE" error.Is there any solution in which I can copy the EPMMember property to date format? or any epm formula?

regards

ATG

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Best Answer
Vadim Kalinin Dec 21, 2016 at 08:26 AM
0

What is the date format you have in DOJ property? YYYYMMDD?

Then:

=DATE(LEFT(EPMMemberProperty(, EPMMemberID(B14), "DOJ"),4),MID(EPMMemberProperty(, EPMMemberID(B14), "DOJ"),5,2),RIGHT(EPMMemberProperty(, EPMMemberID(B14), "DOJ"),2))

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jan 17, 2017 at 11:33 PM
1

Here's an alternative (and for some people, fun) way of mapping a date strings to excel dates using only one set of EPM formulas... Perhaps it's overkill, but i thought I'd share anyway.

=DATEVALUE(TEXT(SUM(MID(<reference to date string>,{<month start index>,<day start index>,<year start index>},{2,2,4})*10^{6,4,0}),"00-00-0000"))

and in your case:

DDMMYYYY
=TODAY()-DATEVALUE(TEXT(SUM(MID(EPMMemberProperty(, EPMMemberID(B14), "DOJ"),{3,1,5},{2,2,4})*10^{6,4,0}),"00-00-0000"))

YYYYMMDD
=TODAY()-DATEVALUE(TEXT(SUM(MID(EPMMemberProperty(, EPMMemberID(B14), "DOJ"),{5,7,1},{2,2,4})*10^{6,4,0}),"00-00-0000"))

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 21, 2016 at 08:48 AM
0

Hello Vadim,

Thanks for the reply

The Date is in the format DDMMYYYY

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Please delete duplicate answer

0
Vadim Kalinin Dec 21, 2016 at 08:50 AM
0

For DDMMYYYY:

=DATE(RGHT(EPMMemberProperty(, EPMMemberID(B14), "DOJ"),4),MID(EPMMemberProperty(, EPMMemberID(B14), "DOJ"),3,2),LEFT(EPMMemberProperty(, EPMMemberID(B14), "DOJ"),2))

Difference:

=TODAY()-DATE(RGHT(EPMMemberProperty(, EPMMemberID(B14), "DOJ"),4),MID(EPMMemberProperty(, EPMMemberID(B14), "DOJ"),3,2),LEFT(EPMMemberProperty(, EPMMemberID(B14), "DOJ"),2))

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Dec 21, 2016 at 08:48 AM
0

Hello Vadim,

Thanks for the reply

The Date is in the format DDMMYYYY

Share
10 |10000 characters needed characters left characters exceeded