Please advise the formula to report the # days past within the selected periods.

My objective is to provide occupancy percentages for each month and YTD as well. Ideally, only days past would be included in the statistics.

eg. 100 Rooms available each day in January equals 3100 rooms available to sell. Sold rooms of 2100 would equate to 67% occupancy.

Hello Fullen,

Assuming you'd like the number of days in the selected fiscal period, the easiest way is to retrieve the starting and ending dates for the period, subtract them and add 1. The formula to do this would be:

This is a start...

This formula only relates to the # of days in the current month of the report.

I am looking to complete a report for the year, something like this:
I have 99 Rooms Available each day:
YTD NOV DEC JAN FEB MAR APR MAY
Rooms Sold 7700 800 2400 2000 2500
Rooms Avail 11800 2970 3069 3069 2772
# Days Avail 120 30 31 31 28
Occupancy % 65% 26% 78% 65% 90%

The formula provided will only provide the # days in the reported fiscal month. For past months, the # changes to the current month's number of days.

I could just drop the number of days on a line and show the occupancy each month but the YTD stats don't jive. Perhaps I just need the formula for the YTD column.

I have it working now by using IF statements so that if there are zero rooms sold, the values of the future months are listing zero rooms available and thereby not added into the YTD.

This doesn't work when all past months are not represented on the report. For instance,

MTD YTD LY MTD LY YTD

Hello Fullen,

You can find the number of days between two dates by subtracting them. For example:

gives the number of days YTD. There are a comprehensive list of date functions in the financial reporter (check the help). For example:

will tell you the number of days that have elapsed in the current month. So would:


You can use an ADGET() call to return the beginning ("gldates.START) and ending ("gldates.END") date of any specific period using the keys:


You can add or subtract the dates returned by the ADGET function just as you can add or subtract a date returned by the DATE() or TODAY() functions.

OK?

I will try these... not sure if it will work for my monthy stats YTD occupancies
Thank you for your help.

Hi Fullen,

To calculate the number of days in YTD use:


OK?

=(ADGET("gldates.END","C"&FPERIOD())-ADGET("gldates.START","C 1"&FPERIOD()))+1

This works for me for current year and P - P 1 for previous year

YEAH!

Hi Fullen,

You don't need the "&FPERIOD()" in the second ADGET() call. The string "C 1" asks the ADGET function to return the starting date for fiscal period 1 in the current year.

Glad it's working for you.

Andrew

Just to finish this off, If I use the following string do I still need the &FPERIOD() with the "P 1"?:

=E24/(((ADGET("gldates.END","P"&FPERIOD())-ADGET("gldates.START","P 1"&FPERIOD()))+1)*99)

Hello Fullen,

No, you do not need the second FPERIOD() call.

The formula should be:


This formula subtracts the ending date for the current period last year (because you are using "P" before the period number - for the current year you'd use "C") from the starting date of last year's fiscal year.

(Presumably you have 99 rooms available each day, and E24 contains your total room revenue).

No, you do not need the 2nd FPERIOD().

FPERIOD() returns the number of the Fiscal Period that you have selected (i.e. 1 through 12).

Andrew uses the first FPERIOD() to create the parameter to the function ADGET(). For instance, in period 12 you need "P12". It varies depending on the Period you choose.

But in the 2nd ADGET(), you already know that you want "P 1". It will never change. Therefore FPERIOD() is not needed.