Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

# Help! Trying to create formula to calculate # of hours or days based on selection

Topic Labels: Formulas
Solved
229 4
cancel
Showing results for
Did you mean:  5 - Automation Enthusiast

Hello! New Airtable user here! I’ve been racking my brain for days trying to figure this out. I am trying to create a formula based on a selection.

What I am trying to accomplish is:
If the rate is HOURLY, calculate date/time difference for # of hours
If the rate is DAILY, calculate date/time difference for # of days
if the rate is TOTAL, calculate the rate X 1

Any help or suggestions would be awesome! 1 Solution

Accepted Solutions  18 - Pluto

Welcome to the community, @Erin_McKinnon!

It looks like you’re trying to do 2 different things in your description — do you want to calculate the total rate (which is what you specified for “Total” above), or do you want to calculate the MULTIPLIER that you will eventually use to multiply against rate to get the total rate (which is what you specified for “Hourly” and “Daily”)?

I’m going to assume that you’re looking for the multiplier. Below is what your formula would look like to get the multiplier.

(Note that you’ll need to use “minutes divided by 60” (instead of “hours”) to calculate the hourly rate, because the `DATETIME_DIFF` function only returns integer values… it never returns decimal values.)

``````IF(
Hourly,DATETIME_DIFF({End Time},{Start Time},'minutes')/60,
IF(
Daily,DATETIME_DIFF({End Time},{Start Time},'days'),
IF(
Total,1
)))
``````

It might also be better for you to combine your 3 checkbox fields into one single-select field, to prevent users from checking more than one checkbox.

Assuming that you combine your 3 checkbox fields into one single-select field called “Billing”, this is what that formula would look like:

``````IF(
Billing="Hourly",DATETIME_DIFF({End Time},{Start Time},'minutes')/60,
IF(
Billing="Daily",DATETIME_DIFF({End Time},{Start Time},'days'),
IF(
Billing="Total",1
)))``````
4 Replies 4  18 - Pluto

Welcome to the community, @Erin_McKinnon!

It looks like you’re trying to do 2 different things in your description — do you want to calculate the total rate (which is what you specified for “Total” above), or do you want to calculate the MULTIPLIER that you will eventually use to multiply against rate to get the total rate (which is what you specified for “Hourly” and “Daily”)?

I’m going to assume that you’re looking for the multiplier. Below is what your formula would look like to get the multiplier.

(Note that you’ll need to use “minutes divided by 60” (instead of “hours”) to calculate the hourly rate, because the `DATETIME_DIFF` function only returns integer values… it never returns decimal values.)

``````IF(
Hourly,DATETIME_DIFF({End Time},{Start Time},'minutes')/60,
IF(
Daily,DATETIME_DIFF({End Time},{Start Time},'days'),
IF(
Total,1
)))
``````

It might also be better for you to combine your 3 checkbox fields into one single-select field, to prevent users from checking more than one checkbox.

Assuming that you combine your 3 checkbox fields into one single-select field called “Billing”, this is what that formula would look like:

``````IF(
Billing="Hourly",DATETIME_DIFF({End Time},{Start Time},'minutes')/60,
IF(
Billing="Daily",DATETIME_DIFF({End Time},{Start Time},'days'),
IF(
Billing="Total",1
)))``````  5 - Automation Enthusiast

This is amazing! THANK YOU! It worked perfectly with the exception of the daily calculation. In this example it should be calculated as 1 day. Do you have any idea how I can change that?   18 - Pluto

You’re welcome! Well, that is actually 0 days because that is the same day (i.e. a full day hasn’t elapsed yet). But if you always want to add in an extra day, then just add a `+1` like I added in the `/60` for minutes.  5 - Automation Enthusiast

Oh I love you so much! THANK YOU! 