Jump to content

days in residence in month


This topic is 7856 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

Hi, I have been trying to figure this out for a while. Using FM 5.5, Windows. I have created a homeless shelter resident tracking system.

Among the fields are "datein" and "dateout". I need to run reports monthly showing how many days the residents were here in that month. THere are residents that were there all month, some that came in during the month and left in that month and others that came in during the month and left either the next month or are still here. The date out may be empty if the resident is still here. So, for each resident that was here in the month, I need to determine how many days the resident was here.


J. Smith - 5

B. Smith - 21

G. Jones - 30

I want to set this up so I can do this every month and eventually do annual reports.

Sorry for the length of posting and thanks in advance for any help


Link to comment
Share on other sites

Let's say you have a number field, ReportMonth

Calculate the FirstDay of the ReportMonth:

Date( Month(ReportMonth), 1, Year(ReportYear))

Calculate the LastDay of the ReportMonth:

Date(Month(ReportMonth) + 1, 0, Year(ReportYear))

## That's a fun way of doing it that I credit to Djukic Goran.

The less evil way would be:

Date(Month(ReportMonth) + 1, 1, Year(ReportYear)) - 1

First you'll need to find everyone, you can script this:

Request1: datein =< LastDay / dateout => FirstDay

Request2: datein =< LastDay / dateout = "" (blank)

Hint: Set Field doesn't work very well when scripting date finds. Use Insert Calculated Result, but make sure the script goes to a layout that the datein and dateout fields are on.

Then you'll have to test for a number of possibilities with a Case() calculation -- calculate the End Date first, then subtract the Start Date:

Case(dateout < LastDay and not IsEmpty(dateout), dateout, LastDay) -

Case(datein < FirstDay, FirstDay, datein) + 1

The plus one is there because I assume if someone checks in and out the same day you count it as one day, not zero.

Don't worry about the length of your post, I thought it was clear and concise. I've seen a lot worse!

Link to comment
Share on other sites

This topic is 7856 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.