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)