Fixing the formula to correctly handle dates across different years

How can I calculate the number of weeks between a start date in a previous year and the current date in the current year? Right now, I get a negative number if the start date is in the previous year.

Hi,
because I don’t want to use my brain :smiley:
I copied you question and asked Chatgpt, I hope this is correct , try and see:

Here’s how to calculate the number of weeks between two dates (even if the start date is from the previous year) using a Knack equation field:


:white_check_mark: Steps to Fix It in Knack

  1. Ensure both dates are valid:

    • One field is your Start Date (e.g., field_1)
    • The other is Today’s Date (you can use now() in an equation field)
  2. Use this equation to calculate the week difference:

    floor((now() - field_1) / 7)
    
    • This subtracts field_1 (your start date) from today’s date (now()), giving the difference in days, then divides by 7 to get weeks.
    • floor() makes sure you round down to the nearest whole number of weeks.
  3. Check if your start date is later than today:
    If you want to avoid negative values altogether (e.g., future start dates), you can use:

    max(0, floor((now() - field_1) / 7))
    

    This ensures the result is never negative.

Hi @George7 - great to see you back on the forum, its been a while :waving_hand:

If I understand your requirement then the below may help. :thinking: - Apologies if I’ve missed the point. :technologist:

Both solutions are valid. However, I don’t just want to avoid negative numbers — it also needs to continue counting from the previous year, which it doesn’t do in JAINI’s solution. CARLHOMES’s solution works fine, but not if you want to use the current date instead of an end date.

Couldn’t you just calculate the number of days. which works fine across many years: currentTime() - {start date} and then just divide by 7?

@LeighBerre96926 - that’s what I suggested in the video :slightly_smiling_face: