Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Conditional formula

Topic Labels: Formulas
Solved
Jump to Solution
1933 4
cancel
Showing results for 
Search instead for 
Did you mean: 
DaveMasters
5 - Automation Enthusiast
5 - Automation Enthusiast

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

 

1 Solution

Accepted Solutions

Ah, yes, thank you for explaining that to me.  I've put something together here that I think does what you're looking for?

Screenshot 2023-01-31 at 2.38.52 PM.png

The formula in use is:

IF(
  {Show Crew},
  {DAYS ON SITE} * 3,
  ({DAYS ON SITE} - {Show Length}) * 3
)

 

See Solution in Thread

4 Replies 4

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

Screenshot 2023-01-30 at 9.46.02 PM.png

IF(
  {Show Crew},
  DATETIME_DIFF({DEPARTURE DATE},{ARRIVAL DATE}, 'DAYS'),
  0
)

 

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.

Ah, yes, thank you for explaining that to me.  I've put something together here that I think does what you're looking for?

Screenshot 2023-01-31 at 2.38.52 PM.png

The formula in use is:

IF(
  {Show Crew},
  {DAYS ON SITE} * 3,
  ({DAYS ON SITE} - {Show Length}) * 3
)

 

DaveMasters
5 - Automation Enthusiast
5 - Automation Enthusiast

That works a treat! Thank you so much!