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
- Formula to work out end date & time - start date a...

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

0
426
6

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

Oct 27, 2022 11:02 PM

6 Replies 6

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

Oct 29, 2022 06:26 AM

The duration field holds its value in “seconds” under the hood. So a duration field displaying 1 hour (`1:00`

) is actually holding the numerical value of `3600`

.

So you could subtract your “Break” field from your “Hours” field, as hours, like this:

```
IF({Hours}, {Hours}, 0) - (IF({Break}, {Break} / 3600, 0))
```

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

Nov 03, 2022 06:46 PM

Thanks so much. Sadly this formula just spits out the same as the hours.

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

Nov 03, 2022 06:47 PM

If someone works 8 hours and takes a 30 minute break, what is the formula to get 7.5 hours please?

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

Nov 03, 2022 07:21 PM

Hi @Hayley_Devlin,

Is your `Billable Hours`

field configured to display its value as an “Integer”? It needs to be configured to display as "Decimal, or else it is going to round `7.5`

up to the nearest integer, which is `8`

.

Reply

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

Nov 03, 2022 10:06 PM

that’s it. Thank you!

Now my issue is that the hours column is not displaying half hour blocks. This should be 4.5 hours (instead of 4). Could you please tell me if this formula is right?

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

Nov 04, 2022 02:21 PM

What is the minimum increment you want to round to for your `Hours`

field? If someone works 7 hours and 15 minutes, do you want it to record `7.25`

hours? Or should that round up to `7.5`

hours? And what are your rounding cutoffs? Should 8:00am - 1:45pm round up to 6 hours? Or down to 5.5 hours? What about 8:00am - 1:44pm?

If rounding is no issue (maybe you will just always enter “rounded” times like 2:00 instead of 1:45), then you can just change that formula to return its difference in minutes, and then divide by 60:

```
DATETIME_DIFF({Finish}, {Start}, "minutes") / 60
```

That will get you fractions of an hour.

If rounding at thresholds is important, that will take a bit more work.

If you need to always round *down* until the next half-hour increment is met, `FLOOR()`

should do the trick:

```
FLOOR(
DATETIME_DIFF({Finish}, {Start}, "minutes") / 60,
0.5
)
```

*FLOOR() rounding 7 hours and 59 minutes down to 7.5 hours worked*

If you want to round to the nearest half-hour, with 15-minute cutoff thresholds, this will do it:

```
IF(
MOD(DATETIME_DIFF({Finish}, {Start}, "minutes"), 60) >= 45,
CEILING(DATETIME_DIFF({Finish}, {Start}, "minutes") / 60, 0.5),
IF(
MOD(DATETIME_DIFF({Finish}, {Start}, "minutes"), 60) >= 30,
FLOOR(DATETIME_DIFF({Finish}, {Start}, "minutes") / 60, 0.5),
IF(
MOD(DATETIME_DIFF({Finish}, {Start}, "minutes"), 60) >= 15,
CEILING(DATETIME_DIFF({Finish}, {Start}, "minutes") / 60, 0.5),
FLOOR(DATETIME_DIFF({Finish}, {Start}, "minutes") / 60, 0.5)
)
)
)
```

*The formula above creates thresholds at the 15 minute marks for rounding up or down to the nearest half-hour*