Skip to main content

Hello Everyone,



I am wondering if it’s possible to set up a field to show me the exact number of people present in my base on a certain day.



Each record in the main table has an arrival date, departure date, group size.



I have a second table that I want to be able to display the number of people present each day of the week.



The system I have in place right now assigns an arrival week number and year with this formula WEEKNUM({Arrival Date},‘Monday’)&’ - '&YEAR({Arrival Date},‘Monday’)


then I simply link the table and do a rollup of the Group size and display the number of people present each week.



The issue with this is that if they arrive on a Sunday it assigns them the previous week number and rolls them up into the previous week. The other issue is if they only stay for 3 days it still displays them for the whole week.



I would love to come up with a way to display the exact number of people present each day rather then week.



Any thoughts or advice is much appreciated.

Hmm, if you’re just looking to do this on a case by case basis where you’re alright with keying in the date that you need the total number of people for, you could





  1. Link all of your records to a single record in another table


  2. In that other table, have a date field where you put in the date that you want to know the number of people were present for


  3. In the main table, do a lookup field to grab that date, and use a formula field to check whether that date is within the arrival and departure dates


  4. In the other table, do a conditional rollup field based off of the formula field, and use a SUM(values) as the aggregation formula




This means that the data would only ever exist when that date field from point 2 was filled in with the date you wanted though





If you’re looking for the data to just exist and for you to be able to look at it at any point of time / manipulate it with automations etc, your current setup where you assign the week and year and link to a different table is the only way I can think of doing it, you’d just do that for all the days within the arrival and departure dates for that record



Getting those dates would be pretty tedious though and the easiest way to do that would be with a script I think. At that point, if you know scripting, you might as well just do the entire thing in a script really.



You can hire me to create either of the scripts for you too!

Hmm, if you’re just looking to do this on a case by case basis where you’re alright with keying in the date that you need the total number of people for, you could





  1. Link all of your records to a single record in another table


  2. In that other table, have a date field where you put in the date that you want to know the number of people were present for


  3. In the main table, do a lookup field to grab that date, and use a formula field to check whether that date is within the arrival and departure dates


  4. In the other table, do a conditional rollup field based off of the formula field, and use a SUM(values) as the aggregation formula




This means that the data would only ever exist when that date field from point 2 was filled in with the date you wanted though





If you’re looking for the data to just exist and for you to be able to look at it at any point of time / manipulate it with automations etc, your current setup where you assign the week and year and link to a different table is the only way I can think of doing it, you’d just do that for all the days within the arrival and departure dates for that record



Getting those dates would be pretty tedious though and the easiest way to do that would be with a script I think. At that point, if you know scripting, you might as well just do the entire thing in a script really.



You can hire me to create either of the scripts for you too!


Hi, you can purchase formulas that will do this up to the year 2025 on my Gumroad store: Formula: Extract dates between two date fields

You can select the output of the date format (friendly, US, European, ISO) as well

Reply


Terms & Conditions