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