Creating a Formula For Tracking Yearly Accrued Hours

[daniel_jacob_self]

Hi All!

I work in a profession where I need to accrue a certain number of hours per year to renew my license.

The trick is that it’s not by calendar year, but by year based on my date of licensure.

I have a table where I create a new line each time I accrue hours.

I’m trying to create a formula above the table (see attachment) that tracks how many hours I have remaining for this specific licensure year

So if my total hours needed for the licensure year are 18, and my license renewal date is 7/13… I’ve been trying to mess around with something like:

18 - [CEU Tracker].Filter(Date>=Date(2024, 7, 13).Last() AND Date<=Date(2025, 7, 12).First()).CEUs.Sum()

Or something like that!? But is there anyway to make it so that the range is 7/13 of this year and 7/12 of next year so that it automatically updates to track the current licensure-year?

Thanks so much y’all are amazing!

[Ryan_Hubbard1]

There is! Use the Today() formula and then extracting the year from it. So your formula becomes:

18 - [CEU Tracker].Filter(Date>=Date(Today().Year() 7, 13).Last() AND Date<=Date(Today().Year()+1, 7, 12).First()).CEUs.Sum()

[daniel_jacob_self]

AMAZING! Thank you Ryan!! This community is so cool and Coda is so cool!!!

Hope you’re well and feeling chuffed at what a badass Coda Maker you are!

Cheers

[daniel_jacob_self]

Wait one quick question that was pulling at the back of my brain all day…

Won’t the formula break when the new year arrives? Meaning, if it’s extracting the year from this year, once January coes around won’t it switch over to that year and that-year-±1?

So for example, right now I’m tracking the hours accrued between July 2024 and July 2025, and today().year() puts in the 2024 and today().year()+1 puts in 2025. But in January 2025, I’ll still want to be tracking the hours accrued between July 24 and July 25 but suddenly “today” will insert 2025 and today +1 will input 2026 won’t it?

[Ryan_Hubbard1]

@daniel_jacob_self yeah good catch and my fault for not reading in more detail.

Here’s how you’d do the full formula:

1. Use a Data control to set your CEU License start date (just makes formulas easier)
2. Use the date function ‘RelativeDate()’ which adds number of months to a date.

Here’s a link to the page that has it implemented. if you’d like to take a deeper look.

[daniel_jacob_self]

WOW! CODA IS SO COOL! And YOU are so cool!! Thank you that’s amazing. So exciting to learn and implement. The possibilities seem limitless!

Appreciate you!