This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Help! Trying to create formula to calculate # of h...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
641
4

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 13, 2021 09:32 AM

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!

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 13, 2021 10:08 AM

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

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 13, 2021 10:08 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 13, 2021 10:37 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 13, 2021 10:50 AM

`+1`

like I added in the `/60`

for minutes.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 13, 2021 10:53 AM

Oh I love you so much! THANK YOU!

Reply