Finding weeks since date

Questions and Help
1

What formula would you use to find the number of weeks since a prior date?

E.g.: Weeks since 9/30/20 with the end date being today's date.

2

To find the difference between dates, you want to use the {time: X} command which gives you the date in seconds.

Find the weeks from the selected date:

{formdate: YYYY-MM-DD; default=2020-09-30; name=date}

{ time: X} is the current date in seconds, { time: X; at={ =date}} is the selected date in seconds. We subtract one from the other to get the seconds between them.

We use "/ 60 / 60 / 24 / 7" to convert seconds into weeks.

Weeks to today from the selected date: {= ({time: X} - {time: X; at={=date}}) / 60 / 60 / 24 / 7; format=.2f}

"format=.2f" rounds to two decimal places. You could use "format=.0f" to round to a whole number.

Or if you wanted to hardcode the date rather than using a form selector:

{=({time: X} - {time: X; at=2020-09-30}) / 60 / 60 / 24 /7; format=.2f}

3

Scott, thank you for your response!

4

Hi Scott, I've seen two of your solutions to the date issue that work this way. I'm trying to figure out how to exclude weekends, which is possible using the date calculator, but not this method, to my knowledge.
Do you know a way to do it?

5

Hi @MacKenzie_Carter,

I've replied to your post in the suggestions forum but I'm going to repeat it here for the benefit of those people following this thread:

This can already be done. The skip needs to be right after the days (because it only applies to days). And you need to use shift and a minus sign to subtract, since you're going back in time.

{time: MM-DD-YYYY; shift=-1M-12D(skip=SAT, SUN)-21Y}

Hope this helps :slight_smile:

6

Trying to find a way to have it give full weeks and days. So the output is something like "7 weeks, 2 days". Having some trouble figuring out how to make it spit out the days minus the number of days in those whole weeks.

Any ideas?

7

Hey Aaron,

You need to use the remainder of the datetimediff in order to do that. Here's a post where I do something very similar -- let me know if you need any help implementing that for your use.

8

This is so far off from what I was trying to do that I have no idea where to start editing to get the result I need. Basically I currently have the below calculator where I can plug in any date in a note and get the weeks since. I can't figure out how to include a formula into what you've provided because I can't seem to figure out how to name a formula or create calcdiff rules around it.

{note}{formdate: MM/DD/YYYY; name=date}{endnote}{=({time: X} - {time: X; at={=date}}) / 60 / 60 / 24 / 7; format=.0f}

9

{note}{formdate: MM/DD/YYYY; name=date}{endnote}{=({time: X} - {time: X; at={=date}}) / 60 / 60 / 24 / 7; format=.0f} weeks

10

Hey Aaron,

Sorry that that example wasn't clear -- working with date differences can definitely be confusing.

Here's a snippet that does what you were requesting:

{note}{formdate: MM/DD/YYYY; name=date}{diffcalc=datetimediff({time: YYYY-MM-DD; at={=date}; pattern=MM/DD/YYYY}, {time: YYYY-MM-DD},"D")}{weekssince=floor({=diffcalc}/7)}{dayssince=remainder(diffcalc, 7)}
{endnote: trim=left}
It has been {=weekssince} weeks and {=dayssince} days since the selected date

1 Like
11

Thanks! I'm not sure I ever would have figured it out that way. I don't really understand a lot about what you did there haha.

12

In case you or anyone else reading this is interested, this is what that previous example snippet was doing:

  1. Created a new variable called diffcalc. This used the datetimediff( function to calculate the difference between two dates. This variable will have that value of how many days there were between those 2 dates.
    -- the first date is the date selected from your date picker
    -- the second is today's date
    -- the difference between the dates is given back to me as a number of days

  2. To determine the number of weeks since the selected date, I divided the number of days in diffcalc by 7, and used floor( to ensure it always rounded down

  3. To get days, I also divided the diffcalc by 7, by this time I used remainder( to get how many days were in the remainder

That gives me how many full weeks there have been, and how many days there had been that wouldn't comprise a full week.

3 Likes