- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 29, 2023 07:16 PM
Hi, I am trying to create a formula to work out meal allocations for works at a festival site.
Currently I have date fields for 'ARRIVAL DATE' & 'DEPARTURE DATE' and a field for 'DAYS ON SITE' which uses the formula DATETIME_DIFF({DEPARTURE DATE},{ARRIVAL DATE}, 'DAYS') to calculate the days that any given crew member will be on site. I use another field 'Crew Meals' with the formula SUM({DAYS ON SITE}*3)
Through the actual show component of the event, we only feed crew members that are actively working ie audo / lighting operators. I have included a check box field 'SHOW CREW' for crew members to check if they work on the show. I would like to create a formula (potentially an extension of the 'CREW MEALS' formula) which takes into consideration the 'SHOW CREW' check box status.
For example - departure date minus arrival date = days on site minus 4 days, if the check box is unchecked. OR
departure date minus arrival date = days on site minus 0 days, if the check box is checked, to indicate the crew member is working through the show.
Any help would be greatly appreciated. Thank you
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 30, 2023 10:40 PM
Ah, yes, thank you for explaining that to me. I've put something together here that I think does what you're looking for?
The formula in use is:
IF(
{Show Crew},
{DAYS ON SITE} * 3,
({DAYS ON SITE} - {Show Length}) * 3
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 30, 2023 05:47 AM
Hm, the "minus 4 days" bit confuses me a bit, but on the off chance the following screenshot looks right to you, the formula for `DAYS ON SITE` is below. Do let me know where I got it wrong if I did and I'll see what I can do to help
IF(
{Show Crew},
DATETIME_DIFF({DEPARTURE DATE},{ARRIVAL DATE}, 'DAYS'),
0
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 30, 2023 12:17 PM
Thanks for the reply Adam.
To clarify the 'minus 4 days', here is an example..
Crew member #1 arrives on the 10th and leaves on the 24th and has indicated, by checking the 'show crew' box, that they are a part of the show crew. The show goes for 4 days. This is 15 days on site, multiplied by 3 meals a days = 75.
Crew member #2 arrives on the 10th and leaves on the 24th and has indicated, by NOT checking the 'show crew' box, that they are NOT a part of the show crew. Therefore, the calculation should be like more like, 15 days onsite, minus 4 days of show (4 days x 3 meals = 12 meals) 15 days x 3 meals = 45 meals MINUS 4 days (show) x 3 meals. 45 meals - 12 meals = 33 meals in total.
I hope that makes more sense. I appreciate your help. Thank you.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 30, 2023 10:40 PM
Ah, yes, thank you for explaining that to me. I've put something together here that I think does what you're looking for?
The formula in use is:
IF(
{Show Crew},
{DAYS ON SITE} * 3,
({DAYS ON SITE} - {Show Length}) * 3
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 31, 2023 11:59 AM
That works a treat! Thank you so much!
