PRORATA function

  • 21 December 2021
  • 0 replies
  • 2080 views
Samya Barkaoui
Rank
Userlevel 5
Badge +3

 

Description

Returns the prorata of days over time Dimensions. It can be used to calculate, for example, the Full Time Equivalent (FTE) of Employees by knowing their start and end date for example.

 

Syntax

PRORATA(Time Dimension [, Start Date] [, End Date] [, Working Days] [, Holidays])
  • Time Dimension based on the calendar settings, options are: Day, Month, Quarter, Year
  • Start Date is included
  • End Date is excluded
  • Working Days defines which days of the week are working and non working days. User must use a Metric of the Dimension Day of Week ⇒ boolean.
  • Holidays defines which dates are holidays. User must use a Metric of the DimensionDay⇒ boolean

 

Return type

Number

 

Examples

Case

Results

Return Type

PRORATA(month)

returns 1 for each month

Number

PRORATA(month, DATE(2020,6,1))

returns 1 for each month, starting the 1st of June 2020, blank prior to that date

Number

PRORATA(month, DATE(2020,6,15), DATE(2020,7,14))

returns the prorata of days over June 2020 (16/30) and July 2020 (13/31).

Number

PRORATA(month, 'employee'.'start date', 'employee'.'end date'+1)

returns the monthly FTE (full time equivalent) by employee taking into account their start (included) and end date (included).

Number
PRORATA(month, STARTOFMONTH('employee'.'start date'), STARTOFMONTH('employee'.'end date'+ 1)) returns the monthly Headcount by employee (1 if the employee is present on the last day of each month) Number

 

Excel equivalent: None

See also: DAYSINPERIOD, STARTOFMONTH

 

 

Samya
This topic has been closed for comments

Ask your question here!

Terms & ConditionsCookie settings