Help

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

Topic Labels: Formulas
Solved
Jump to Solution
1529 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Erin_McKinnon
5 - Automation Enthusiast
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!

image

1 Solution

Accepted Solutions
ScottWorld
18 - Pluto
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
)))

See Solution in Thread

4 Replies 4
ScottWorld
18 - Pluto
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
)))
Erin_McKinnon
5 - Automation Enthusiast
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?

image

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.

Erin_McKinnon
5 - Automation Enthusiast
5 - Automation Enthusiast

Oh I love you so much! THANK YOU!