I am looking for a simple formula to compute hours worked. I have “Start Time” and “End Time” and am looking for a formula for “Total Hours Worked”. I tried one of the other formulas that I found on another post and was unable to get it to work with my table. If someone could provide me with a simple formula to add my start time and end time and output a total hours worked I would appreciate it!
Simple Hours Worked Formula
+18You can perfectly make use of the Field Type DURATION.
Create a Formula Field “Total Hours Worked”, and use the formula {End Time}-{Start Time}.
The result is a number (seconds). In this formula field (Total Hours Worked), customize the field settings in FORMATTING (above your formula) to DURATION and voila… the display in HH:MM (if you prefer to see it that way; HH:MM:SS is also possible).
Thanks! This was super helpful. However, now my issue is formatting my “Start Time” and “End Time” columns. Is there a “Time” function that can be used without the date attached? I tried to customize my Start and End time columns but cannot find one that works with that formula to give me total hours worked. Ideally, what I want to do is be able to type in 8:00 in Start Time and 5:00 in end time, and then in total time worked have 9:00 come up for hours. Do you know how I can do this? Thanks so much!
Thanks! This was super helpful. However, now my issue is formatting my “Start Time” and “End Time” columns. Is there a “Time” function that can be used without the date attached? I tried to customize my Start and End time columns but cannot find one that works with that formula to give me total hours worked. Ideally, what I want to do is be able to type in 8:00 in Start Time and 5:00 in end time, and then in total time worked have 9:00 come up for hours. Do you know how I can do this? Thanks so much!
@Kate_Linkosky - Will start times always be AM and end times always be PM? Or is it possible that somebody could work a night shift? And also, will time records ever be created on a day other than the day that the “Start” and “End” times occurred?
I’m sure I can work something out that allows you to just enter 8:00 in one field and 5:00 in another and have it output hours worked, assuming the times are on the same day and the first is AM and the second is PM. But the way it gets set up will largely depend on the questions I asked above.
Thanks! This was super helpful. However, now my issue is formatting my “Start Time” and “End Time” columns. Is there a “Time” function that can be used without the date attached? I tried to customize my Start and End time columns but cannot find one that works with that formula to give me total hours worked. Ideally, what I want to do is be able to type in 8:00 in Start Time and 5:00 in end time, and then in total time worked have 9:00 come up for hours. Do you know how I can do this? Thanks so much!
Here’s something to start with - I can help you make adjustments if needed:
TimeClock - Airtable
Explore the "TimeClock" base on Airtable.
The “Description” of each field tells you how to set it up. It’s important that any field that asks whether you want to use “The same time zone (GMT) for all collaborators” is checked “Yes” (green) or else the calculations may not come out right.
Once it’s all set up, you can hide all the formula fields and show only “In”, “Out”, and “Hours Worked”.
@Kate_Linkosky - Will start times always be AM and end times always be PM? Or is it possible that somebody could work a night shift? And also, will time records ever be created on a day other than the day that the “Start” and “End” times occurred?
I’m sure I can work something out that allows you to just enter 8:00 in one field and 5:00 in another and have it output hours worked, assuming the times are on the same day and the first is AM and the second is PM. But the way it gets set up will largely depend on the questions I asked above.
Hey Jeremy! So, what it is is a table for my boss to enter in the hours that his subcontractors submit. So the only people that have access to the table are myself and him. I enter my hours in there as well. My hours aren’t the ones we are trying to easily compute as mine vary and there are times I work in the morning as well as at night. His other subcontractor generally works during the day so I am not worried about night shifts. THanks so much for your help, let me know what you can come up with!
Okay just checked out the TimeClock base and I think this is going to work for me! Just have to configure it to go into the table I have set up. Thanks again for the suggestion!
Okay spoke too soon, is there a way to tweak this so that the date field does not only show the input date?
Okay spoke too soon, is there a way to tweak this so that the date field does not only show the input date?
In other words, you need to manually tell it which date these times being entered are for?
In other words, you need to manually tell it which date these times being entered are for?
Yes! Sorry, I am new at this!
Yes! Sorry, I am new at this!
Ok, refresh it and take a look - I updated it to give you the ability to enter a date to override the automatic dating of “Today” if you need to enter times from a different day. Again, you can hide any formula fields you don’t need to see.
Does that work? Anything else you’d need this to do?
Here’s something to start with - I can help you make adjustments if needed:
TimeClock - Airtable
Explore the "TimeClock" base on Airtable.
The “Description” of each field tells you how to set it up. It’s important that any field that asks whether you want to use “The same time zone (GMT) for all collaborators” is checked “Yes” (green) or else the calculations may not come out right.
Once it’s all set up, you can hide all the formula fields and show only “In”, “Out”, and “Hours Worked”.
Hi there, please would you be able to adjust this so that it works for night shifts? so for example, 7pm to 7am is a shift. Or could you please tell me how to do it. Thank you very much.
Hi Jeremy, this is so helpful. Can you let me know how I format the first columns to have european format of Dates? Thanks, Nadine
Hi Jeremy, this is so helpful. Can you let me know how I format the first columns to have european format of Dates? Thanks, Nadine
If you are referring to the formula fields, you can use these specifiers with the DATETIME_FORMAT function to get just about any format you could want:
So, for example, you could do:
DATETIME_FORMAT({My Date}, 'YYYY-MM-DD')Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.