Re: Chart for employee time sheets
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
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:
- time arrived
- start of midday (lunch?) break
- end of break
- end of day
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.
