Tagged with datedif - Visokio Forums

Demo: Dynamic calculation of investments with different maturity dates

paola — Tue, 17 Nov 2015 07:16:46 -0500

Simple demo attached, illustrating the use of DATEDIFF formula in the scenario where investments/deposits have different maturity date.
First step is calculation of number of days between today and the investment maturity date:

DATEDIFF(NOW, [Date], "days")

By using NOW function, this calculation will keep the report up-to-date, always returning the latest breakdown.
It is also possible to 'fix' the date and use DATEVALUE("dd/MM/yyyy") instead of NOW, e.g. DATEDIFF(DATEVALUE("01/10/2015"), [Date], "days") .
Second step is to create irregular time intervals, that will 'bucket' the records and be used for sum calculation.


IF(
[Difference days]<1,"matured",
[Difference days]<2,"1 day",
[Difference days]<8,"1 week",
[Difference days]=<31,"1 month",
[Difference days]<92,"3 months",
[Difference days]<184,"6 months",
[Difference days]<366,"1 year",
[Difference days]<1096,"3 years",
[Difference days]<1827,"5 years",
[Difference days]<2557,"7 years",
[Difference days]=<3653,"10 years",
[Difference days]>3653,"over 10 years",
null)

Aggregation and summing of investment amounts for each period is done in the Bar/Line view.

Formulae: Identify date/time in the next 60 minutes

nash — Mon, 24 Nov 2014 04:15:48 -0500

Hi, I have a date/time column. I would like to find out if there have been any other date/time within the next 60 minutes:

Date Result
15.05.2014 15:05:55 no
15.05.2014 17:10:55 yes
15.05.2014 17:20:55 no

I tried a similar logic as for moving average, but it didn’t work. I am not sure why. Maybe the date/time format works differently.

Formulae: Convert a time field (hh:mm) to an integer?

JamesDutton — Mon, 02 Jul 2012 21:44:30 -0400

I'm trying to create a formula that takes a time field (in the format hh:mm) and multiplies by a revenue figure it to create a billings model for people. However, in the current format I can't multiply by anything. Makes sense. So, my question is - is it possible to convert this field to an integer (i.e. convert hours & mins to mins) - e.g. 01:30 becomes 90. That 90 would be multiplied by their hourly rate - e.g. $100 = $900 billings.