Hi! I track employee time using In and Out Date/Time fields and a formula field that calculates total hours. Now I want to see total hours in a chart block, but my Y-axis must be a numeric field.
I want to keep seeing In and Out times, so simply entering hour amounts won’t work. Is there another way I can total, look up, or roll up my employee hours in a way that a chart block will read it? Do I need to explore a different type of block?
Thanks for any support you’ve got!
+20Welcome to the community, @Student_Services_Adm! :grinning_face_with_big_eyes: I just ran a quick test, and with a formula field to calculate total hours worked, the Chart block lets me use that field for the Y axis.
Here’s my hunch: in your formula, you’re doing something that changes the output from a number to a string. Are you adding something like " hours" to the end, so that the formula shows entries like “8 hours”, “7 hours,” etc.? Or maybe you’re checking for valid dates in those fields before running the calculation (to avoid seeing “#ERROR”), and you’re adding an empty string to the end if the dates aren’t there? Like this:
IF(AND(In, Out), DATETIME_DIFF(Out, In, "hours"), "")
Unfortunately that will also force the field’s output to be a string, and it won’t be usable by the Chart block.
The solution is to leave the output as a number. If you’re adding " hours" to the end, remove that from your formula. If you’ve ended your IF() function with empty quotes, remove them and the comma just before them:
IF(AND(In, Out), DATETIME_DIFF(Out, In, "hours"))
That last part of an IF() function is actually optional (though the docs don’t currently say that). If omitted, the function will automatically return the appropriate equivalent of BLANK() based on the formula’s other output. Because DATETIME_DIFF() outputs a number, it will output 0 if the difference calculation isn’t run.
Welcome to the community, @Student_Services_Adm! :grinning_face_with_big_eyes: I just ran a quick test, and with a formula field to calculate total hours worked, the Chart block lets me use that field for the Y axis.
Here’s my hunch: in your formula, you’re doing something that changes the output from a number to a string. Are you adding something like " hours" to the end, so that the formula shows entries like “8 hours”, “7 hours,” etc.? Or maybe you’re checking for valid dates in those fields before running the calculation (to avoid seeing “#ERROR”), and you’re adding an empty string to the end if the dates aren’t there? Like this:
IF(AND(In, Out), DATETIME_DIFF(Out, In, "hours"), "")
Unfortunately that will also force the field’s output to be a string, and it won’t be usable by the Chart block.
The solution is to leave the output as a number. If you’re adding " hours" to the end, remove that from your formula. If you’ve ended your IF() function with empty quotes, remove them and the comma just before them:
IF(AND(In, Out), DATETIME_DIFF(Out, In, "hours"))
That last part of an IF() function is actually optional (though the docs don’t currently say that). If omitted, the function will automatically return the appropriate equivalent of BLANK() based on the formula’s other output. Because DATETIME_DIFF() outputs a number, it will output 0 if the difference calculation isn’t run.
Hi Justin,
Thank you so much for weighing in! That’s great I can get a formula field into a chart. So maybe my problem is I’ve got nowhere that calculates total hours beyond individual days. Here’s a sample of my records:
You’ll see IN, OUT, and Day Total calculated by DATETIME_DIFF({OUT}, {IN}). So not an IF formula. But there’s currently nowhere that counts up the day totals, except for the “Sum” at the top of each grouping. And I wonder how/where I would do that? By Employee? By Month? Primary field is Employee Name. My pay periods are single-select fields. I’ve got Month and Weekday identified in other formula fields. . . sorry if this makes it more complicated.
Heather
Hi Justin,
Thank you so much for weighing in! That’s great I can get a formula field into a chart. So maybe my problem is I’ve got nowhere that calculates total hours beyond individual days. Here’s a sample of my records:
You’ll see IN, OUT, and Day Total calculated by DATETIME_DIFF({OUT}, {IN}). So not an IF formula. But there’s currently nowhere that counts up the day totals, except for the “Sum” at the top of each grouping. And I wonder how/where I would do that? By Employee? By Month? Primary field is Employee Name. My pay periods are single-select fields. I’ve got Month and Weekday identified in other formula fields. . . sorry if this makes it more complicated.
Heather
Here’s a wider view:
+20If that’s your formula for the {Day total} field, then that may be part of your problem. The preferred way to use the DATETIME_DIFF() function is to specify a units value as a third string argument; e.g. “hours”, “minutes”, “days”, etc. By omitting that argument, Airtable defaults to returning the difference in seconds (which I’m assuming you formatted as a duration based on your screenshot). For me, that still works in the Chart block, but the scale on the left is meaningless because it’s so large:
Thankfully all is not lost. :slightly_smiling_face: Before tackling that solution, I want to address some other things.
First off, you said that your primary field is the employee name, which I strongly suggest changing. I’m guessing you’re coming from a spreadsheet background, where cells are used somewhat arbitrarily for whatever makes sense (I’ve been there). With a database, the primary field should be unique, so that no two records have the same primary field value. As you can see, Airtable doesn’t enforce that from the user’s end. Thankfully its own internal record tracker does create unique identifier for each record, but you should still follow good database design and make that primary field unique. We’ll talk about how in just a second.
When it comes to the main issue that you mentioned—summing a day total—this would be much easier if all time entries for a given employee on a given day were in a single record. From the looks of it, there are currently two records per employee per day worked, and I’m guessing those four values represent:
With all that in mind, I suggest reworking your base like this…
First, make an [Employees] table, where you store all employee-related data. One record per employee, with the primary field as their name.
Next, you’ll need to reorganize your current table a bit. Between the {Name} field at the {Pay period} field, insert an {Employee} link field that lets you choose from your new [Employees] table.
Convert the primary field to a formula, using this:
IF(IN, Employee & " - " & DATETIME_FORMAT(IN, "L"))
That will make the primary field into something more unique, combining the employee name with the date portion of the {IN} date value.
Next (and this is going to be a bit of a big change), we need to effectively turn each pair of your current records into a single record that stores all four of those time values above. Here’s what I suggest:
{IN} - This can stay as-is, and will represent the time they clock in at the start of their day/shift{OUT} - I suggest renaming this to {Break Start} or something similar.{Break End} - Add this field next to {Break Start}.{OUT} - Add this field after {Break End}
Looking at each pair of records that currently represent one day for a given employee, you’ll need to copy the date values from the {IN} and {OUT} (renamed {Break Start}) fields from the lower record of that pair, and paste them into {Break End} and the new {OUT}, respectively, on the top record of that pair. I hope this diagram makes this more clear than my text does:
The blue boxes are the record pairs you currently have. You’re just copying the dates from lower one into the new fields in the one above it, then removing the lower record completely. This puts all four time entries for one person on one day into a single record. Yes, it’s going to be a lot of work to move all this stuff around, but having one record per employee per day will be far easier to manage in the long run.
Once all that is done, the {Day total} formula will need to be reworked to take the sum of the first time span—{IN} to {Break Start}—and add it to the second time span—{Break End} to {OUT}. That will give you the true summary of time worked for that employee on that date.
However, we also need to create a number that’s going to work for the chart. If you switch to “hours” for the units, Airtable won’t give you portions of hours, only whole hours, so you won’t have an accurate picture of the time worked. I suggest using this:
(DATETIME_DIFF({Break Start}, IN, "seconds") + DATETIME_DIFF(OUT, {Break End}, "seconds")) / 3600
That gives you something like this, with the formatting for that field set to decimal with two places visible:
The only downside is that you don’t have the clean hours and minutes that the duration format provides, but you’ve got something much more usable for the Chart block.
In the end, all of this reorganizing solves several problems. You now have an accurate look at what each employee did on a single day because it’s all in a single record for that day, the primary fields are unique, you’ve got day totals that work a lot more effectively in the chart block, and you also have a table for tracking employees.
If that’s your formula for the {Day total} field, then that may be part of your problem. The preferred way to use the DATETIME_DIFF() function is to specify a units value as a third string argument; e.g. “hours”, “minutes”, “days”, etc. By omitting that argument, Airtable defaults to returning the difference in seconds (which I’m assuming you formatted as a duration based on your screenshot). For me, that still works in the Chart block, but the scale on the left is meaningless because it’s so large:
Thankfully all is not lost. :slightly_smiling_face: Before tackling that solution, I want to address some other things.
First off, you said that your primary field is the employee name, which I strongly suggest changing. I’m guessing you’re coming from a spreadsheet background, where cells are used somewhat arbitrarily for whatever makes sense (I’ve been there). With a database, the primary field should be unique, so that no two records have the same primary field value. As you can see, Airtable doesn’t enforce that from the user’s end. Thankfully its own internal record tracker does create unique identifier for each record, but you should still follow good database design and make that primary field unique. We’ll talk about how in just a second.
When it comes to the main issue that you mentioned—summing a day total—this would be much easier if all time entries for a given employee on a given day were in a single record. From the looks of it, there are currently two records per employee per day worked, and I’m guessing those four values represent:
With all that in mind, I suggest reworking your base like this…
First, make an [Employees] table, where you store all employee-related data. One record per employee, with the primary field as their name.
Next, you’ll need to reorganize your current table a bit. Between the {Name} field at the {Pay period} field, insert an {Employee} link field that lets you choose from your new [Employees] table.
Convert the primary field to a formula, using this:
IF(IN, Employee & " - " & DATETIME_FORMAT(IN, "L"))
That will make the primary field into something more unique, combining the employee name with the date portion of the {IN} date value.
Next (and this is going to be a bit of a big change), we need to effectively turn each pair of your current records into a single record that stores all four of those time values above. Here’s what I suggest:
{IN} - This can stay as-is, and will represent the time they clock in at the start of their day/shift{OUT} - I suggest renaming this to {Break Start} or something similar.{Break End} - Add this field next to {Break Start}.{OUT} - Add this field after {Break End}
Looking at each pair of records that currently represent one day for a given employee, you’ll need to copy the date values from the {IN} and {OUT} (renamed {Break Start}) fields from the lower record of that pair, and paste them into {Break End} and the new {OUT}, respectively, on the top record of that pair. I hope this diagram makes this more clear than my text does:
The blue boxes are the record pairs you currently have. You’re just copying the dates from lower one into the new fields in the one above it, then removing the lower record completely. This puts all four time entries for one person on one day into a single record. Yes, it’s going to be a lot of work to move all this stuff around, but having one record per employee per day will be far easier to manage in the long run.
Once all that is done, the {Day total} formula will need to be reworked to take the sum of the first time span—{IN} to {Break Start}—and add it to the second time span—{Break End} to {OUT}. That will give you the true summary of time worked for that employee on that date.
However, we also need to create a number that’s going to work for the chart. If you switch to “hours” for the units, Airtable won’t give you portions of hours, only whole hours, so you won’t have an accurate picture of the time worked. I suggest using this:
(DATETIME_DIFF({Break Start}, IN, "seconds") + DATETIME_DIFF(OUT, {Break End}, "seconds")) / 3600
That gives you something like this, with the formatting for that field set to decimal with two places visible:
The only downside is that you don’t have the clean hours and minutes that the duration format provides, but you’ve got something much more usable for the Chart block.
In the end, all of this reorganizing solves several problems. You now have an accurate look at what each employee did on a single day because it’s all in a single record for that day, the primary fields are unique, you’ve got day totals that work a lot more effectively in the chart block, and you also have a table for tracking employees.
Wow, Justin, that is SO HELPFUL! I really appreciate the explanation and support.
Thank you so much, I’ll get to get to work on this :slightly_smiling_face: Heather
+20Glad to know that you got the answer you were seeking! If you would, please mark my comment as the solution to your question. This helps others who may be searching with similar questions. Thanks!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
