Skip to main content
Question

Counting Days

  • October 21, 2019
  • 0 replies
  • 99 views
Forum|alt.badge.img+8
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
------------------------------
This topic has been closed for replies.

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 1
Assumption:
  • 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 2
Assumption:
  • 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 Westbrook
PRIME Developer
Harder Mechanical Contractors Inc.
Portland OR
------------------------------
Terms & ConditionsAccessibility statement