Help

Conditional formula

Topic Labels: Formulas
Solved
Jump to Solution
1098 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!