Skip to main content
Solved

Multiple Select Formula for TimeSheet

  • February 5, 2023
  • 6 replies
  • 44 views
bedjik
Forum|alt.badge.img+9

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?

Best answer by bazfilmer

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

6 replies

bazfilmer
Forum|alt.badge.img+4
  • bazfilmer
  • Inspiring
  • Answer
  • February 5, 2023

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
bedjik
bedjik
Forum|alt.badge.img+9
  • bedjik
  • Author
  • Known Participant
  • February 6, 2023

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

Thank you, I will try it out and let you know. All the hours are within 1 day, so no complication here. But I am pretty new in Airtable and codes, can you please specify where I need to insert my IN and OUT timings in this formula. My fields (IN and OUT) are both multiple selects that I created. Or I should not insert anything and it will work just fine. Sorry, it might sound stupid for you, but I'm trying to learn))

bazfilmer
Forum|alt.badge.img+4
  • bazfilmer
  • Inspiring
  • February 6, 2023

Thank you, I will try it out and let you know. All the hours are within 1 day, so no complication here. But I am pretty new in Airtable and codes, can you please specify where I need to insert my IN and OUT timings in this formula. My fields (IN and OUT) are both multiple selects that I created. Or I should not insert anything and it will work just fine. Sorry, it might sound stupid for you, but I'm trying to learn))


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.

bedjik
bedjik
Forum|alt.badge.img+9
  • bedjik
  • Author
  • Known Participant
  • February 7, 2023

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.


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 

bazfilmer
Forum|alt.badge.img+4
  • bazfilmer
  • Inspiring
  • February 7, 2023

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 


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)
bedjik
Forum|alt.badge.img+9
  • bedjik
  • Author
  • Known Participant
  • February 7, 2023

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)

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

Terms & ConditionsAccessibility statement