Is it possible to create a formula to calculate if 'date' is in this calendar week, last week or next week?

Platform discussions
1

I’m working on a cash flow management board and need a formula to set status based on if an item due date is this week, next week, or last week.

This basic formula gets me an output of Future or Past but I need something just a bit more precise and week based, not day based.
IF(FORMAT_DATE({Payment Due},“YYYYMMDD”)<=FORMAT_DATE(today(),“YYYYMMDD”), “future”,“past”)

I think WEEKNUM could be leveraged to return a week number that I can apply some arithmatic to and it gets me close on the first half of the formula. Like this:
IF(Weeknum({Payment Due})<=…

But the second half is weird. Can I somehow format either Today() or WEEKNUM() to output the week number for today rather than for a date column?

2

@gbarker121

You can check out my last video on monday formulas. It has a list of the FORMAT_DATE() codes in the description.

FORMAT_DATE(TODAY(),“W”) returns the current week of the year. (Weeks start on the first Monday.)

Jim - Subscribe to The Monday Man
Watch Our Latest Video: monday Formulas - Part 3: Dates/Times, Strings and Status

1 Like
3

Thanks a ton Jim. I’ve not had a chance to explore this too much but wanted to thank you for your reply. I’ll provide an update sometime this week.

4

Hi @gbarker121 :wave:

So glad @JCorrell was able to offer a possible solution for you here!
Let us know once you have a chance to check it out and if we can provide any further assistance :grinning_face_with_smiling_eyes:

Best,

Dani

5

This was great Jim. Just enough information to get me on the right track but not so much that I didn’t learn a lot in the process. Thanks for your help!

6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.