I am seeking an Airtable expert to write several formulas per the below description.
Zapier will be feeding data into Airtable, so I would like to get these formulas in before developing Zapier workflows.
Link to Video Describing Requirements: https://www.useloom.com/share/facdd62943c44a578329e631bed17a85
Since, I cannot attach anything, below are the requirements as discussed in the above video link…
Within ‘Operating Table_Main Client Record’, the following formulas need be written:
– “Hours Since Proposal Sent” should count the number of hours (set as an integer and rounded up or down to the nearest whole number) since the proposal was sent relative to the current time (time zone = EST). If “Proposal Sent” is blank or set as “No”, then this field should be blank. Also, if “Proposal Accepted On” is not blank, then this field should be blank.
– “Days Since Proposal Sent (Actual)” should simply divide “Hours Since Proposal Sent” by 24 and be expressed as a decimal with one digit of precision. For example, 1.23 would be equal to 1.2 and 0.85 would be 0.9. If the “Hours Since Proposal Sent” field is blank, then this field should be blank as well.
– “Days Since Proposal Sent (Rounded)” will be a simple rounding function. If “Days Since Proposal Sent (Actual)” is blank, then this field should be blank as well. If “Days Since Proposal Sent (Actual”) is not blank, then the following rounding rules apply:
*Less than 1 = 0
*1.0 – 1.9 = 1
*2.0 – 2.9 = 2
*3.0 – 3.9 = 3
*4.0 – 4.9 = 4
*5.0 – 5.9 = 5
*6.0 – 6.9 = 6
*7.0 – 7.9 = 7
*Greater than 7.9 = 99
– “Lead Funnel Code” should be populated according to the following rules:
*If “Send Proposal?” is blank OR “Proposal Accepted On” is not blank, then “Lead Funnel Code” should be blank.
*If “Send Proposal?” is set as “No” AND “Proposal Accepted On” is blank, then “Lead Funnel Code” is set to “Cold” as this indicates a client with whom we spoke, but who did not want a proposal sent to them.
*If “Send Proposal?” is set as “Yes” AND “Proposal Accepted On” is blank, then “Lead Funnel Code” is set as follows:
Where “Days Since Proposal Send (Rounded)” is 0, set “Lead Funnel Code” to blank.
Where “Days Since Proposal Sent (Rounded)” is 1-7, set “Lead Funnel Code” as “Warm”.
Where “Days Since Proposal Sent (Rounded)” is 99, set “Lead Funnel Code” as “Cold”.
