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

[Erin_McKinnon]

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!

[ScottWorld · Accepted solution]

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]

ScottWorld:

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
)))

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?

[ScottWorld]

Erin_McKinnon:

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.

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]

Oh I love you so much! THANK YOU!