I have four Airtable fields {Total Cost}, {Event Date & Time}, {Equipment Charge}, {Event Duration}, and {Number Of Techs}
If the {Event Date & Time} happens during the Day time, the rate is $69.
If {Event Date & Time} happens during the Evening the rate is $79.
If the {Event Date & Time} happens during any holidays, the rate is $105.
If the {Event Date & Time} happens during a weekend, add $60 to the {Total Cost}.
I created a formula to calculate the {Total Cost} but it is not working. Your help will be very appreciated.
See the Formula below:
------------------------------------------------
IF(
DATETIME_FORMAT({Event Date & Time}, 'h A') >= '7AM'
AND(
DATETIME_FORMAT({Event Date & Time}, 'h A') < '7 PM'),
{Event Duration} * {Number Of Techs} * 69,
IF(
DATETIME_DIFF({Event Date & Time}, DATETIME_PARSE('2023-01-01'), 'days') IN [0,1,2,361],
{Event Duration} * {Number Of Techs} * 105,
{Event Duration} * {Number Of Techs} * 79
)
) + IF(
DATETIME_FORMAT({Event Date & Time}, 'ddd') IN ['Sat', 'Sun'],
{Total Cost}+60,
0
)
