Yearly time tracker - from excel to coda.io

[Patricia_Hoffmann]

Hello everyone!

I want to move my yearly time tracker (with weekly work hrs, overtime, sick days, holidays etc.) from excel to coda.
Has anyone already done something like this and could provide an example?

Thank you!

[Patricia_Hoffmann]

I’ll describe the issues that I am currently facing:

A) What is generally the best approach to set up such a doc?

My approach so far looks kind of like this. I feel like this is a very direct translation form excel to coda and that there is probably a better way to do so :

I want to set up an automation that adds a new row to the table everyday.

B) I want to automize a couple of things. For example: If an employee is on vacation today, then I automatically want the Should time to equal the time that he is supposed to work that day.
I’ve started to work this out with this formula/approach so far, but it doesn’t work:

Any Coda-genius out there that could help?

[Terry_Stagg]

You can use SwitchIf(thisRow.[Vacation?]=true,“8”) to put 8 in the duration column if they are on vacation. I tried a few different ways but this was the easiest and worked consistently.

This automation will add a row every day.

Hope this helps!

[Patricia_Hoffmann]

Thank you @Terry_Stagg!!! That already solves one problem

I really have to say that I’m impressed by the Coda community. I have just recently joined and so far I’ve received a lot of support and always in quite a short period of time as well!

[Terry_Stagg]

That’s one of the things that made me switch to Coda completely is all of the help you get from the community and the Coda support is excellent too. Everyone wants each other to succeed so that sure makes it nice.

[Patricia_Hoffmann]

I’m stuck at two more points… Can you maybe help @Terry_Stagg or someone else?

First one:
I want to count the overtime/undertime for each row in the Calendar Table:

In the table Employee I set up three columns to type in the Should/Day work hours for each employee in a certain date range.

Now, what I want is that the Should/Day in the table Calendar to look up the Should/Day in the table Employees and to automatically insert the correct number depending on the date. I’ve tried a lot of things already, but I guess my skills are not as advanced yet

Second one:
I want to calculate the overtime as well as undertime for each row. in the column Overtime in the table Calendar .However, it doesn’t give me the duration, but always states “xxx hrs ago” and not “+/- hrs ago”

[Pch]

Hi @Patricia_Hoffmann !

I’ve quickly modified your second sample doc and:

1. I corrected your Filter() formula to get your Should/Day value.

As this value is stored in the table Employees (at least, this is the one I think you wanted to use ), you need to filter that table to retrieve it… which I did with this formula:

Employees.Filter(
  Name = thisRow.Employee
  AND [Beginn Should/Day] <= thisRow.Date
  AND thisRow.Date <= [End Should/Day]
).First().[Should/Day].IfBlank(0).Hours()

And what it does is:

Takes the table Employees and look for the rows (each specific row in that table being stored as/ represented by CurrentValue) where:

• CurrentValue.Name = thisRow.Employee
  If True, this tells Filter() to keep those rows and, behind the scene, it returns a 1st filtered list of rows from the table Employees. If False the rows are discarded.

AND

• CurrentValue.[Beginn Should/Day] <= thisRow.Date
  If True, it tells Filter() to also keep those rows from the 1st filtered list of rows previously returned behind the scene. This creates a 2nd “hidden” filtered list of rows from the table Employees.

AND

• thisRow.Date <= CurrentValue.[End Should/Day]
  If True, it tells Filter() once more, to keep those rows from the 2nd “hidden” filtered list of rows previously returned behind the scene. This would be the final list of rows returned by Filter()

All 3 conditions in the Filter() formula needs to be True for Filter() to keep a row in its output as AND only return True “if all the items are true, otherwise false” .

As for the rest of the formula :

Employees.Filter( ... ).First().[Should/Day].IfBlank(0).Hours()

Now that Filter() found the appropriate list of rows from the table Employees:

• .First().(...).(...).(...):
  is there to effectively extract the row from the list of rows returned by Filter()
• (...).[Should/Day].(...).(...):
  access the value in the field [Should/Day] for the filtered row in the table Employees
