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
- Re: Multiple Select Formula for TimeSheet

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

Solved

Jump to Solution

0
2012
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 04, 2023 05:52 PM

Hello,

Hope someone will be able to help me, I am trying to create a formula field for my employee to auto calculate his work hours for a day. He fills out the form and I am tired to calculate his hours per day.

So, I've got one field "IN" with multiple select options "8:00am" "8:30am" and "9:00am" and another field "OUT" with multiple select hours "3:00pm", "3:30pm" and "4:00pm" (I have more options, but it does not matter here), so ones IN and OUT are filled, I want to have a formula field calculating how many hours my employee worked today. I thing it should be done as logical formula, like if IN is *8:00am" and OUT is "3:00pm" then HOURS result is 7, something like this, but I can not make it right, anybody could help?

Solved! Go to Solution.

3 Solutions

Accepted Solutions

Solved
See Solution in Thread

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

Feb 04, 2023 06:34 PM

This formula to find the difference between IN and OUT will work within your current setup. Although, it assumes that IN and OUT are on the same day. If there's potential for overnight beginning and ending on different dates, it will require additional thought.

(DATETIME_FORMAT(DATETIME_PARSE(OUT, 'h:mmA'), 'X')-DATETIME_FORMAT(DATETIME_PARSE(IN, 'h:mmA'), 'X'))/60/60

Reply

Solved
See Solution in Thread

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

Feb 05, 2023 06:05 PM

No worries - we were all new at some point. The formula should be added to a new “formula” field you need to create. If you look at the formula I included in my previous post, you’ll see two references to other fields “OUT” and “IN” within it. When I wrote it I assumed that the IN and and OUT fields are named exactly that on your table, but if the names are different you’ll need to edit the formula to correctly reference the fields.

One point to clarify. I’m assuming that your IN and OUT fields are “single select” fields, where you can only select one option per record. If that’s not the case we may need to discuss further.

Reply

Solved
See Solution in Thread

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

Feb 07, 2023 06:53 AM

1) Can you confirm that the formula field is configured to show decimals like the below photo?

2) Here is the same formula, but I've added and IF() statement. If the outcome of the original formula is an error, then the entire formula will return BLANK(), otherwise it will return the non-error output of the formula.

IF(

ISERROR((DATETIME_FORMAT(DATETIME_PARSE(OUT, 'h:mmA'), 'X')-DATETIME_FORMAT(DATETIME_PARSE(IN, 'h:mmA'), 'X'))/60/60),

BLANK(),

(DATETIME_FORMAT(DATETIME_PARSE(OUT, 'h:mmA'), 'X')-DATETIME_FORMAT(DATETIME_PARSE(IN, 'h:mmA'), 'X'))/60/60)

6 Replies 6

Solved
See Solution in Thread

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

Feb 04, 2023 06:34 PM

This formula to find the difference between IN and OUT will work within your current setup. Although, it assumes that IN and OUT are on the same day. If there's potential for overnight beginning and ending on different dates, it will require additional thought.

(DATETIME_FORMAT(DATETIME_PARSE(OUT, 'h:mmA'), 'X')-DATETIME_FORMAT(DATETIME_PARSE(IN, 'h:mmA'), 'X'))/60/60

Reply

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

Feb 05, 2023 04:40 PM

Solved
See Solution in Thread

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

Feb 05, 2023 06:05 PM

No worries - we were all new at some point. The formula should be added to a new “formula” field you need to create. If you look at the formula I included in my previous post, you’ll see two references to other fields “OUT” and “IN” within it. When I wrote it I assumed that the IN and and OUT fields are named exactly that on your table, but if the names are different you’ll need to edit the formula to correctly reference the fields.

One point to clarify. I’m assuming that your IN and OUT fields are “single select” fields, where you can only select one option per record. If that’s not the case we may need to discuss further.

Reply

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

Feb 06, 2023 10:37 PM

Thank you very much for your help, everything worked fine. If you can help with two things here, I would really appreciate it

1. Is it possible to modify formula for half an hour calculation, what I mean is that sometimes my employee has hours like this, 8:30am to 5:00pm, so right now, the formula shows 9, but it should be 8:30. If it makes things complicated, it is ok and do not bother.

2. In the work sheet, we also show day offs, so these days do not have any selection and the formula displays "NaN", is it possible to make it just blank field

Thank you again for all your help

Solved
See Solution in Thread

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

Feb 07, 2023 06:53 AM

1) Can you confirm that the formula field is configured to show decimals like the below photo?

2) Here is the same formula, but I've added and IF() statement. If the outcome of the original formula is an error, then the entire formula will return BLANK(), otherwise it will return the non-error output of the formula.

IF(

ISERROR((DATETIME_FORMAT(DATETIME_PARSE(OUT, 'h:mmA'), 'X')-DATETIME_FORMAT(DATETIME_PARSE(IN, 'h:mmA'), 'X'))/60/60),

BLANK(),

(DATETIME_FORMAT(DATETIME_PARSE(OUT, 'h:mmA'), 'X')-DATETIME_FORMAT(DATETIME_PARSE(IN, 'h:mmA'), 'X'))/60/60)

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

Feb 07, 2023 08:09 AM

Thanks again, everything works, decimals and IF. No more questions