Counting Days

Question

I am having a difficult time in figuring out how to do this...
Associates have their hotel length of stay recorded in two fields "Hotel Check-In Date" and "Hotel Check-Out Date"
One of the functions for this is to determine the amount of per diem owed to an associate.

The report that is currently being used is for the manager to select a date range for example start date is 10/21 and end date is 10/26. If an associate starts on the 21st or later... we are fine. However, this will not account for associates that started on 10/14 working thru 10/26.

Somehow I need to build a report that will say "provide me a list of all associates that are working between dates 10/21 and 10/26 no matter whether started on 10/14 or even earlier.

Any idea how to build this? It would be GREATLY appreciated!

------------------------------
Hans Hamm
------------------------------

evanwestbrook · Answer

Hans,If I understand correctly, you have a report that needs to display all associates staying at a hotel in a given date range. In your example, an associate with a start and end date of 10/14 and 10/26 should show up in a report for associates staying in hotels on the dates 10/21 through 10/26 even though 10/21 is not explicitly outlined in this associate's stay.I'm not sure if this can be done using the dynamic date range filter, as a formula field would need to reference a dynamic filter input. However, we may be able to think up something similar. Are there any business rules your user has that could be used to simplify the filter? Here are a couple of examples:Example 1Assumption:

End user just wants to see all associates in a hotel in the last 7 days

Solution:

Create a checkbox-formula field that flags if the check out date is within the last seven days.
Set this formula field as the dynamic filter instead of a date range

Example 2Assumption:

End user just wants to view dates based on some business time unit (i.e. week ending date, billing period, etc.)

Solution:

Create a text-formula field that translates check in and check out date to the business time unit
Set this formula field as the dynamic filter instead of a date range

Are there any ways you can think of to translate the business needs for the date range?------------------------------Evan WestbrookPRIME DeveloperHarder Mechanical Contractors Inc.Portland OR------------------------------

hanshamm · Answer

Evan,The 2nd example is closer to what is needed.If I was going to lay it out it would be something like this...

Show me a count of nights for associates in hotel rooms from 10/21 thru 10/26

Would give me 6 nights for both Joe and Sally

&nbsp;
14-Oct
15-Oct
16-Oct
17-Oct
18-Oct
19-Oct
20-Oct
21-Oct
22-Oct
23-Oct
24-Oct
25-Oct
26-Oct

Joe
X
X
X
X
X
X
X
X
X
X
X
X
X

Sally
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
X
X
X
X
X
X

Show me a count of nights for associates in hotel rooms from 10/14 thru 10/26

Would give me 13 nights for Joe 6 nights for Sally

&nbsp;
14-Oct
15-Oct
16-Oct
17-Oct
18-Oct
19-Oct
20-Oct
21-Oct
22-Oct
23-Oct
24-Oct
25-Oct
26-Oct

Joe
X
X
X
X
X
X
X
X
X
X
X
X
X

Sally
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
&nbsp;
X
X
X
X
X
X

------------------------------Hans Hamm------------------------------

markshnier__you · Answer

Here is a thought.If this is a low usage situation by very few users, who need this filter. Then make a single record in a new table called Analysis Dates.&nbsp; it will be Record ID# 1 and it will have two date fields for Analysis Start date and Analysis End Date.Make a relationship down to your detail records.&nbsp; Now you can use these date fields to either filter records or to calculate the #&nbsp;of days within the hotel stay period which are within the Analysis Date range.If you like, the records can be displayed directly on the Analysis date range record as an embedded report.&nbsp; But if not, I suggest that you display the Analysis Date range on the report itself as Group By headers or columns so the user knows what extra date filters are being used.&nbsp;​------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commarkshnier2@gmail.com------------------------------

markshnier__you · Answer

.. and if its multi user there is a solution for that too.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commarkshnier2@gmail.com------------------------------

evanwestbrook · Answer

Hans,Mark has some good ideas below. I'm responding in a different branch of this thread just to keep our suggestions straight for you.To accomplish what is mentioned in Example 2, you could create a library table called something like "Weeks" (or whatever your consistent time unit is.) Here's how you might set it up:

&nbsp;Fields: [Week Description], [Week Start Date], and [Week End Date].
Relate "Weeks" to "Hotel Stays" table one to many (one week can have many hotel stays.)
Set up an automation to automatically link a [Hotel Check-In Date]&nbsp; [Hotel Check-Out Date] pair on "Hotel Stays" to the parent Weeks table. (See Brian's solution on response 3 for an example)
Set up report on "Hotel Stays" table to filter based on "Week" for the hotel stay instead of the start and end date.

i.e. "Show me the #&nbsp;of nights in Week Ending 10/20" instead of "Show me the #&nbsp;of nights between "10/14" and 10/20"

If a truly dynamic feature is what you're after, ​​​Mark's solution would be good.------------------------------Evan WestbrookPRIME DeveloperHarder Mechanical Contractors Inc.Portland OR------------------------------

Forum Discussion

Counting Days

Related Content

Jinja Question: Adding business days to today

Calculating number of days between two date fields

Add 1 day to date formula

Time of Day field values are not in 24-hour format in Pipelines

Jinja - Count Business Days