Skip to main content

Hey all! I have recently started using Airtable and I have been searching for an answer to this question for too long.


I am attempting to track volunteer hours. I have a form that people will complete every time they have a “shift”. This form asks people when they came in and when they left. I want to create a formula that finds the difference of the End time and the Start time in order to find the total time of their shift.


I have read through several ask the community questions similar to this and tried several formulas; including DATETIME_DIFF. However, nothing seems to be working.


Please help! Thanks in advance.





I am at the point of almost leaving a question in the form that asks for the volunteer to put the duration of their shift. However, I would much rather have airtable do so automatically.

You just need to use commas to separate the parameters you pass into the DATETIME_DIFF() function:



DATETIME_DIFF(


   {End Time},


   {Start Time},


   'hours'


)

You just need to use commas to separate the parameters you pass into the DATETIME_DIFF() function:



DATETIME_DIFF(


   {End Time},


   {Start Time},


   'hours'


)




It’s only giving me -0, I’m not sure why




It’s only giving me -0, I’m not sure why


Nevermind! I used ‘minutes’ instead of hours & it worked! Thank you!!

Nevermind! I used ‘minutes’ instead of hours & it worked! Thank you!!


So you should get hours * 60, and not -0 🤔

So you should get hours * 60, and not -0 🤔


I had used this formula: And it seemed to be working fine, but now it’s giving me negative results.


So I tried the ‘hours’ * 60 and it gave me an error. Any clue what am I doing wrong?

I had used this formula: And it seemed to be working fine, but now it’s giving me negative results.


So I tried the ‘hours’ * 60 and it gave me an error. Any clue what am I doing wrong?




Yes, you have those Starts after the corresponding Ends, instead of before (they maybe are swapped).





What I meant with ‘hours * 60’ was that, if you expect for instance to get a 6 because your want to get hours, when you specify minutes, the number should be 360, what is 6 multiplied by 60.



Yes, you have those Starts after the corresponding Ends, instead of before (they maybe are swapped).





What I meant with ‘hours * 60’ was that, if you expect for instance to get a 6 because your want to get hours, when you specify minutes, the number should be 360, what is 6 multiplied by 60.


SO swapping just swapped which records came out positive/negative. 60 & 360 both gave me errors.

SO swapping just swapped which records came out positive/negative. 60 & 360 both gave me errors.


Okay I didn’t realize ‘*360’ had to be written with a comma. So I put the comma & the negatives do not change…

Okay I didn’t realize ‘*360’ had to be written with a comma. So I put the comma & the negatives do not change…


your last post with pictures looks like it did everything correctly, the clock out times were just entered incorrectly(before start time) so you correctly] got negative numbers.

your last post with pictures looks like it did everything correctly, the clock out times were just entered incorrectly(before start time) so you [correctly] got negative numbers.


I just realized that was the issue. Thank you so much for being so responsive!



Yes, you have those Starts after the corresponding Ends, instead of before (they maybe are swapped).





What I meant with ‘hours * 60’ was that, if you expect for instance to get a 6 because your want to get hours, when you specify minutes, the number should be 360, what is 6 multiplied by 60.




This was not clear? 🤔







No, DATETIME_DIFF expect just a unit as the third parameter, you can’t put something like ‘hours * 3600’. Again, it was just a way of explaining that whichever unit you used (hours or minutes), you should get a correct number, and not a -0.





  • If you expect to get 5 hours but you specify minutes as the unit, you’ll get 300, because 5 hours has 300 minutes.


  • If you expect to get 90 minutes but you specify hours as the unit, you’ll get 1.5, because 90 minutes are equal one and a half hour




I can’t believe I am explaining this :grinning_face_with_sweat:

Hi. Has anyone managed to work out how to calculate total number of hours worked between two dates (multiple weeks sometimes) but only including working hours e.g. Mon-Fri, 0900-1700 ?

Reply


Terms & Conditions