• (...).(...).IfBlank(0).(...):
  In case there’s no corresponding filtered row from Employees (so if Filter() returns nothing), the formula should return 0 (so it can be pass to the last step of this formula… ).
• (...).(...).(...).Hours():
  Creates a duration in hours either from the 0 potentially returned by IfBlank() or the corresponding number value from [Should/Day].
  I did this because the values in [Should/Day] were interpreted as days by Coda and I assumed it was the amount of hours one person should work per day during the time frame set in the table Employees … Not sure if I made the right choice here . Sorry if I didn’t .

That’s for the Should/Day field in the table [Calendar 2023 - Master]
(Sorry for the length )

2. I added a number field called [Over/Under Time (Hrs)] where you’ll see a formula looking like this:

Duration(thisRow.Duration - thisRow.[Should/Day]).ToHours()

Which simply converts the time duration returned by thisRow.Duration - thisRow.[Should/Day] into a number of hours in this case (ToHours()).
Positive numbers returned by this formula would be the overtime hours and negative ones the undertime hours

You could also divide the Overtimes/Undertimes in 2 different fields (see the fields [Overtime 2] and Undertime) and use something like :

For the overtime:

If(
  thisRow.Duration > thisRow.[Should/Day],
  thisRow.Duration - thisRow.[Should/Day],
  0
)

And for the undertime:

If(
  thisRow.[Should/Day] > thisRow.Duration,
  thisRow.[Should/Day] - thisRow.Duration,
  0
)

Both compare thisRow.[Should/Day] to thisRow.Duration and subtracts the smaller duration from the larger one to get rid of the “ago” in their respective duration fields …

3. I’ve added a text field called [Over/Under Time (Formatted)] to format the duration as duration fields are not that flexible when it comes to formatting… But if you just want to display the over/under times in a certain way, maybe this could work …

It uses the value in the field [Over/Under Time (Hrs)] and the Format() formula …

Format(
  // Format() template to use depending on the value of thisRow.[Over/Under Time (Hrs)]
  If(thisRow.[Over/Under Time (Hrs)] >= 0,"+ {1} {2}","- {1} {2}"),
  // Value of the placeholder {1} in the template
  thisRow.[Over/Under Time (Hrs)].AbsoluteValue(),
  // Value of the placeholder {2} in the template
  If(thisRow.[Over/Under Time (Hrs)].Contains(-1,0,1),"hr","hrs")
) 

I know @Christiaan_Huizer talked about negatives hours in this blog post:

How to Coda negative hours?

So, maybe this could help you …

Sorry for the length of this reply … But I hope this helps you a little !

[Patricia_Hoffmann]

Hello Pch!

I just want to thank you so much. I spent quite some time on understanding everything you wrote and I feel like I made quite a progress. Thank you so much for taking the time! It so much fun to see that it all kind of works out now
I still have to dive a bit deeper into the formatting formula to fully understand it

I’m still struggling with one issue. Maybe you have a solution to that one as well?
I want to expand the table Employees by the column “Work days”.
In the Mastertable I included a checkbox called “Work days”. What I want is that if the employee is supposed to work on that day, that the checkbox is then activated and the column “Should/Day” stays blank.
This has been my approach so far (It didn’t work out though ):

I set it up in Setup V3 in the document provided above.

[joost_mineur]

The column should/day does not have a formula, so it is going to say whatever you put in there. I really don’t understand what should/day is supposed to reflect, so I did not suggest a formula.

As far as the checkbox for workday/?* goes, I put a working formula in place. It’s not a sample of beautiful code, but it works:
Employees.Filter(Name=thisRow.Employee and thisRow.Date>=[Beginn Should/Day] and thisRow.Date <=[End Should/Day]).[Work days].ListCombine().Contains(thisRow.Date.WeekdayName())

[Patricia_Hoffmann]

Hi!

Should/Day has a formula

I’ll have a look at your solution - thank you

[Patricia_Hoffmann]

Hey joost!
Thank you very much - It worked out And once again I learnt a bit more about coda!