Skip to main content
Solved

Conditional formula

  • January 30, 2023
  • 4 replies
  • 50 views
D
Forum|alt.badge.img+2

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

 

Best answer by TheTimeSavingCo

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?

The formula in use is:

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

 

4 replies

TheTimeSavingCo
Forum|alt.badge.img+31

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 )

 

Adam, Airtable Consultant - thetimesaving.company
D
Forum|alt.badge.img+2
  • DaveMasters
  • Author
  • New Participant
  • January 30, 2023

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 )

 


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
Forum|alt.badge.img+31

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?

The formula in use is:

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

 

Adam, Airtable Consultant - thetimesaving.company
D
Forum|alt.badge.img+2
  • DaveMasters
  • Author
  • New Participant
  • January 31, 2023

That works a treat! Thank you so much!

TheTimeSavingCo
Terms & ConditionsAccessibility statement