Excel DATE and EDATE functions
Hi,
Do the Excel "DATE (YEAR, MONTH, DAY)" and EDATE functions work in Data Flow in Board - I tried, but it does not return any values. The cube is just empty - looks like Board is just ignoring any calculation using these functions.
Answers
-
Dear Vikash G,
can you please describe a little more detaillized what you want to do with these formulars?
For general you can use today() e.g. to write actual time stamp to text cube.
regards
Alexander Kappes
0 -
Hi Alexander Kappes,
I am trying to use these formulas to add a certain number months to a specific date. Similar to what EDATE does when used in excel, but then I use this function directly in data flow, it does give any result. I am trying to replicate a depreciation model in Board. I have Asset life (in months), Asset purchase date and Asset Value. I am trying to replicate a straight line method model - for which I was looking to get the final month of the Asset by adding the Asset life to Asset Purchase date. In Excel this works - EDATE(Asset Purchase Date, Asset Life).
Regards,
Vikash
0 -
Hi Vikash G,
now I understand. You have starting date, months to use and want to calculate the "Enddate of use".
For that you didn´t need any formular in BOARD. You can directly calculate with the date cube. You just have to add the number of days to your startdate.
See my little sample
Hope it helps
regards
Alexander Kappes
1 -
Hi Vikash G,
in addition to Alexander Kappes you can use the SLN Formula for calculating the depreciation.
It is: SLN(Initial cost of asset, Asset value at the end of the depreciation, Periods over which asset is depreciated)
BRDietmar
2 -
Hi Alexander Kappes,
I am able to add number of days to an existing date in my data flow algorithm and it works as you have mentioned too.
However is there a way we can add a certain number of months (not days) to an existing date? Basically trying to look for an EDATE equivalent formula in Board.
I trying to allocate the depreciation value across each of month that the Asset is valid for (I.e. from the Purchase Date to Purchase Date + Asset Life(in months)).
Regards,
Vikash
0 -
Dear Vikash G,
to calculate from month to day, you just have to calculate the number of months with 365 and divide by 12 :-)
Means you can directly use in DataFlow.
e.g. like this
Hope it helps
regards
Alexander Kappes
0 -
Thank you for the SLN option. I will make use of this function to get the depreciation value.
Regards,
Vikash
0 -
Hi Vikash G,
or instead of Alexander Kappes Option you can use a dataflow to mark every month für depreciation in a dataflow:
Sorry for the german text. It is an example from a customer. The calculations of the algorithms are in the text.
BR
Dietmar
0 -
Hi Alexander Kappes,
Thank you very much, I just did that and got what I wanted to achieve
Regards,
Vikash
0
