Conditional formula

[DaveMasters]

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

[TheTimeSavingCo]

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
)

[DaveMasters]

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.

[TheTimeSavingCo · Accepted solution]

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
)

[DaveMasters]

That works a treat! Thank you so much!